Category: SQL scripts

We can directly resize datafiles TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’SYSTEM’; FILE_NAME BYTES —————- ———- /…/dbsGNX.dbf 419430400 TEST.SQL>ALTER DATABASE DATAFILE ‘/…/dbsGNX.dbf’ RESIZE 390M; Database altered. TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’SYSTEM’; FILE_NAME BYTES ————— ———- /…/dbsGNX.dbf 408944640 But the minimum file size is the size of the extend the furthest in the datafile:…

Read More “How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile)” »

Step-1 : Find the Query and its SQL_ID to trace from AWR reports. Step-2 : Execute following query to find real execution plan: SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’8ska10yfjx4b5′ CONNECT BY PRIOR id = parent_id and sql_id=’8ska10yfjx4b5′;…

Read More “How to find the real execution plan and binds used in that explain plan in Oracle 10g??” »

SELECT owner, name, type FROM v$db_object_cache WHERE kept = ‘YES’ AND TYPE = ‘CURSOR’;

13:01:01 SYS@AWRTEST:-> select lebsz from x$kccle; LEBSZ ——— 512 512 512 3 rows selected. or df -g on unix prompt.

Oracle Identifiers: =================== In order not to make using Oracle confusing, there are some identifiers whose meaning must be totally clear if one want to work on Oracle: SID (System Identifier): ======================== A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less;…

Read More “Oracle Identifiers” »

To obtain the shared memory id and semaphore id for 8.1.X you can do either of the following: $ORACLE_HOME/bin/sysresv IPC Resources for ORACLE_SID “V817” : Shared Memory: ID KEY 14851 0x8a85a74c Semaphores: ID KEY 11206656 0x4bd4814c Oracle Instance alive for sid “V817” OR %sqlplus internal SQL> oradebug ipc Information written to trace file. Trace file…

Read More “DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS” »

SQL> variable komal number SQL> begin 2 :komal := add_me(45,12); 3 end; 4 / PL/SQL procedure successfully completed. SQL> print komal KOMAL ———- 57 SQL> variable tejal number; SQL> execute :tejal := add_me(10,20) PL/SQL procedure successfully completed. SQL> print tejal TEJAL ———- 30 SQL>

How to execute any DML statement in Parallel mode?? (1) ALTER SESSION ENABLE PARALLEL DML; (2) Apply Parallel(table_name, parallel_degree) in hint. You can see three new clumns (TQ, IN-OUT, PQ Distribut) in execution plan of “select * from table(dbms_xplan.display())”. These new columns will specify the query is running in parallel mode.

set echo off set head off set lines 130 set verify off accept v_owner prompt ‘Enter Owner Name : ‘ accept v_name prompt ‘Enter Object Name : ‘ accept v_type prompt ‘Enter Object Type : ‘ prompt Dependency tree for OWNER : &v_owner, OBJECT_NAME : &v_name OBJECT_TYPE : &v_type prompt ========================================================================================== SELECT rpad (rpad(‘*’, LEVEL*2,…

Read More “Find Multiple levels of object dependencies : depen.sql” »

Subject: Script To List Recursive Dependency Between Objects Doc ID: Note:139594.1 Type: BULLETIN Last Revision Date: 16-JUN-2004 Status: PUBLISHED PURPOSE ——- This script provides a listing of how objects depend on other given objects such as packages, package bodies or procedures recursively. SCOPE & APPLICATION ——————- You often get invalidated objects and would like to…

Read More “SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql” »