Categories
Search
Categories
Archives

Review schema statistics

<pre>

set linesize 185
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off
Set Heading Off
Set Termout Off
Column Var_DB_LAST_ANALYZED_IND new_value Var_DB_LAST_ANALYZED_IND noprint
Select
to_char(max(last_analyzed), ‘DD-MM-YYYY HH24:MI’) Var_DB_LAST_ANALYZED_IND
from
dba_indexes
where
owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’)
;
Column Var_DB_LAST_ANALYZED_TAB new_value Var_DB_LAST_ANALYZED_TAB noprint
Select
to_char(max(last_analyzed), ‘DD-MM-YYYY HH24:MI’) Var_DB_LAST_ANALYZED_TAB
from
dba_tables
where
owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’)
;
Set Termout On
Set Heading On
clear breaks
prompt
prompt — ———————————————————————– —
prompt — Statistics —
prompt — ———————————————————————– —
prompt
column owner format a25 heading “Owner”
column table_owner format a25 heading “Owner”
column last_analyzed format a20 heading “Last|Analyzed”
column last_action format a20 heading “Last|Actions”
column nb format 999,999,999 heading “Count”
column nb_ins format 999,999,999 heading “Count|Inserts”
column nb_upd format 999,999,999 heading “Count|Updates”
column nb_del format 999,999,999 heading “Count|Deletes”
column MBytes format 9999999 heading ‘Size(Mb)’
column SBytes format 9999999 heading ‘Size(Mb)’
column sbp format 990.9 heading “%Calc.”
set Heading Off
Set Feedback Off
Set Verify Off
column status format a120 wrap heading “Status”
Select status_01 ||’ | ‘||status_02 status
From
(select ‘ Cascade ‘||lpad(replace(dbms_stats.get_param(‘cascade’),’DBMS_STATS.’, ”),22) Status_01 from dual)
, (select ‘ Degree ‘||lpad(dbms_stats.get_param(‘degree’),23) Status_02 from dual)
Union
Select status_01 ||’ | ‘||status_02 status
From
(select ‘ Estimate % ‘||lpad(replace(dbms_stats.get_param(‘estimate_percent’),’DBMS_STATS.’, ”),19) Status_01 from dual)
, (select ‘ Opt. ‘||lpad(replace(dbms_stats.get_param(‘method_opt’),’DBMS_STATS.’, ”),27) Status_02 from dual)
Union
Select status_01 ||’ | ‘||status_02 status
From
(select ‘ No Invalid.’||lpad(replace(dbms_stats.get_param(‘no_invalidate’),’DBMS_STATS.’, ”),19) Status_01 from dual)
, (select ‘ Granularity ‘||lpad(dbms_stats.get_param(‘granularity’),20) Status_02 from dual)
Union
Select status_01 ||’ | ‘||status_02 status
From
(Select ‘ Statistics Level ‘||Lpad(value,12) status_01 from V$PARAMETER where name=’statistics_level’)
, (select ‘ AutoStats ‘||lpad(dbms_stats.get_param(‘autostats_target’),22) Status_02 from dual)
;
Select status_01||’ | ‘||status_02 status
From
(Select ‘ Monitoring On ‘||Lpad(count(*),16) status_01 from Dba_Tables where monitoring = ‘YES’ and temporary = ‘N’ and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’))
, (Select ‘ Monitoring Off ‘||Lpad(count(*),17) status_02 from Dba_Tables where monitoring = ‘NO’ and temporary = ‘N’ and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’))
Union
Select status_01 ||’ | ‘||status_02 status
From
(Select ‘ Last Tab. An. ‘||Lpad(‘&Var_DB_LAST_ANALYZED_TAB.’,16) status_01 from dual)
, (select ‘ Last Ind. An. ‘||lpad(‘&Var_DB_LAST_ANALYZED_IND.’,18) Status_02 from dual)
Union
Select status_01
From
(Select ‘ Last Start Dt.’||Lpad(to_char(last_start_date, ‘DD-MM-YYYY HH24:MI’), 16) status_01 from Dba_Scheduler_Jobs Where JOB_NAME = ‘GATHER_STATS_JOB’)
;
Set Heading On
clear breaks
break on created on lst_ana on owner -
skip 1
column lst_ana heading ‘Last_Analyzed’ format a20
column owner heading ‘Owner’ format a15
column Table_Name heading ‘Table Name’ format a35
column Index_Name heading ‘Table Name’ format a35
column Cluster_Name heading ‘Cluster Name’ format a15
column Tablespace_Name heading ‘Tablespace’ format a16
column Ini_Ext heading ‘Init.|Ext(Kb)’ format 9999999
column Nex_Ext heading ‘Next.|Ext(Kb)’ format 9999999
Select
To_Char(last_analyzed, ‘DD-MM-YYYY HH24:MI’) lst_ana
, Owner
, Table_Name
, Tablespace_Name
, Initial_Extent/1024 Ini_Ext
, Next_Extent/1024 Nex_Ext
From
Dba_Tables
Where
last_analyzed > to_date(‘&Var_DB_LAST_ANALYZED_TAB.’, ‘DD-MM-YYYY HH24:MI’) – 0.1/24/60
Order
By last_analyzed
, Owner
, Table_Name
;
Select
To_Char(last_analyzed, ‘DD-MM-YYYY HH24:MI’) lst_ana
, Owner
, Index_Name
, Tablespace_Name
, Initial_Extent/1024 Ini_Ext
, Next_Extent/1024 Nex_Ext
From
Dba_Indexes
Where
last_analyzed > to_date(‘&Var_DB_LAST_ANALYZED_TAB.’, ‘DD-MM-YYYY HH24:MI’) – 0.1/24/60
Order
By last_analyzed
, Owner
, Index_Name
;
clear breaks
Set Heading On
Prompt
prompt — Schedule / Job / Window
prompt — ———————————————————————– —
column o format a5 word_wrapped heading “Owner”
column jn format a25 word_wrapped heading “Job Name|(Subname – Creator)”
column jt format a17 word_wrapped heading “Job Type”
column ja format a38 heading “Job Action”
column st format a9 word_wrapped heading “State”
column lsd format a16 word_wrapped heading “Last Start Date”
column nrd format a5 word_wrapped heading “Next|Run|Date”
column fc format 9999 heading “Fail.|Ct.”
column rc format 999999 heading “Run|Ct.”
column en format a6 word_wrapped heading “Enab.”
column ri format a30 word_wrapped heading “Rep.|Int.”
column sosn format a24 word_wrapped heading “Sched. |Owner|Sched. Name”
column sowc format a5 word_wrapped heading “Stop|On|Wind.|Close”
column ad format a5 word_wrapped heading “Auto|Drop”
column sn format a25 word_wrapped heading “Sched.|Name”
column jc format a21 word_wrapped heading “Job|Class”
column pn format a18 word_wrapped heading “Prog.|Name”
clear breaks
break on o -
skip 1
select
owner o
, job_name||'(‘||decode(job_subname,null,”,job_subname||’ – ‘) ||job_creator||’)’ jn
– , job_type jt
– , replace(Substr(job_action,1,38),chr(10),’ ‘) ja
, state st
, to_char(last_start_date, ‘DD-MM-YYYY HH24:MI’) lsd
, to_char(next_run_date, ‘DD-MM-YYYY HH24:MI’) nrd
– , substr(repeat_interval,1,30) ri
, failure_count fc
, run_count rc
, enabled en
– , decode(schedule_owner,null,”,schedule_owner||’ – ‘)||schedule_name sosn
, stop_on_window_close sowc
, auto_drop ad
, SCHEDULE_NAME sn
, JOB_CLASS jc
, program_name pn
from
Dba_Scheduler_Jobs
Where
JOB_NAME = ‘GATHER_STATS_JOB’
Order
By owner
, state
, enabled
, job_name
;
clear breaks
column o format a5 word_wrapped heading “Owner”
column pn format a18 word_wrapped heading “Prog.|Name”
column pt format a18 word_wrapped heading “Prog.|Type”
column pa format a42 word_wrapped heading “Prog.|Action”
column en format a8 word_wrapped heading “Enabled”
column de format a8 word_wrapped heading “Detached”
column co format a60 word_wrapped heading “Comments”
select
owner o
, program_name pn
, program_type pt
, program_action pa
, enabled en
, detached de
, comments co
from
dba_scheduler_programs
where
PROGRAM_NAME = ‘GATHER_STATS_PROG’
;
column wgn format a30 word_wrapped heading “Window Group Name”
column wn format a30 word_wrapped heading “Window Name”
Select
Window_Group_Name wgn
, Window_Name wn
From
DBA_SCHEDULER_WINGROUP_MEMBERS
;
column wn format a17 word_wrapped heading “Window Name”
column ri format a40 word_wrapped heading “Repeat Interval”
column du format a13 word_wrapped heading “Duration”
column nsd format a17 word_wrapped heading “Next|Start Date”
column lsd format a17 word_wrapped heading “Last|Start Date”
column wp format a8 word_wrapped heading “Wind.|Pri.”
column en format a8 word_wrapped heading “Enabled”
column ac format a8 word_wrapped heading “Active”
column co format a30 word_wrapped heading “Comments”
select
Window_Name wn
, REPEAT_INTERVAL ri
– , end_date ed
, duration du
, To_Char(next_start_date, ‘DD-MM-YYYY HH24:MI’) nsd
, To_Char(last_start_date, ‘DD-MM-YYYY HH24:MI’) lsd
, WINDOW_PRIORITY wp
, enabled en
, active ac
, comments co
From
dba_scheduler_windows
;
Prompt
Prompt
set feedback on

set linesize 185set pagesize 1000set trimout onset trimspool onSet Feedback offset timing offset verify off
Set Heading OffSet Termout Off
Column Var_DB_LAST_ANALYZED_IND new_value Var_DB_LAST_ANALYZED_IND noprint
Select to_char(max(last_analyzed), ‘DD-MM-YYYY HH24:MI’) Var_DB_LAST_ANALYZED_IND from dba_indexes where owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’);
Column Var_DB_LAST_ANALYZED_TAB new_value Var_DB_LAST_ANALYZED_TAB noprint
Select to_char(max(last_analyzed), ‘DD-MM-YYYY HH24:MI’) Var_DB_LAST_ANALYZED_TAB from dba_tables where owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’);
Set Termout OnSet Heading On
clear breaks
promptprompt — ———————————————————————– —prompt — Statistics —prompt — ———————————————————————– —prompt
column owner format a25 heading “Owner”column table_owner format a25 heading “Owner”column last_analyzed format a20 heading “Last|Analyzed” column last_action format a20 heading “Last|Actions” column nb format 999,999,999 heading “Count” column nb_ins format 999,999,999 heading “Count|Inserts” column nb_upd format 999,999,999 heading “Count|Updates” column nb_del format 999,999,999 heading “Count|Deletes” column MBytes format 9999999 heading ‘Size(Mb)’ column SBytes format 9999999 heading ‘Size(Mb)’ column sbp format 990.9 heading “%Calc.”
set Heading OffSet Feedback OffSet Verify Off
column status format a120 wrap heading “Status”
Select status_01 ||’ | ‘||status_02 status From (select ‘ Cascade ‘||lpad(replace(dbms_stats.get_param(‘cascade’),’DBMS_STATS.’, ”),22) Status_01 from dual) , (select ‘ Degree ‘||lpad(dbms_stats.get_param(‘degree’),23) Status_02 from dual)UnionSelect status_01 ||’ | ‘||status_02 status From (select ‘ Estimate % ‘||lpad(replace(dbms_stats.get_param(‘estimate_percent’),’DBMS_STATS.’, ”),19) Status_01 from dual) , (select ‘ Opt. ‘||lpad(replace(dbms_stats.get_param(‘method_opt’),’DBMS_STATS.’, ”),27) Status_02 from dual)UnionSelect status_01 ||’ | ‘||status_02 status From (select ‘ No Invalid.’||lpad(replace(dbms_stats.get_param(‘no_invalidate’),’DBMS_STATS.’, ”),19) Status_01 from dual) , (select ‘ Granularity ‘||lpad(dbms_stats.get_param(‘granularity’),20) Status_02 from dual)UnionSelect status_01 ||’ | ‘||status_02 status From (Select ‘ Statistics Level ‘||Lpad(value,12) status_01 from V$PARAMETER where name=’statistics_level’) , (select ‘ AutoStats ‘||lpad(dbms_stats.get_param(‘autostats_target’),22) Status_02 from dual);

Select status_01||’ | ‘||status_02 status From (Select ‘ Monitoring On ‘||Lpad(count(*),16) status_01 from Dba_Tables where monitoring = ‘YES’ and temporary = ‘N’ and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’)) , (Select ‘ Monitoring Off ‘||Lpad(count(*),17) status_02 from Dba_Tables where monitoring = ‘NO’ and temporary = ‘N’ and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘EXFSYS’, ‘MDSYS’, ‘OLAPSYS’, ‘WMSYS’, ‘TSMSYS’, ‘CTXSYS’, ‘SYSMAN’, ‘ORACLE_OCM’, ‘MDSYS’, ‘ORDSYS’, ‘OUTLN’, ‘XDB’))UnionSelect status_01 ||’ | ‘||status_02 status From (Select ‘ Last Tab. An. ‘||Lpad(‘&Var_DB_LAST_ANALYZED_TAB.’,16) status_01 from dual) , (select ‘ Last Ind. An. ‘||lpad(‘&Var_DB_LAST_ANALYZED_IND.’,18) Status_02 from dual)UnionSelect status_01 From (Select ‘ Last Start Dt.’||Lpad(to_char(last_start_date, ‘DD-MM-YYYY HH24:MI’), 16) status_01 from Dba_Scheduler_Jobs Where JOB_NAME = ‘GATHER_STATS_JOB’);

Set Heading On
clear breaksbreak on created on lst_ana on owner -skip 1
column lst_ana heading ‘Last_Analyzed’ format a20column owner heading ‘Owner’ format a15column Table_Name heading ‘Table Name’ format a35column Index_Name heading ‘Table Name’ format a35column Cluster_Name heading ‘Cluster Name’ format a15column Tablespace_Name heading ‘Tablespace’ format a16column Ini_Ext heading ‘Init.|Ext(Kb)’ format 9999999column Nex_Ext heading ‘Next.|Ext(Kb)’ format 9999999
Select To_Char(last_analyzed, ‘DD-MM-YYYY HH24:MI’) lst_ana , Owner , Table_Name , Tablespace_Name , Initial_Extent/1024 Ini_Ext , Next_Extent/1024 Nex_Ext From Dba_TablesWhere last_analyzed > to_date(‘&Var_DB_LAST_ANALYZED_TAB.’, ‘DD-MM-YYYY HH24:MI’) – 0.1/24/60 Order By last_analyzed , Owner , Table_Name;
Select To_Char(last_analyzed, ‘DD-MM-YYYY HH24:MI’) lst_ana , Owner , Index_Name , Tablespace_Name , Initial_Extent/1024 Ini_Ext , Next_Extent/1024 Nex_Ext From Dba_IndexesWhere last_analyzed > to_date(‘&Var_DB_LAST_ANALYZED_TAB.’, ‘DD-MM-YYYY HH24:MI’) – 0.1/24/60 Order By last_analyzed , Owner , Index_Name;
clear breaks
Set Heading On
Promptprompt — Schedule / Job / Windowprompt — ———————————————————————– —
column o format a5 word_wrapped heading “Owner”column jn format a25 word_wrapped heading “Job Name|(Subname – Creator)”column jt format a17 word_wrapped heading “Job Type”column ja format a38 heading “Job Action”column st format a9 word_wrapped heading “State”column lsd format a16 word_wrapped heading “Last Start Date”column nrd format a5 word_wrapped heading “Next|Run|Date”column fc format 9999 heading “Fail.|Ct.”column rc format 999999 heading “Run|Ct.”column en format a6 word_wrapped heading “Enab.”column ri format a30 word_wrapped heading “Rep.|Int.”column sosn format a24 word_wrapped heading “Sched. |Owner|Sched. Name”column sowc format a5 word_wrapped heading “Stop|On|Wind.|Close”column ad format a5 word_wrapped heading “Auto|Drop”column sn format a25 word_wrapped heading “Sched.|Name”column jc format a21 word_wrapped heading “Job|Class”column pn format a18 word_wrapped heading “Prog.|Name”
clear breaksbreak on o -skip 1
select owner o , job_name||'(‘||decode(job_subname,null,”,job_subname||’ – ‘) ||job_creator||’)’ jn– , job_type jt– , replace(Substr(job_action,1,38),chr(10),’ ‘) ja , state st , to_char(last_start_date, ‘DD-MM-YYYY HH24:MI’) lsd , to_char(next_run_date, ‘DD-MM-YYYY HH24:MI’) nrd– , substr(repeat_interval,1,30) ri , failure_count fc , run_count rc , enabled en– , decode(schedule_owner,null,”,schedule_owner||’ – ‘)||schedule_name sosn , stop_on_window_close sowc , auto_drop ad , SCHEDULE_NAME sn , JOB_CLASS jc , program_name pn from Dba_Scheduler_Jobs Where JOB_NAME = ‘GATHER_STATS_JOB’ Order By owner , state , enabled , job_name;
clear breaks
column o format a5 word_wrapped heading “Owner”column pn format a18 word_wrapped heading “Prog.|Name”column pt format a18 word_wrapped heading “Prog.|Type”column pa format a42 word_wrapped heading “Prog.|Action”column en format a8 word_wrapped heading “Enabled”column de format a8 word_wrapped heading “Detached”column co format a60 word_wrapped heading “Comments”
select owner o , program_name pn , program_type pt , program_action pa , enabled en , detached de , comments co from dba_scheduler_programs where PROGRAM_NAME = ‘GATHER_STATS_PROG';

column wgn format a30 word_wrapped heading “Window Group Name”column wn format a30 word_wrapped heading “Window Name”
Select Window_Group_Name wgn , Window_Name wn From DBA_SCHEDULER_WINGROUP_MEMBERS;

column wn format a17 word_wrapped heading “Window Name”column ri format a40 word_wrapped heading “Repeat Interval”column du format a13 word_wrapped heading “Duration”column nsd format a17 word_wrapped heading “Next|Start Date”column lsd format a17 word_wrapped heading “Last|Start Date”column wp format a8 word_wrapped heading “Wind.|Pri.”column en format a8 word_wrapped heading “Enabled”column ac format a8 word_wrapped heading “Active”column co format a30 word_wrapped heading “Comments”
select Window_Name wn , REPEAT_INTERVAL ri– , end_date ed , duration du , To_Char(next_start_date, ‘DD-MM-YYYY HH24:MI’) nsd , To_Char(last_start_date, ‘DD-MM-YYYY HH24:MI’) lsd , WINDOW_PRIORITY wp , enabled en , active ac , comments co From dba_scheduler_windows;
PromptPrompt
set feedback on

</pre>

External Files

The DDL to create the external table will be as follows:
CREATE TABLE emp_external
( emp_id   NUMBER(4)
, ename    VARCHAR2(12)
, job      VARCHAR2(12)
, mgr_id   NUMBER(4)
, hiredate DATE
, salary   NUMBER(8)
, comm     NUMBER(8)
, dept_id  NUMBER(2))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TEST_FILES
ACCESS PARAMETERS (records delimited BY newline
fields terminated BY ‘,’)
LOCATION (‘emp_load.dat’)
);
– =======================================
DECLARE
BEGIN
/* UTL_FILE.FRENAME (
location  IN VARCHAR2,
filename  IN VARCHAR2,
dest_dir  IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);
*/
UTL_FILE.FRENAME(‘TEST_FILES’,’emp1.dat’, ‘TEST_FILES’, ‘employee_records.dat’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(substr(sqlcode,1,100));
END;
– =======================================
 Create table export_empl_info
organization external
( type oracle_datapump
default directory xtern_data_dir
location (‘empl_info_rpt.dmp’) ) as select * from empl_info;

DATES

col sessiontimezone format a30

SELECT sessiontimezone, current_date  FROM DUAL;

ALTER SESSION SET TIME_ZONE = ‘-5:0′;

SELECT sessiontimezone, current_date FROM DUAL;

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS';

SELECT sessiontimezone, current_date FROM DUAL;

ALTER SESSION SET TIME_ZONE = ‘-7:0′;

SELECT sessiontimezone, current_date FROM DUAL;

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY';

SELECT add_months(SYSDATE, 2) FROM DUAL;

SELECT add_months(TO_DATE(’20-JAN-2010′), 1) FROM DUAL;

SELECT SYSDATE + 1 FROM DUAL;

SELECT SYSDATE - 1 FROM DUAL;

col drows format a40

SELECT DUMP(SYSDATE) DROWS FROM DUAL;

SELECT DUMP(SYSDATE, 8) DROWS FROM DUAL;

SELECT DUMP(SYSDATE, 16) DROWS FROM DUAL;

SELECT TO_CHAR(SYSDATE, ‘HH:MI:SS’) FROM DUAL;

SELECT TO_CHAR(SYSDATE + INTERVAL ’10’ MINUTE, ‘HH:MI:SS’) FROM DUAL;

SELECT TO_CHAR(SYSDATE – INTERVAL ’10’ MINUTE, ‘HH:MI:SS’) FROM DUAL;

 

ALTER SESSION SET time_zone = local;

SELECT DBTIMEZONE FROM DUAL;

SELECT CURRENT_TIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = ‘-5:0′;

SELECT DBTIMEZONE FROM DUAL;

SELECT CURRENT_TIMESTAMP FROM DUAL;

ALTER SESSION SET time_zone = local;

SELECT SYSTIMESTAMP FROM DUAL;

SELECT SYSTIMESTAMP AT TIME ZONE dbtimezone FROM DUAL;

EXTRACT (<type> FROM <datetime | interval>)

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR

SELECT EXTRACT(YEAR FROM DATE ‘2012-01-01′) FROM DUAL;

SELECT FROM_TZ(TIMESTAMP ‘2007-11-20 08:00:00′, ‘3:00′) FROM DUAL;

 

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = ‘-8:00′;

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, ‘DD-MON-RR HH.MI.SSXFF PM’));

 

col TR format a10

SELECT  EXTRACT(year FROM SYSTIMESTAMP) EY,
EXTRACT(month FROM SYSTIMESTAMP) EM,
EXTRACT(day FROM SYSTIMESTAMP) ED,
EXTRACT(hour FROM SYSTIMESTAMP) EH,
EXTRACT(minute FROM SYSTIMESTAMP) EM,
EXTRACT(second FROM SYSTIMESTAMP) ES,
EXTRACT(timezone_hour FROM SYSTIMESTAMP) TH,
EXTRACT(timezone_minute FROM SYSTIMESTAMP) TM,
EXTRACT(timezone_region FROM SYSTIMESTAMP) TR,
EXTRACT(timezone_abbr FROM SYSTIMESTAMP) TA  FROM DUAL;

col TR format a10

ALTER SESSION SET time_zone = ‘US/Eastern';

SELECT  EXTRACT(year FROM CURRENT_TIMESTAMP) EY,
EXTRACT(month FROM CURRENT_TIMESTAMP) EM,
EXTRACT(day FROM CURRENT_TIMESTAMP) ED,
EXTRACT(hour FROM CURRENT_TIMESTAMP) EH,
EXTRACT(minute FROM CURRENT_TIMESTAMP) EM,
EXTRACT(second FROM CURRENT_TIMESTAMP) ES,
EXTRACT(timezone_hour FROM CURRENT_TIMESTAMP) TH,
EXTRACT(timezone_minute FROM CURRENT_TIMESTAMP) TM,
EXTRACT(timezone_region FROM CURRENT_TIMESTAMP) TR,
EXTRACT(timezone_abbr FROM CURRENT_TIMESTAMP ) TA  FROM DUAL;

 

LAST_DAY(<date>)

GREATEST(<date>, <date>, <date>, …)

 

DataPump

conn / as sysdba

desc dba_directories

col owner format a10
col directory_path format a70

SELECT * FROM dba_directories;

CREATE OR REPLACE DIRECTORY data_pump_dir AS ‘/tmp';
– default is $ORACLE_BASE/admin/<database_name>/dpdump

GRANT export full database TO scott;

FULL export

expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=Full_%U.dmpFULL=y

SCHEMA export

expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=Schema_%U.dmp SCHEMAS=scott

TABLE export

expdp scott/tiger DUMPFILE=data_pump_dir:table01.dmp  TABLES=emp, dept

TABLESPACE export

expdp scott/tiger DUMPFILE=data_pump_dir:tblsp.dmp TABLESPACES=,users  TRANSPORT_FULL_CHECK=y

COMPRESSION export

expdp scott/tiger SCHEMAS=scott DIRECTORY=data_pump_dir DUMPFILE=compr.dmp COMPRESSION=none

CONTENT export

expdp scott/tiger SCHEMAS=scott DIRECTORY=data_pump_dir DUMPFILE=content.dmp CONTENT=metadata_only

ENCRYPTION export

expdp scott DIRECTORY=data_pump_dir DUMPFILE=encrypt.dmp ENCRYPTION=all ENCRYPTION_ALGORITHM=aes256 ENCRYPTION_MODE=dual  ENCRYPTION_PASSWORD=white

ESTIMATE export

expdp scott SCHEMAS=scott DIRECTORY=data_pump_dir DUMPFILE=est.dmp ESTIMATE=blocks

expdp scott SCHEMAS=scott DIRECTORY=data_pump_dir DUMPFILE=est.dmp ESTIMATE_ONLY=y

expdp scott SCHEMAS=scott DIRECTORY=data_pump_dir DUMPFILE=est.dmp ESTIMATE=statistics

ATTACH export

expdp scott DIRECTORY=data_pump_dir DUMPFILE=attach.dmp ATTACH=job_name 

EXCLUDE export

expdp scott SCHEMAS=scott DIRECTORY=data_pump_dir DUMPFILE=exclude.dmp EXCLUDE=constraint|ref_constraint|grant|users|view,package,function| OR EXCLUDE=SCHEMA:\“=’Finance’\”    (name of the schema)

Other options are:

FILESIZE=5M

INCLUDE=table|procedure|\”IN (‘SERVERS’, ‘SERV_INST’)\”|INDEX:\“LIKE ‘PK%\

NETWORK_LINK=tnsnames_entry

NOLOGFILE=y

PARALLEL=2

PARFILE=data_pump_dir:parfile.par

QUERY=emp:\“WHERE emp_id = ”437”\

REMAP_DATA=scott.servers.srvr_id:tom.hosts.srvr_id

REUSE_DUMPFILES=Y

SAMPLE=\“SCOTT.EMP:10\”     – 10 is percentage.

STATUS=5    — how often to show a status

VERSION=latest|version

Flashback Options

–SELECT dbms_flashback.get_system_change_number FROM dual;

FLASHBACK_SCN=69455020

SELECT dbms_flashback.get_system_change_number FROM dual;

SELECT SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number)FROM dual;

FLASHBACK_TIME=\“TO_TIMESTAMP(’01-JAN-2012 00:00:01′, ‘DD-MON-YYYY HH24:MI:SS’)\

FULL import

impdp scott DIRECTORY=data_pump_dir DUMPFILE=import.dmp FULL=y

TABLE import

impdp scott DUMPFILE=data_pump_dir:table.dmp TABLES=emp, dept

TABLESPACE import

impdp scott DUMPFILE=data_pump_dir:tblsp.dmpTABLESPACES=scott,users TRANSPORT_FULL_CHECK=y

dbms_java.grant_permission

$sqlplus /nolog

connect sys/change_on_install as sysdba

call dbms_java.grant_policy_permission(‘SCOTT’,’SYS’,’java.io.FilePermission’,’*’);

connect scott/tiger

begin
dbms_java.grant_permission(‘SCOTT’,’java.io.FilePermission’,’/tmp’,’read,write,execute,delete’);
end;
/

Simply grant the user rights to the directory tree instead of all the files in a particular directory.

begin

dbms_java.grant_permission(‘WD’,’java.io.FilePermission’,’/home/scott/-‘,

‘read,write,execute,delete’);

end;
/

 The use of “-” instead of “*” will give access to all files recursively through all sub-directories.

 

date.sql

ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI DDMONRR'; 

chkstats.sql

--- chkstats.sql get a snap shot of sys & sess stats from when chkstatc was run 

col STAT_NAME format a40 
col STAT_VALUE format 999,999,999,999 

select c.STAT_NAME, s.SESS_VALUE - c.SESS_VALUE SESS_VALUE, s.SYS_VALUE- c.SYS_VALUE SYS_VALUE 
from chkstat_tmptab c, 
  (SELECT  name.name stat_name, sstat.value SYS_VALUE, mstat.value SESS_VALUE 
   FROM  v$sysstat sstat, v$statname name, v$mystat mstat 
   WHERE sstat.statistic# = name.statistic# 
   and   mstat.statistic# = name.statistic# 
   and   name.name in 
   ('recursive calls','db block gets','consistent gets','physical reads','redo size','bytes sent via SQL*Net to client' 
   ,'bytes received via SQL*Net from client','SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)')) s 
where c.STAT_NAME = s.STAT_NAME 
order by  c.STAT_NAME; 

orasar.sql

---orasar.sql listing of System Activity for the past 24 hours 
set pages 1000 lines 160 
col SNAP_Times format a22 
col logons format 99999 
col CPU format 99999999 
col Execs format 9999999 
col MBS_S format 99999 
col MBS_R format 99999 
col REDOMB format 99999 
col PARSE format 999999 
col Dsorts format 999999 
col eqWaits format 999999 

--- Set up title 
set termout off serveroutput off heading off timing off 
col title_name new_value title_name 
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from  v$instance; 
set termout on serveroutput on heading on 

TTitle Left '###  DB System Activity Report (&&hours hrs)  -  ' title_name skip 2 

select to_char(lag(si.SNAP_TIME) over (order by si.dbid, si.instance_number, si.snap_id),'DDMONRR HH24:MI') ||'-'|| to_char(si.SNAP_TIME,'HH24:MI') as SNAP_Times, 
st.logons, st.Execs, st.Parse, st.CPU, st.eqWaits, st.DBgets, st.ConGets, st.PhyReads, st.ConChgs, st.PhyWrts, st.Dsorts, 
round(st.Redo/(1024*1024)) REDOMB, round(st.BytesS/(1024*1024)) MBS_S, round(st.BytesR/(1024*1024)) MBS_R 
from stats$snapshot si, 
      (select SNAP_ID, 
              case when logons < lag(logons,1,0) over (order by snap_id) then logons 
                   when lag(logons,1,0) over (order by snap_id) = 0 then 0 
                   else logons - lag(logons,1,0) over (order by snap_id) end as logons, 
              case when Execs < lag(Execs,1,0) over (order by snap_id) then Execs 
                   when lag(Execs,1,0) over (order by snap_id) = 0 then 0 
                   else Execs - lag(Execs,1,0) over (order by snap_id) end as Execs, 
              case when Parse < lag(Parse,1,0) over (order by snap_id) then Parse 
                   when lag(Parse,1,0) over (order by snap_id) = 0 then 0 
                   else Parse - lag(Parse,1,0) over (order by snap_id) end as Parse, 
              case when CPU < lag(CPU,1,0) over (order by snap_id) then CPU 
                   when lag(CPU,1,0) over (order by snap_id) = 0 then 0 
                   else CPU - lag(CPU,1,0) over (order by snap_id) end as CPU, 
              case when ConGets < lag(ConGets,1,0) over (order by snap_id) then ConGets 
                   when lag(ConGets,1,0) over (order by snap_id) = 0 then 0 
                   else ConGets - lag(ConGets,1,0) over (order by snap_id) end as ConGets, 
              case when PhyReads < lag(PhyReads,1,0) over (order by snap_id) then PhyReads 
                   when lag(PhyReads,1,0) over (order by snap_id) = 0 then 0 
                   else PhyReads - lag(PhyReads,1,0) over (order by snap_id) end as PhyReads, 
              case when PhyWrts < lag(PhyWrts,1,0) over (order by snap_id) then PhyWrts 
                   when lag(PhyWrts,1,0) over (order by snap_id) = 0 then 0 
                   else PhyWrts - lag(PhyWrts,1,0) over (order by snap_id) end as PhyWrts, 
              case when DBgets < lag(DBgets,1,0) over (order by snap_id) then DBgets 
                   when lag(DBgets,1,0) over (order by snap_id) = 0 then 0 
                   else DBgets - lag(DBgets,1,0) over (order by snap_id) end as DBgets, 
              case when eqWaits < lag(eqWaits,1,0) over (order by snap_id) then eqWaits 
                   when lag(eqWaits,1,0) over (order by snap_id) = 0 then 0 
                   else eqWaits - lag(eqWaits,1,0) over (order by snap_id) end as eqWaits, 
              case when ConChgs < lag(ConChgs,1,0) over (order by snap_id) then ConChgs 
                   when lag(ConChgs,1,0) over (order by snap_id) = 0 then 0 
                   else ConChgs - lag(ConChgs,1,0) over (order by snap_id) end as ConChgs, 
              case when dsorts < lag(dsorts,1,0) over (order by snap_id) then dsorts 
                   when lag(dsorts,1,0) over (order by snap_id) = 0 then 0 
                   else dsorts - lag(dsorts,1,0) over (order by snap_id) end as dsorts, 
              case when Redo < lag(Redo,1,0) over (order by snap_id) then Redo 
                   when lag(Redo,1,0) over (order by snap_id) = 0 then 0 
                   else Redo - lag(Redo,1,0) over (order by snap_id) end as Redo, 
              case when BytesS < lag(BytesS,1,0) over (order by snap_id) then BytesS 
                   when lag(BytesS,1,0) over (order by snap_id) = 0 then 0 
                   else BytesS - lag(BytesS,1,0) over (order by snap_id) end as BytesS, 
              case when BytesR < lag(BytesR,1,0) over (order by snap_id) then BytesR 
                   when lag(BytesR,1,0) over (order by snap_id) = 0 then 0 
                   else BytesR - lag(BytesR,1,0) over (order by snap_id) end as BytesR 
          from 
          (select SNAP_ID, 
             max(decode(NAME ,'logons cumulative', value )) logons, 
             max(decode(NAME ,'CPU used by this session', value )) cpu, 
             max(decode(NAME ,'consistent gets', value )) ConGets, 
             max(decode(NAME ,'physical reads', value )) PhyReads, 
             max(decode(NAME ,'physical writes', value )) PhyWrts, 
             max(decode(NAME ,'parse count (total)', value )) Parse, 
             max(decode(NAME ,'execute count', value )) Execs, 
             max(decode(NAME ,'enqueue waits', value )) eqWaits, 
             max(decode(NAME ,'db block gets', value )) DBgets, 
             max(decode(NAME ,'consistent changes', value )) ConChgs, 
             max(decode(NAME ,'sorts (disk)', value )) dsorts, 
             max(decode(NAME ,'redo size', value )) Redo, 
             max(decode(NAME ,'bytes sent via SQL*Net to client', value )) BytesS, 
             max(decode(NAME ,'bytes received via SQL*Net from client', value )) BytesR 
      from stats$sysstat 
      where NAME in  ('logons cumulative','CPU used by this session','enqueue waits','db block gets','consistent gets','physical reads', 
                      'consistent changes','physical writes','parse count (total)','execute count','redo size', 
                      'bytes sent via SQL*Net to client','bytes received via SQL*Net from client','sorts (disk)') 
      group by SNAP_ID)) st 
where si.SNAP_ID = st.SNAP_ID 
and si.SNAP_TIME > sysdate - &&hours/24 
order by si.SNAP_TIME; 

TTitle off

logs.sql

--- logs.sql Log switch summary 
set pages 1000 lines 160 

COLUMN DAY   FORMAT a5 
COLUMN H00   FORMAT 99     HEADING '00' 
COLUMN H01   FORMAT 99     HEADING '01' 
COLUMN H02   FORMAT 99     HEADING '02' 
COLUMN H03   FORMAT 99     HEADING '03' 
COLUMN H04   FORMAT 99     HEADING '04' 
COLUMN H05   FORMAT 99     HEADING '05' 
COLUMN H06   FORMAT 99     HEADING '06' 
COLUMN H07   FORMAT 99     HEADING '07' 
COLUMN H08   FORMAT 99     HEADING '08' 
COLUMN H09   FORMAT 99     HEADING '09' 
COLUMN H10   FORMAT 99     HEADING '10' 
COLUMN H11   FORMAT 99     HEADING '11' 
COLUMN H12   FORMAT 99     HEADING '12' 
COLUMN H13   FORMAT 99     HEADING '13' 
COLUMN H14   FORMAT 99     HEADING '14' 
COLUMN H15   FORMAT 99     HEADING '15' 
COLUMN H16   FORMAT 99     HEADING '16' 
COLUMN H17   FORMAT 99     HEADING '17' 
COLUMN H18   FORMAT 99     HEADING '18' 
COLUMN H19   FORMAT 99     HEADING '19' 
COLUMN H20   FORMAT 99     HEADING '20' 
COLUMN H21   FORMAT 99     HEADING '21' 
COLUMN H22   FORMAT 99     HEADING '22' 
COLUMN H23   FORMAT 99     HEADING '23' 
COLUMN TOTAL FORMAT 999 HEADING 'Total' 

set heading off timing off 
select ''  from dual; 
select '['||sysdate||']['||VERSION||'] '||INSTANCE_NAME||' - '||HOST_NAME||' 
---------------------------------------------------------------------------- ' from  v$instance; 
set heading on 

SELECT 
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                          DAY 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23 
, COUNT(*)                                                                      TOTAL 
FROM 
v$log_history  a 
WHERE (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') >= trunc(sysdate -20)) 
AND (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') <= trunc(sysdate)) 
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) 
order by SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5); 

sched.sql

set pages 1000 lines 160 
col OWNER           format a12 
col JOB_NAME        format a24 
col JOB_ACTION      format a42 
col JOB_MODE        format a20 
col STATE           format a9 
col START_DATE      format a15 
col LAST_START_DATE format a15 
col NEXT_RUN_DATE   format a15 
col LOG_DATE        format a15 
col Run             format 9999 
col fails           format 9999 


set termout off serveroutput off heading off timing off 
col title_name new_value title_name 
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from  v$instance; 
@date 
ALTER SESSION SET nls_timestamp_tz_format = 'HH24:MI DDMONRR'; 
ALTER SESSION SET nls_timestamp_format    = 'HH24:MI DDMONRR'; 
set termout on serveroutput on heading on 

TTitle Left '###  Check V$DATAPUMP_JOB -  ' title_name skip 2 
select LOG_ID,LOG_DATE,OWNER,JOB_NAME,OPERATION,STATUS from DBA_SCHEDULER_JOB_LOG where LOG_DATE > sysdate -7; 

TTitle Left '###  Check DBA_SCHEDULER_JOBS -  ' title_name skip 2 
select OWNER,JOB_NAME,JOB_ACTION,START_DATE,STATE,RUN_COUNT Run,FAILURE_COUNT Fails,LAST_START_DATE,NEXT_RUN_DATE 
from DBA_SCHEDULER_JOBS; 

TTitle Left '###  Check v$SCHEDULER_RUNNING_JOBS -  ' title_name skip 2 
select * from v$SCHEDULER_RUNNING_JOBS; 

TTitle off