SELECT SQL_HANDLE,PLAN_NAME FROM DBA_SQL_PLAN_BASELINES WHERE PLAN_NAME ='SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9';Run the following PL/SQL as a privileged user (a DBA or "as sysdba")
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SQL_b4545d8efdd4c545',
plan_name => 'SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9');
DBMS_OUTPUT.put_line('Number of plans dropped: ' || l_plans_dropped);
END;
/
#!/bin/ksh
LOGFILE=$SCRIPT_BASE/log/restore_${ORACLE_SID}_`date +%Y%m%d%H%M%S`.log
export RECOVERY_CATALOG=catowner/**********@RMANCAT
nohup rman target / catalog $RECOVERY_CATALOG << EOF > $LOGFILE 2>&1 &
startup force nomount;
set DBID=1554594349;
run {
allocate channel t1 type sbt;
allocate channel t2 type sbt
send 'NB_ORA_POLICY=ora_dbserver1_00_netwzone1,NB_ORA_CLIENT=dbserver1-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
set until sequence < logseq > thread 1;
restore controlfile;
alter database mount;
restore database;
recover database;
}
alter database open resetlogs;
EOF
echo "RMAN restore started in background. Check logfile: $LOGFILE"
# Use the hash sign to prefix a comment # +---------------- minute (0 - 59) # | +------------- hour (0 - 23) # | | +---------- day of month (1 - 31) # | | | +------- month (1 - 12) # | | | | +---- day of week (0 - 7) (Sunday=0 or 7) # | | | | | # * * * * * command to be executed
EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'benoit';Display the plan using the DBMS_XPLAN.DISPLAY table function:
SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY);This query produces the following output:
Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."ENAME"='benoit')
15 rows selected.
EXPLAIN PLAN SET STATEMENT_ID='EXPL1' FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','EXPL1','serial'));
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
TABLESPACE tsd
)
ENABLE ROW MOVEMENT;
Note: The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.CREATE INDEX amount_sold_ix ON sales(amount_sold)
GLOBAL PARTITION BY RANGE(sale_month)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
Source: Oracle Documentation
asmcmd -p (-p option gives you the current directory at any given time - convenient) lsdg --> lists disk groups lsdsk -s --> -s shows various I/O related statistics of the disks lsdsk --candidate -p --> lists all available ASM disk candidates lsdsk --member -p --> lists all the current member of a disk group, as opposed to the candidates. or simply lsdsk -p
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
set linesize 300
set trimspool on
col name format a35
col time format a35
col guaranteed format a10
col "oldest flback SCN" format 9999999999999
col SCN format 9999999999999
set pagesize 200
spool chk_flashb.log
prompt =================================================
Prompt give me the
prompt * estimated flashback log size
prompt * retention target
prompt * current accumulated size of all flashback logs
prompt =================================================
select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb",
retention_target/60 "Hours of flback logs",
flashback_size/1024/1024 "Current Flbacklog Size mb"
from v$flashback_database_log
/
prompt ===============================================
Prompt How far back can the database be flashed back?
prompt ===============================================
select oldest_flashback_scn "oldest flback SCN",
oldest_flashback_time "oldest flback time"
from v$flashback_database_log
/
prompt =================================================
prompt show the restore points created the last 2 weeks
prompt =================================================
SELECT NAME,
SCN,
TIME,
DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE "guaranteed",
STORAGE_SIZE
FROM V$RESTORE_POINT
where time >= SYSDATE-14;
Prompt ===================================
prompt Show the flash recovery area usage
Prompt ===================================
select * from v$flash_recovery_area_usage;
Prompt ======================================
prompt I/O information for flashback logging
Prompt ======================================
select begin_time "Begin time",
end_time "End time",
round(flashback_data/1024/1024) "MB of flbk data written",
round(redo_data/1024/1024) "MB of redo data"
from v$flashback_database_stat
order by begin_time asc;
exit