Use srvctl to remove service or disable service

Before using srvctl to reomve service or database from the Oracle clusterware (OCR), you need to use 'crs_stat -t' to check if they are OFFLINE or not in Target and State columns. If they are not, you should stop them first to make them OFFLINE.

. Stop and remove the service using the below srvctl command (Guide B14197-08):
$ srvctl stop service -d db_name -s service_name_list
$ srvctl remove service -d db_name -s service_name

. Stop and remove the database using the below srvctl command:
$ srvctl stop instance -d db_name -i inst_name_list
$ srvctl stop database -d db_name
$ srvctl remove instance -d db_name -i inst_name
$ srvctl remove database -d db_name

. To disable the database from automatic startup by clusterware, use the below srvctl command when it is OFFLINE status:
$ srvctl disable database –d db_name

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.