Step by step test Sql Plan Management

–Test Version: 11.1.0.6
–Plan history is maintained only for repeatable SQL statements.A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged.
–How to turn on Automatic Plan Capture
Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE
–Manual Plan Loading
–Loading Plans from SQL Tuning Sets
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => ‘tset1′);
END;
/
–you can load plans [...]

Step by step test 11g adaptive cursor

DB version: 11.1.0.6
–
–Adaptive Cursors in 11g
–
–create a test table
T@ORCL11G>alter system flush shared_pool;
System altered.
T@ORCL11G>create table t2
as
select case when rownum = 1 then 1 else 99 end id, a.*
from dba_objects a
/
Table created.
T@ORCL11G>create index t2_id on t2(id);
Index created.
T@ORCL11G> begin
dbms_stats.gather_table_stats
( user, ‘T2′,
estimate_percent => 100,
method_opt=> ‘for all indexed columns’,cascade=>TRUE);
end;
/
PL/SQL procedure successfully completed.
–gather 100% is necessary as the data is skew
–bind [...]

Step by step test flashback data archives, a.k.a. Total Recall

–Flashback Data Archives, a.k.a. Total Recall
–  A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime.
–  Test version: 11.1.0.6
–create a system-wide default flashback archive on an existing tablespace.
conn / as sysdba
SYS@orcl11g>create flashback archive default fb tablespace example retention 1 year;
Flashback archive created.
–create [...]

Step by step test 11g new partition methods

DB version: 11.1.0.6
–new partition methods, ** are tested
**interval partition
**referencial partition
**virtual column partition
**system partition
*other new composite partitions
*Range-Range
*List-Range
*List-Hash
*List-List
*Interval-Range
*Interval-Hash
*Interval-List
*partition advisor, part of the SQL Access Advisor
*partition mode data pump, tables=[schema].[table_name].[partiton_name], PARTITION_OPTIONS = {NONE | DEPARTITION | MERGE}
–
–**interval partition, a kind of range partition
–
–use a sale table as example
create table sales
(prod_id        NUMBER,
cust_id        NUMBER,
time_id        DATE
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’)) STORE [...]

Step by step setup ASM using os files in 11g

Environment: Windows
DB version: 11.1.0.6
–Creating the ASM Instance Manually, using dbca is also ok
–create CSS service if it doesn’t exist in services
WINNT> <ORACLE_HOME>\bin\localconfig add
–create 2 1000MB os files as asmdisk
asmtool -create D:\asm\asmdisk1 1000
asmtool -create D:\asm\asmdisk2 1000
–if get an error msg, using commnd “net helpmsg errornumber” to know why
–create spfile
file: <ORACLE_HOME>\database\init+ASM.ora
INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
ASM_DISKSTRING=’D:\asm\*’
LARGE_POOL_SIZE=16M
_ASM_ALLOW_ONLY_RAW_DISKS= FALSE
–Note: The undocumented parameter _ASM_ALLOW_ONLY_RAW_DISKS is [...]

Step by step test oracle 11g physical standby database

–Enviroment
db version: 11.1.0.6.0
Primary          orcl11g        D:\oracle\oradata\orcl11g\orcl11g\
Physical standby     standby11g    C:\oracle\oradata\standby11g\
in oracle 11g using adrci command to check alert log, cause alert log in xml format under diag home
–Physical standby top new features, ** is tested
**Open and Recover physical standby at same time
**Snapshot standby, updatable standby database that provides full data protection for a [...]