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…
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…
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 : ‘)…
