Move Tempfile and Online logs to a new location

One partition on the disk was almost full and I had to move database files around. This is what I did on Tempfile and Redo logs without re-creating control file. It also shows a way to re-size the tempfile (sometimes, you have to do that because it becomes too big).

SQL> conn / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 274995432 bytes
Database Buffers 260046848 bytes
Redo Buffers 1048576 bytes
Database mounted.

-- Drop the tempfile when the database is mounted
SQL> alter database tempfile '/var/oracle/oradata/emrep/temp01.dbf' drop including datafiles;

Database altered.

-- Rename the online redo logs. Make sure you copied all them to the new loaction before issuing below lines to avoid ORA-27037 error.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo01.log'
2 to '/var/oracle/oradata/emrep/redo01.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo02.log'
2 to '/var/oracle/oradata/emrep/redo02.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo03.log'
2 to '/var/oracle/oradata/emrep/redo03.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo04.log'
2 to '/var/oracle/oradata/emrep/redo04.log';
Database altered.

-- Open the database
SQL> alter database open;
Database altered.

-- Run "SQL> select * from v$logfile;" to verify all redo logs are in "ONLINE" status.

-- Verify the tempfile was dropped
SQL> select * from dba_temp_files;
no rows selected

-- Add tempfile to the new location. Make sure there is no same file in the location to avoid over-writing an existin one!
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/emrep/temp01.dbf'
2 SIZE 8012M REUSE AUTOEXTEND ON NEXT 102400000 MAXSIZE 12000M;
Tablespace altered.

-- Verify the result
SQL> select tablespace_name, file_name, bytes/(1024*1024) from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024)
--------------- --------- -----------------
TEMP /app/oracle/oradata/emrep/temp01.dbf 8012

-- Backup the new controlfile
SQL> alter database backup controlfile to trace;
Database altered.

Metalink note 99275.1 gives more details on "alter database rename file '/old path/filename.dbf' to '/new path/filename.dbf';"

Display CLOB data in Sql*Plus

If you have the session ID (982) and the session is actively running, you use following code to display the content in a CLOB field.

DECLARE

clob_field CLOB;
charbuf VARCHAR2(4000);
read_offset PLS_INTEGER :=1;
lob_length PLS_INTEGER;

BEGIN
-- for screen output
DBMS_OUTPUT.ENABLE (1000000);
-- Make sure that only one row gets returned!!
SELECT b.sql_fulltext INTO clob_field
FROM v$sqlarea b,v$session a
WHERE a.sid=982 and a.sql_address=b.address;

--get length of the clob field
lob_length := DBMS_LOB.GETLENGTH(clob_field);

--loop through clob and print in 255 length segments
WHILE (read_offset <= lob_length) LOOP
charbuf := DBMS_LOB.SUBSTR(clob_field, 255, read_offset);
dbms_output.put_line(trim(charbuf));
read_offset := read_offset + 255;
END LOOP;

END;
/

Display package code in Sql*Plus

All packages and procedures are stored in DBA_SOURCE, by one line in a row. Unless you use a third party tool, it is not easy to read them in Sql*Plus for a large package or procedure.

Here are the settings for displaying all code in a readable format:

set pagesize 0
set arraysize 10
set long 200
col text format a80 word_wrap

select text from dba_source
where name = 'PACK_NAME'
-- and owner = 'XXX'
;

Note the view dba_source is defined in 10g as

Name Type
----- ------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

Find a foreign key references to a table

Foreign key can be created in the table creation or by "alter table ..."

CREATE TABLE my_table
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constrint_fk
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (col_1, col_2, ... col_n)
);

ALTER TABLE my_table ADD (
  CONSTRAINT constrint_fk
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (col_1, col_2, ... col_n)
  enable validate);

Here, my_table is the child table to the foreign key constrint_fk. Columns (col_1, col_2, ... col_n) shall be the primary key or an unique index of the parent_table. Columns (column1, column2, ... column_n) shall be an index of my_table to avoid 'eng: TM' locks in a busy database when the parent_table gets DELETE or UPDATE operations frequently.

When I tried to truncate a table before refreshing it, I got error "ORA-02266: unique/primary keys in table referenced by enabled foreign keys". Below query will identify all foreign keys referenced by child tables:

SELECT DISTINCT a.table_name CHILD_NAME, a.constraint_name F_KEY, a.status, a.last_change
  FROM dba_constraints a, dba_constraints b
WHERE a.OWNER = UPPER('<owner>')
   AND   a.r_constraint_name = b.constraint_name
   AND   a.constraint_type = 'R'
   AND   b.constraint_type in ('P', 'U')
   AND   b.table_name = UPPER('<MY_TABLE>');

Then, run "alter table owner.CHILD_NAME disable constraint F_KEY;" to disable all constraints (foreign keys). After that, I can truncate table MY_TABLE. But if I want to drop the table, I need to drop the constraints first.

One table can have many foreign keys referencing multiple parent tables. Below query finds all foreign keys that links MY_TABLE to all parent tables:

SELECT c.table_name, c.constraint_name, c.constraint_type, c2.table_name P_table, c2.constraint_name P_constraint
  FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
 WHERE  c.OWNER = UPPER('<owner>')
     AND c.table_name =UPPER('<MY_TABLE>')
     AND c.constraint_TYPE = 'R';

When I delete/update data in table MY_TABLE, I do not need to disable foreign keys to all parent table generated from above query.

hanganalyze and systemstate

Oracle Support defines a "true" database hang as "an internal deadlock or a cyclical dependency between two or more processes." When dealing with DML locks (that is, enqueue type TM), Oracle is able to detect this dependency and roll back one of the processes to break the cyclical condition. On the other hand, when this situation occurs with internal kernel-level resources (such as latches or pins), Oracle is usually unable to automatically detect and resolve the deadlock.

To troubleshoot the hung database with the hang in progress, Oracle provides utilities to use kernel calls to identify blocking and waiting sessions.

- Hanganalyze

Hanganalyze is a newer utility to get a trace, and can be invoked from SQL *Plus or through oradebug (which is available when connected as SYS in the SQL *Plus utility).

SQL> connect / as sysdba
Connected.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/admin/biqa/udump/biqa_ora_1962226.trc
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/admin/biqa/udump/biqa_ora_1962226.trc
SQL> exit

or

SQL > alter session set events 'immediate trace name hanganalyze level 3';

Various levels and trace information:

1-2 Only hanganalyze output, no process dump at all
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF, LEAF_NW, IGN_DMP state)
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
10 Dump all processes (IGN state)

In RAC environment, above command is slightly different because an lock in an instance may be from processes of aonther instance. See Metalink Note 175006.1.

- SYSTEMSTATE Dump

In a database hang situation, Oracle Support may ask systemstate dumps to diagnose the root cause of the problem. It is important to take at least three of them a few minutes apart, on all instances of your database, to capture evidence shows whether a resource is still being held from one time to the next.

1. By oradebug
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> exit

In a RAC, oradebug allows you to dump the global system state by connecting to one node "SQL> oradebug -g all dump systemstate 266". The -g option will dump system states for all the instances. The SYSTEMSTATE dump/trace file can be found in the user_dump_dest directory on the instance where the dump was generated.

2. By Sql*Plus
It is recommended to reconnect every time so that it gets a new process ID and also the new trace files.
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
-- Take the 1st systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
-- Take the 2nd systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
-- Take the 3rd systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';

The dump file size depends on the size of the System Global Area (SGA), the number of sessions logged in, and the workload on the system. If using systemstate level 266 takes much longer (hours) than expected to generate the dump file, then end the systemstate dump and try level 258 (or even 10).

- SQL *Plus with the prelim option

When the entire database is hung and you cannot connect to SQL *Plus, you can try invoking SQL *Plus with the prelim option if you're using Oracle 10g or later. This attaches the process to the Oracle instance and no SQL commands are run. No login triggers or no pre-processing is done, and no SQL queries are allowed to run.

$sqlplus -prelim
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 11:42:23 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
SQL>