Saturday, November 01, 2008

What happens if you drop your DUAL table? [Oracle 9i] [Oracle 10g][Oracle 11g]

The dual table is a table which contains just one column and one row with the letter X.

All users of the database have access to the dual table but it should not be fiddled with. It is used for functions like

SQL> select sysdate from dual;

SYSDATE

---------

01-NOV-08

Just incase you happen to drop the dual table, it can be easily recreated with the following script.

Login as sysdba and execute:

CREATE TABLE SYS.DUAL

(

DUMMY VARCHAR2(1)

)

TABLESPACE SYSTEM;

drop PUBLIC SYNONYM DUAL;

CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;

INSERT INTO dual VALUES ('X');

SELECT * FROM dual;

commit;

That’s it!!! You have your DUAL table back.

Absence of DUAL table is likely to throw the following error to users (not sysdba users)

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.

Monday, October 20, 2008

How to switch or enable a database to archive log mode [Oracle 9i]

When a database is in archive log mode oracle ensures that the online redo logs are not overwritten until they are archived. When a database is in archive log mode chances of data loss are minimal.

Here is a step by step procedure of how to enable archive logging in an Oracle database.

First and foremost this requires you to change a few parameters in the parameter file and also shut down the database.

Insert the following lines in the pfile of your database.

LOG_ARCHIVE_DEST_1='location=D:\oracle_databases\bkp2\archive_log\ARCHIVELOG1.DBF'

LOG_ARCHIVE_DEST_STATE_1='enable'

LOG_ARCHIVE_START=TRUE

The above entries on the pfile ensure:

1) Location of the archive log files (in this case the archive logs are not mirrored)

2) The log_archive_dest_state_1 allows control over the availability state of the specified destination (1). The value enable indicates that Oracle can use the destination, whereas defer indicates that the location is temporarily disabled.

3) The archive logging is started.

Now shut down the database and start the database in mount mode with the pfile.

SQL> shut immediate

SQL> startup mount pfile=<location>

SQL> alter system archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create spfile from pfile=<location> --- to ensure further db startups can use the spfile.

Now ensure that the database is running archive log mode.

SQL>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination D:\oracle_databases\test2\archivelog\arc_

Oldest online log sequence 31

Next log sequence to archive 32

Current log sequence 32

SQL> alter system switch logfile;

Check whether the archive logs are being generated in the desired location.

That’s it!!

For more information on automatic archival, click here.

Wednesday, June 25, 2008

List tables and their corresponding tablespace name and data file [Oracle 9i]

Following is a way to list a table name and its corresponding tablespace name and data file.

SQL> select t.table_name,t.tablespace_name,df.file_name from dba_tables t, dba_data_files df

2 where t.tablespace_name = df.tablespace_name;

Further filters can be added as per requirement.

E.g.

To list the tables filtered by the owner of the table we write.

SQL> select t.table_name,t.tablespace_name,df.file_name from dba_tables t, dba_data_files df where

2 t.tablespace_name = df.tablespace_name and t.owner like 'TEMP1';

Additionally, to check the status of the tablespace we write,

SQL> select tablespace_name,status from dba_tablespaces;

Sunday, June 22, 2008

ALTER SYSTEM SWITCH LOGFILE hangs [Oracle 9i]

While learning Oracle DBA, I came across a situation, where I found the command

ALTER SYSTEM SWITCH LOGFILE hangs and there was nothing I could do about it.

On digging further, I found the cause.

My database was in manual archive log mode.

i.e. Automatic archiving was disabled.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Disabled

Archive destination C:\oracle\ora92\RDBMS

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12

In this case when all the online redo logs get filled up we need to manually archive the online redo log files.

Until then, the “ALTER SYSTEM SWITCH LOGFILE” command will make the database wait till the online redo log files

are archived with the command

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

You will need to issue the above command from a new session.

Wednesday, May 21, 2008

List the contents of a schema - Oracle 9i

Just a couple of steps to list all the objects of a schema.

When you describe the all_objects view you get the following.

SQL> desc all_objects;

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER NOT NULL VARCHAR2(30)

OBJECT_NAME NOT NULL VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NOT NULL NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED NOT NULL DATE

LAST_DDL_TIME NOT NULL DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

Now to list the contents of a schema to you will need to fire a SQL statement as follows.

SQL> SELECT object_name,owner,object_type FROM ALL_OBJECTS WHERE lower(owner) LIKE 'temp1';

where,

temp1 is the schema name.


Sunday, May 18, 2008

List and search all the files in the HDD on a windows box using simple OS commands and SQL loader (Oracle)

List and search all the files in the HDD on a windows box using simple OS commands and SQL loader (Oracle).

1) Goto command prompt and goto the root drive (c: or d:).

2) Type "dir /s /b >> d:\sample.txt" without the quotes

3) All the files in your drive (c: or d:) are listed in a raw format.

4) Get to your oracle database and create a table with just one column, say name.

create table filelist

(name varchar(2000))

I give 2000 as some path locations in sys vol info can sometimes be very very long.

5) At the OS level create a control file for the SQL loader.

--------------------Code starts-----------------

LOAD DATA

INFILE 'D:\sample.txt'

APPEND

INTO TABLE fileList

fields terminated by ''

(name char(2000))

--------------------Code ends-------------------

The

i) INFILE 'D:\sample.txt' should be the same file as the one in step 2 (Type "dir /s /b >> d:\sample.txt")

ii) (name char(2000)) /* if you dont specify the char(2000) you are bound to get a error

"sql loader Field in data file exceeds maximum length" */

6) Space occupied on the database table is around 8MB for around 99k odd records.

7) Run the SQL Loader command at the OS level.

sqlldr temp1/temp$1 control='D:\sample.ctl' log='D:\samplelog.txt'

where,

temp1/temp$1 are the username and password of the user/schema under which the table 'fileList' resides

sample.ctl is the file where the information about the table and source datafile is written and,

sample.log.txt is the log file which stores the logs of the SQL loader run

8) A successful run should typically have something like this.

=================log file================

Table FILELIST:

99479 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 96128 bytes(64rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 99479

Total logical records rejected: 0

Total logical records discarded: 0

=================log file ends================

9) After the successful run you can search for a particular in the drive by a simple SELECT statement

select name from filelist where name like '%file_name%';

And the output will be the files with the entire path location of the files in the drive.

The purpose of this snippet is just to show how SQL loader can be used.

This code can be modified further to accommodate a Java front end and be used as a file search utility for windows.

This process can also be automated by running all this in a batch file.

DISCLAIMER: Use the above code at your own risk and I will not be responsible for any damage caused to your machine.

Sunday, April 13, 2008

Move From Gmail To Google Apps

Hi Folks,

I feel many do not know a workaround to move mails and stuff from your gmail account to your google apps account.

I didn’t find any method while searching the net but thought of the following workaround and it worked fine.

1. Close all your mail applications (like Outlook or mobile device) from where you download your mails.

2. Get into your gmail account, goto settings, Forwarding and POP/IMAP and select Enable POP for all mail.

3. Get back to your google apps mail.

4. Enter the Gmail POP settings in your google Apps mail and all the mails and chats will be transferred from your gmail to your google Apps mail. It takes at least 6 hours for 200 odd MB of mail. So you can calculate how long it would take for you’re a/c.

5. Contact list doesn’t seem get transferred so you may have to do it manually by exporting it to excel and back to gppgle apps mail.

This method saves you the trouble of downloading mails to your HDD and uploading it again.

Hope this helps.

Cheers,

Arvind