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…

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…

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…

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…

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…

COMMAND_TYPE Values

If you are in the habit of looking at V$SQLAREA and V$SQL to see what is going on in the database {for those of use who occasionally step out of the “GUI DBA Tool” environment} you will see a lot of stuff in there. I tend to look for the most demanding SQL so I…

CHAR Function in SQL

chr(13): return carriage chr(10): new line break

Excel Char function

Return Carriage / New Line : char(10) or char(13)

Delete query takes too long

There could be several explanations as to why your query takes a long time: You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT, There could be a ON DELETE TRIGGER that does…

First blog post

This is the excerpt for your very first post.