Oracle 10g New Features Quick Guide

1. Flashback Table
a. Revival of dropped table.
b. It’s like windows’ recycle bin which stores deleted data. Same way in Oracle
10g Recyclebin is maintaining.
c. The table and its associated objects are placed in a logiacal container known
as the ‘recycle bin’
d. Commands Used:
i. Select * from tab; (which shows dropped table list. Like BIN$.....)
ii. Show recyclebin; (will show the original table name & drop time)
e. We can take the table back as original using following command.
i. Flashback table <table_name> to before drop;
ii. Select * from tab;
f. We can Purge recyclebin or a table from recyclebin
i. Purge recyclebin;
ii. Drop table <table_name> purge;
g. When a tablespace is completely filled up with recycle bin data such that the
datafiles have to extend to make room for more data, the tablespace is said
to be under “space pressure”. In that scenario, objects are automatically
purged from the recycle bin in a first-first-out manner.

2. Tablespace Management
a. Oracle Database 10g introduces a new tablespace called SYSAUX that holds
the objects of DBSNMP, ODM etc. schemas. This tablespace is created
automatically during database creation and is locally managed. Objects in
SYSAUX can be recovered as any normal user object while the database itself
remains operational.
b. Renaming a tablespace
i. You can simply rename an existing tablesapce (System & Sysaux
excepted), whether permanent or temporary, usin the following
command.
ALTER TABLESPACE <OLDNAME> RENAME TO <NEWNAME>;

3. Oracle Data Pump

a. Refer separate document related to Data Pump

4. Flashback New Features

a. Flashback Query:
Flashback Query allows the contents of a table to be queried with reference to a
specific point in time, using the AS OF clause. It is same as DBMS_FLASHBACK
functionality, but a more convenient form.

CREATE TABLE flashback_query_test (

id NUMBER(10)
);

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM

v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
 722452 2004-03-29 13:34:12

INSERT INTO flashback_query_test (id) VALUES (1);

COMMIT;

SELECT COUNT(*) FROM flashback_query_test;

COUNT(*)
----------
1

SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-

03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

COUNT(*)
----------
0

SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;

COUNT(*)
----------
0

b. Flashback Version Query:

i. Flashback version query allows the versions of a specific row to be
tracked during a specified time period using the VERSIONS BETWEEN
caluse.

CREATE TABLE flashback_version_query_test (

id NUMBER(10),
description VARCHAR2(50)
);

INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');

COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM

v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725202 2004-03-29 14:59:08

UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

COMMIT;
UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM

v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725219 2004-03-29 14:59:36
COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

SELECT versions_startscn, versions_starttime,

versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query_test
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08', 'YYYY-
MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN

VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------
------------------------ ---------------- - -----------
725212 29-MAR-04 02.59.16 PM
02001C0043030000 U THREE
725209 29-MAR-04 02.59.16 PM 725212 29-MAR-04
02.59.16 PM 0600030021000000 U TWO
725209 29-MAR-04
02.59.16 PM ONE
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query_test
VERSIONS BETWEEN SCN 725202 AND 725219
WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN

VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------
------------------------ ---------------- - -----------
725212 29-MAR-04 02.59.16 PM
02001C0043030000 U THREE
725209 29-MAR-04 02.59.16 PM 725212 29-MAR-04
02.59.16 PM 0600030021000000 U TWO
725209 29-MAR-04
02.59.16 PM ONE

c. Flashback Transaction Query:

i. Flashback Transaction Query can be used to get extra information
about the transactions listed by flashback version queries. The
VERSIONS_XID column values from a flashback version query can be
used to query the FLASHBACK_TRANSACTION_QUERY view like:
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0600030021000000');

XID OPERATION START_SCN COMMIT_SCN

---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
-----------------------------------------------------------------------------
-----------------------
0600030021000000 UPDATE 725208 725209
SCOTT
update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where
ROWID = 'AAAMP9AAEAAAA
AYAAA';

0600030021000000 BEGIN 725208 725209

SCOTT

XID OPERATION START_SCN COMMIT_SCN

---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
-----------------------------------------------------------------------------
-----------------------

2 rows selected.

d. Flashback Table:
i. The FLASHBACK TABLE command allows point in time recovery of
individual tables subject to the following requirements:
1. You must have either FLASHBACK ANY TABLE system privilege
or have FLASHBACK object privilege on the table.
2. You must have SELECT, INSERT, DELETE and ALTER privilege
on the table.
3. There must be enough information in the und tablespace to
complete the operation.
4. Row movement must be enabled on the table. (ALTER TABLE
<TABLE_NAME> ENABLE ROW MOVEMENT;)

CREATE TABLE flashback_table_test (

id NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
715315

INSERT INTO flashback_table_test (id) VALUES (1);

COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

COUNT(*)
----------
0

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

COUNT(*)
----------
1

e. Flashback Database:
i. The FLASHBACK DATABASE command is a fast alternative to
performing and incomplete recovery. In order to flashback the
database you must have SYSDBA privilege and the flash recovery area
must have been prepared advance.
ii. If the database in NOARCHIVELOG it must be switched to
ARCHIVELOG mode.

CONN sys/password AS SYSDBA

ALTER SYSTEM SET
log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ARCHIVE LOG START
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Flashback must be enabled before any flashback operations are performed:

CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
With flashback enabled the database can be switched back to a previous point in time or SCN without the
need for a manual incomplete recovery. In the following example a table is created, the database is then
flashbacked to a time before the table was created:
-- Create a dummy table.
CONN scott/tiger
CREATE TABLE flashback_database_test (
id NUMBER(10)
);

-- Flashback 5 minutes.
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.

CONN scott/tiger
DESC flashback_database_test

Some other variations of the flashback database command include:

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

iii. The window oaf time that is available for flashback is determined by
the DB_FLASHBACK_RETENTION_TARGET parameter. The maximum
flashback can be determined by querying the
V$FLASHBACK_DATABASE_LOG view.
iv. It is only possible to flashback to a point in time after flashback was
enabled.

5. Automatic Undo Retention Tuning

i. Oracle 10g automatically tunes undo retention to reduce the chances
of “snapshot too old” errors during long-running queries.
ii. The UNDO_RETENTION parameter is used to set a low retention time
threshold which the system will attempt to achieve.
iii. In the event of any undo space constraints the system will prioritize
DML operations over undo retention meaning the low threshold may
not be achieved.
iv. If the undo retention threshold must be guaranteed, even at the
expense of DML operations, the RETENTION GUARANTEE clause can be
set against the undo tablesapce during or after creation.

-- Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

-- Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 GUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY

5 rows selected.

-- Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY

5 rows selected.

v. The NOT APPLY value is assigned to non-undo tablespaces for which

this functionality does not apply.

6. Segment Management (Online Segment Shrink):

a. Based on the recommendations from the segment advisor you can recover
space from specific objects using one of the variations of the ALTER TABLE …
SHRINK SPACE command.

-- Enable row movement.

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).

ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.

ALTER TABLE scott.emp SHRINK SPACE CASCADE;

b. The shrink will accomplish by moving rows between blocks, hence the
requirement for row movement to be enabled for the shrink to take place.
This can cause problem with ROWID based triggers.
c. The shrinking process is only available for objects in tablespaces with
automatic segment space management enabled.

7. Automatic Workload Repository (AWR) Report:

a. AWR Report collects performance statistics like
i. Wait events used to identify performance problem
 ii. Time model statistics indicating the amount of DB time associated with
a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL
views.
iii. Active Session History (ASH) statistics from the
V$ACTIVE_SESSION_HISTORY view
iv. Object usage statistics
b. By default snapshots of the relevant data are taken every hour and retained
for 7 days.
c. Baseline: A baseline is a pair of snapshots that represents a specific period of
usage. Once baselines are defined they can be used to compare current
performance against similar periods in the past.

8. VLDB (Very Large Database – Bigfile Tablespace):

a. Bigfile tablespace are tablespace with a single large datafile. In contrast
normal tablespace can have several datafiles, but each is limited in size. The
benefits of bigfiles are:
i. Bigfile are supported only for locally managed tablespace with
automatic segment-space management.

9. Fine Grained Auditing Enhancement:

a. Fine-grained auditing now includes support for DML statements in addition to
queries.

10.Flash Recovery Area:

a. The flash recovery area is a location on the filesystem that holds files related
to recovery. It includes:
i. Multiplexed controlfiles
ii. Multiplexed online redo logs
iii. Archived redo logs
iv. RMAN disk backups

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST =

'/u01/app/oracle/flash_recovery_area';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;

11.Automatic Shared Memory Management:

a. The Automatic Shared Memory Management feature can decide the total size
of the SGA and then set a parameter named SGA_TARGET that decides the
total size of the SGA.
b. The individual pools within the SGA will be dynamically configured based on
the workload.
c. A non-zero value of the parameter SGA_TARGET is all that is needed to
enable the automatic memory allocation.
d. ALTER SYSTEM SET SGA_TARGET = 500M SCOPE=BOTH;
e. This approach avoids the need to set individual values for the pools; thus
you’ll need to make their values zero in the parameter file or remove them
completely.

Shared_pool_size = 0
Large_pool_size = 0
Java_pool_size = 0
Db_create_size = 0