Tuesday, December 30, 2008

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';"

Tuesday, December 23, 2008

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;
/

Monday, December 22, 2008

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)