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.