Category: Oracle

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’);

set pagesize 300 set linesize 130 set head on set echo off accept tab prompt “Enter The Table Name -> ” select b.constraint_name “Cons Name”, b.status, decode(b.constraint_type, ‘P’, ‘PRIMARY’, ‘C’, ‘CHECK’, ‘U’, ‘UNIQUE’, ‘R’, ‘FOREIGN KEY’) “Cons Type”, substr(a.column_name, 1, 20) “Column Name”, decode(b.constraint_type, ‘C’, ‘ MUST NOT BE NULL’ , ‘ Position : ‘)…

Read More “find_cons.sql” »

set markup html preformat on Rem Rem Use the display table function from the dbms_xplan package to display the last Rem explain plan. Force serial option for backward compatibility Rem select plan_table_output from table(dbms_xplan.display(‘plan_table’,null,’serial’));

set markup html preformat on Rem Rem Use the display table function from the dbms_xplan package to display the last Rem explain plan. Use default mode which will display only relevant information Rem select * from table(dbms_xplan.display());