Category: SQL scripts

SET LONG 1000000 UNDEFINE trigger_name SET PAGES 0 SET FEEDBACK OFF SET LINES 132 set echo off set numwidth 9 Set head off set verify off set time off set timing off COLUMN text FORMAT A302 word_wrapped COLUMN spool_name NEW_VALUE spool_name NOPRINT SELECT lower(trigger_name||’.ctr’) spool_name FROM dba_triggers WHERE trigger_NAME = UPPER(‘&&trigger_name’); SPOOL &spool_name select ‘CREATE…

Read More “crtgr.sql /* For creating trigger from data dictionary */” »

set pages 300 set lines 100 truncate table plan_table; EXPLAIN PLAN SET STATEMENT_ID = ‘pvm’ FOR SELECT * FROM emp; SELECT rpad(cardinality, 10, ‘ ‘) || ‘ ‘ || rpad(cost, 10, ‘ ‘) || ‘ ‘ || lpad(‘ ‘,level-1)||operation||’ ‘|| options||’ ‘||object_name “Rows Cost Plan” FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior…

Read More “eplan9i.sql” »

Renaming oracle instance (from UOCTEST to UAS) Note : Remember to rename datafile, logfile, controlfile, parameterfile and passwordfile. Aslo need to change listener. ******** Parameter File *************************** (1) check whether instance is using SPFIlE. using “show parameter spfile” (2) SQL> connect / as sysdba SQL> “create pfile from spfile” in 10g by default it will…

Read More “Rename Oracle Instance Name” »

DECLARE TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; emp_tab EmpTabTyp; BEGIN /* Retrieve employee record. */ SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7468; END;

DECLARE TYPE DeptRecTab IS TABLE OF dept%ROWTYPE; dept_recs DeptRecTab; CURSOR c1 IS SELECT deptno, dname, loc FROM dept WHERE deptno > 10; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO dept_recs; END;

create user uoc identified by uoc default tablespace uoc_data temporary tablespace temp; grant connect, resource to uoc; alter user uoc quota unlimited on uoc_data; alter user uoc quota unlimited on temp;

CREATE OR REPLACE TRIGGER “MESG”.ats_pdt_base_pre_ins_dbt BEFORE INSERT ON mesg.ats_product_base REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE BEGIN BEGIN set_audit_proc(‘I’, :new.usr_crtd, :new.dt_crtd, :new.usr_mdfd, :new.dt_mdfd); END; END; /

when you apply “ALTER TABLESPACE ABC BEGIN BACKUP;”, then status of all dbfs of that tablespace become ACTIVE in v$backup view. It become INACTIVE once you applu “ALTER TABLESPACE ABC END BACKUP;”.

create database PARESH CONTROLFILE REUSE MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 1000 MAXINSTANCES 1 MAXLOGHISTORY 1357 LOGFILE GROUP 1 ( ‘/db1/oradata/800PARESH/redo01a.log’, ‘/db2/oradata/800PARESH/redo01b.log’ ) SIZE 25M REUSE, GROUP 2 ( ‘/db1/oradata/800PARESH/redo02a.log’, ‘/db2/oradata/800PARESH/redo02b.log’ ) SIZE 25M REUSE, GROUP 3 ( ‘/db1/oradata/800PARESH/redo03a.log’, ‘/db2/oradata/800PARESH/redo03b.log’ ) SIZE 25M REUSE DATAFILE ‘/db3/oradata/800PARESH/system01.dbf’ size 200M reuse;

select * from user_objects where object_name = upper(‘&objnm’);