execute immediate

1. sql_stmt := ‘insert into t’||tbcounter||’ values (:1, :2)’; execute immediate sql_stmt using s, s; 2. sqlstring := q'[insert into x values( ‘ || i || ‘)]’; execute immediate sqlstring; 3. begin execute immediate ‘create table abcd (efgh number)’; execute immediate ‘drop table abcd’; execute immediate ‘create table abcd (efgh varchar2(10))’; end; 4. begin execute… Continue reading execute immediate

Naming Convention

Oracle DBA’s must pay careful attention to the structure and naming conventions and naming standards within the database. All applications will reside within the same schema owner and naming conventions will be used to identify table/index components: Databases & SIDs SIDs and databases should be named: AAAT[n] where AAA is a meaningful three to six… Continue reading Naming Convention

Cleanup trace and dump files

The UNIX/Linux “find” command can be used to locate any external Oracle files including database files (dbf), alert log files, and all trace and dump files. The “which” command can also find files, and we can easily find the location of the SQL*Plus executable: root> which sqlplus /u01/home/oracle/product/10.2/bin/sqlplus For non-executable files, you can use the… Continue reading Cleanup trace and dump files

Temporary Tablespaces

Handling Oracle temporary tablespaces and Quick I/O You can create a new temporary tablespace using Quick I/O files. However, you cannot convert existing temporary tablespaces which use regular files to Quick I/O with the qio_getdbfiles command on Oracle9. By default, qio_getdbfiles skips any tablespaces marked TEMPORARY because they can be sparse, which means that not… Continue reading Temporary Tablespaces

Get the locked tables

Here is the query to get the locked tables in oracle SELECT l.inst_id , SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER , SUBSTR(L.SESSION_ID,1,3) SID , S.serial# , SUBSTR(O.OWNER ||’.’ ||O.OBJECT_NAME,1,40) OBJECT , P.SPID OS_PID , DECODE(L.LOCKED_MODE, 0,’NONE’, 1,’NULL’, 2,’ROW SHARE’, 3,’ROW EXCLUSIVE’, 4,’SHARE’, 5,’SHARE ROW EXCLUSIVE’, 6,’EXCLUSIVE’, NULL) LOCK_MODE FROM sys.GV_$LOCKED_OBJECT L , DBA_OBJECTS O , sys.GV_$SESSION S , sys.GV_$PROCESS… Continue reading Get the locked tables