Oracle Data Pump 11g

Data Pump utility enhanced in Oracle 11g.

Oracle 11g introduce some new features for Data Pump utility, I tested some and want to share with you all.

For EXPDP utility

1. compression parameter

If we have issue with export dumpfile size then in 11g it can be gone through compression parameter.

so we don't need to any other third party tool to compress exported dumpfile because compression parameter dumpfile automatically compress during export.

Syntax:

COMPRESSION=[all , metadata_only, data_only, NONE]

Default: metadata_only

I tested compression option in same schema, first time i export schema without compress and second time with compress parameter see the difference. both dumpfile exported with COMPRESSION=ALL parameter

29-07-2008 07:10 PM 1,169,989,632 A1withoutCMPR.DMP

29-07-2008 07:15 PM 317,968,384 A2withCMPR.DMP

2. encryption & encryption_password parameter

This is most useful parameter becuase through this we can provide better security to exported dumpfile from unauthorized access.

Syntax:

ENCRYPTION=[all, data_only, encrypted_columns_only, metadata_only, NONE]

Default: NONE

ENCRYPTION_PASSWORD=there is no default; Value must be user provided

3. remap_data parameter

This is another useful options for export utility.

remap_data parameter allow us to specify a remap function that takes as a source the original value of the designated column and returns a remapped value that will replace the original value in the dump file.

For example: Data move from production server to test server, in data there is one table named employee which contents all employee bank account number. for security we don't disclose this value to test server team, so through remap_function we can prevent this.

For this we need to create package which content one function we replace or change bankac value for the column.

Syntax:

REMAP_DATA=schema.table_name.column_name:schema.pkg.function

For example:

REMAP_DATA=hr.employee.bankac:hr.package.function

4. reuse_dumpfiles parameter

This is another useful parameter, suppose we daily export dumpfile with somename but suppose is samename file already exist then export terminated with the following message

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "c:\test.dmp"

ORA-27038: created file already exists

OSD-04010: option specified, file already exists

To prevent this error we can use reuse_dumpfiles=y which overwrite any existing file with samename.

Syntax:

REUSE_DUMPFILES=y

Default: N

For IMPDP utility

1. encryption_password parameter

If dumpfile exported with encryption_password parameter then for import we must use password for import otherwise import terminated with the following message.

ORA-39002: invalid operation

ORA-39174: Encryption password must be supplied.

NOTE: Password is case-sensitive.

2. partition_options parameter

This is another cool feature which i come across.

Syntax:

PARTITION_OPTIONS=[none, departition,merge]

Default: none / departition (as per condition)

If NONE is specified means no change during import, table created as they exist in source database.

If DEPARTITION specified means each partitions created as a separate table in source database.

If MERGE specified means all partitions created as a one simple table.

For example: when partition_options=DEPARTITION

1. Create One table with 4 partition

CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL )
PARTITION BY RANGE (sale_year)
( PARTITION sales_q1 VALUES LESS THAN (1999),
PARTITION sales_q2 VALUES LESS THAN (2000),
PARTITION sales_q3 VALUES LESS THAN (2001),
PARTITION sales_q4 VALUES LESS THAN (2002));

2. check the partitions

SQL> select partition_name from user_tab_partitions where table_name='SALES';
PARTITION_NAME

------------------------------

SALES_Q1

SALES_Q2

SALES_Q3

SALES_Q4

3. expdp the table

host expdp scott/tiger dumpfile=sales.dmp directory=data tables=SALES

4. drop the table

SQL> drop table sales purge;
Table dropped.

5. Import the table with PARTITION_OPTIONS=DEPARTITION

SQL> host impdp scott/tiger dumpfile=p.dmp directory=data partition_options=DEPARTITION

This time 4 table created for each partition.

SQL> select * from tab where tname like 'S%';
TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------SALES_SALES_Q1 TABLE

SALES_SALES_Q2 TABLE

SALES_SALES_Q3 TABLE

SALES_SALES_Q4 TABLE

check the partitions view

SQL> select partition_name from user_tab_partitions;

no rows selected

Again we import same exported with with PARTITION_OPTIONS=MERGE

SQL> host impdp scott/tiger dumpfile=sales.dmp directory=data partition_options=MERGE

This time only one table created for all partitions.

SQL> select * from tab where tname like 'S%';
TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------SALES TABLE

Check the partitions view

SQL> select partition_name from user_tab_partitions where table_name='SALES';

no rows selected

Oracle Data Recovery Advisor

Oracle 11g come up with one new cool feature for Database Backup & Recovery. This is DRA (Data Recovery Advisor).

Which help us to recover the database without any trouble with few RMAN commands.

What is Data Recovery Advisor?

DRA is an oracle database tool that automatically diagnoses data failures, determines and presents appropriate repair options and executes repaires at user requrests.

The following RMAN commands are use to perform Data Recovery Advisor.

1. List Failure

2. Advise Failure

4. Repair Failure

5. Change Failure

1. List Failure

List failure command give us information regarding failures and the effect of these on database operations. Each failures uniquely identified by failure number.

2. Advise Failure

Advise failure give us advise for how to deal with failure against database means advise failure give us solution for particular failure.

3. Repair failure

Repair failure command give us "rman generated scrits" which restore and recover database from backup.

4. Change failure

Change failure is RMAN command which change the failure status or priority.

like there is two status : OPEN or CLOSED and Priority is HIGH or LOW.

If some failure is rman showing HIGH and we want to change it to LOW then using change failure command we can change it.

Let see some pratical

1. Suppose I lost my system datafile.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF'

2. Now I am not worry becuase I have database backup with 11g database.

3. I need to just connect with RMAN, after connect with RMAN I do the following task.

1. Using list failure command checking the failure description

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

After got the failure description we can get "advise" from oracle about failure through advise failure command.

RMAN> advise failure;
List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=152 device type=DISK

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

1. If file C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Restore and recover datafile 1

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

Above is rman advise regarding particular failure if above suggested repair option is helpful and fix the current problem then ok otherwise need to call oracle support services.

now check oracle suggested repair options or scripts.

RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data lossRepair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Above is suggested script from RMAN to restore and recover database for particular failure, if suppose we want to use above script then again run "repair failure" command without 'preview' keyword.

RMAN> repair failure ;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
Starting restore at 26-JUL-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF

channel ORA_DISK_1: reading from backup piece C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1

channel ORA_DISK_1: piece handle=C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1 tag=TAG20080726T124808

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:05:25

Finished restore at 26-JUL-08

Starting recover at 26-JUL-08

using channel ORA_DISK_1
starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 26-JUL-08

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

Database is now recovered and open for normal operation. So it is very easy to restore and recover database using Data Recovery Advisor feature.

same like above procedure for the system datafile we can restore or recover "undo file" & other "application datafiles".

If we lost one of controlfile or all controlfiles then use the following procedure.

ORA-00205: error in identifying control file, check alert log for more info

I am getting above error during startup database then I connect to rman and check "list failure".

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

732 CRITICAL OPEN 26-JUL-08 Control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL is missing

Then I check "advise" from rman regarding above error

RMAN> advise failure;
List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

732 CRITICAL OPEN 26-JUL-08 Control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=151 device type=DISK

RMAN-06495: must explicitly specify DBID with SET DBID command

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

no manual actions available
Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Use a multiplexed copy to restore control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2234784495.hm

Then I check repair preview for the above failure.

RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2234784495.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from 'C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL';

sql 'alter database mount';

And finally I apply the above suggested script by RMAN.

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2234784495.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from 'C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL';

sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
Starting restore at 26-JUL-08

using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL03.CTL

Finished restore at 26-JUL-08
sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

Do you want to open the database (enter YES or NO)? YES

database opened

Now if we lost all or one of redolog file then use the following procedure

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

During startup I am getting above error then after I connect to RMAN and perform the following task

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

781 CRITICAL OPEN 26-JUL-08 Redo log group 2 is unavailable

775 CRITICAL OPEN 26-JUL-08 Redo log group 1 is unavailable

784 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG is missing

778 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG is missing

After that I check advise from RMAN regarding above failure list.

RMAN> advise failure;
List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

781 CRITICAL OPEN 26-JUL-08 Redo log group 2 is unavailable

775 CRITICAL OPEN 26-JUL-08 Redo log group 1 is unavailable

784 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG is missing

778 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

1. If file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG was unintentionally renamed or moved, restore it

2. If file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG was unintentionally renamed or moved, restore it
Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Recover database

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_4228591735.hm

Then finally I apply the suggested RMAN advise means "repair failure".

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_4228591735.hm
contents of repair script:

# recover database until cancel and open resetlogs

sql 'alter database recover database until cancel';

alter database open resetlogs;
Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
sql statement: alter database recover database until cancel
database opened

repair failure complete

So what you say, it is one of cool feature with 11g :)

now up to we see how to perform recovery against "controlfile" & "datafile" & "redolog" file suppose if we lost "tempfiles" then what Oracle suggest. let see

Oracle 11g

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host del C:\app\m.taj\oradata\test\TEMP*.dbf
SQL> --In above statement I deleted the TEMPORARY files from filesystem.

SQL> startup

ORACLE instance started.
Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 201327508 bytes

Database Buffers 327155712 bytes

Redo Buffers 5844992 bytes

Database mounted.

Database opened.

Database is open without any error but when i checked alertlog file then i found following entries.

Re-creating tempfile C:\APP\M.TAJ\ORADATA\TEST\TEMP01.DBF

Above line meaning is Oracle Automatically Created Missing Tempfile during startup but this is not happen with Oracle 10gr1 (tested), Don't know about 10gr2 (Didn't test)

When I deleted tempfile manually in 10gr1 then after database open without error during startup I found following entires in alertlog file

Oracle10gr1

Sat Jul 26 19:27:04 2008

Errors in file f:\oracle\product\10.1.0\admin\test\bdump\test_dbw0_3292.trc:

ORA-01186: file 202 failed verification tests

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\TEMPFILE01.DBF'

File 202 not verified due to error ORA-01157

Means in 10gr1 we need to manually RE-CREATE temporary tablespace if we lost tempfiles. but in 11g it is automatically done by ORACLE :)

Oracle Partitioning

Oracle Partitioning

Oracle partitioning an option of oracle database can enhance the managebility, performance and availability.

Partitioning allow a table index & index organized table to be subdivided into smaller pieces. Each piece of database object is called a partition. Each partition has its own name and may optionally have its own storage.

Partitioning for the Managebility

We can divide Large TABLE or INDEX in smaller piece through partition option, we can take backup partition wise so it is reduce the maintanace, we can delete one partition from the table instead delete full table data.

Partitioning for the Performance

When large table is partitioned then we get performance benefits, suppose we have table with partitioned with range as per monthly data, so when we query particular month data then query search partition partition for the result instead of full table.

Partitioning for the Availability

When large table is partitioned and suppose of the partition is unavailable due to same reason then still we can query and all other partition is available for database.

Oracle 8i

Oracle partition introduce with Oracle 8 version and Oracle 8i provide three partition method.

1. Range partitioning

2. Hash partitoning

3. Composite partitioning

1. Range partitioning

Use range partitioning to map rows to partitions based on ranges of columns values. means we create partition based on range like month wise, year wise, day wise etc.

CREATE TABLE sales

( invoice_no NUMBER,

sale_year INT NOT NULL )

PARTITION BY RANGE (sale_year)

( PARTITION sales_q1 VALUES LESS THAN (1999),

PARTITION sales_q2 VALUES LESS THAN (2000),

PARTITION sales_q3 VALUES LESS THAN (2001),

PARTITION sales_q4 VALUES LESS THAN (2002));

We can use any column for "partitioning key" when partition on based. in above example is use sale_year based, all values under 1999 year will insert in partition "sales_q1".

2. Hash partitioning

When large table is not fit for range partition and we want better performance then we can use HASH partition.

In hash partition we can define no. of partition which oracle will create based on their algorithm. or when can store each partition on separate tablespace.

SQL> create table sales_test1

( sales_id number, sales number)

PARTITION BY HASH (sales_id) PARTITIONS 4;

Table created.

Above table create 4 partition for the SALES_ID column.

create table sales_test2

( sales_id number, sales number)

PARTITION BY HASH (sales_id)

( PARTITION p1 tablespace tbs1,

PARTITION p2 tablespace tbs2);

Above table created with two partition and each partition will store on separate tablespace.

3. Composite Range-Hash partitioning

Basically this is combination of RANGE and HASH partitioning option. In this first we partition table through RANGE and subdivide with HASH partitioning.

create table sales_test3

(sales_id number, sales number,sales_year number)

PARTITION BY RANGE (sales_year)

SUBPARTITION BY HASH (sales)

SUBPARTITIONS 2 STORE in ( tbs1, tbs2)

(PARTITION p1 values less than (1999),

PARTITION p2 values less than (2000))

Above table is first partition by RANGE on sales_year column then SUBPARTITION by HASH on SALES column with 2 partition and each on store in separate tablespace.

Oracle 9i (9.0.1)

Oracle Introduce one more feature in series of partitioning.

4. LIST partitioning

Up to now we see range, hash & composite partitioning option.

Through list partitiong we can explicit control on rows to map with partitions.

Suppose we have large table their one column is for color status and rows inserted based on that column, color column have three distinct values that is "blue,red,black". now we want to create partition on this distinct values, In this condition RANGE or HASH partition option will not work. so we use LIST partition

create table color_test

(colorid number,color char(10))

PARTITION BY LIST (color)

(PARTITION c1 values ('BLUE'),

PARTITION c2 values ('RED'),

PARTITION c3 values ('BLACK'));

Above table created with LIST partition based on COLOR column which explicity map with partition.

Oracle9i (9.2.0)

Oracle introduce one more feature in series of partitioning

5. Composite RANGE-LIST partitioning

It is same like composite RANGE-HASH partitioning method.

First table partition by RANGE then subpartition by LIST method.

create table sales_test4

( salesid number, sales number, status varchar2(30), salesdate date)

PARTITION BY RANGE (salesdate)SUBPARTITION BY LIST (status)(PARTITION q1_0001 VALUES LESS THAN (to_date('01-APR-2008','DD-MON-YYYY'))

(SUBPARTITION q1_0001_a VALUES ('A'),

SUBPARTITION q1_0001_b VALUES ('B'),

SUBPARTITION q1_0001_c VALUES ('C')))

Above table first partition by RANGE Method on salesdate column then subpartition by LIST on status column.

Oracle10g (10.1.0)

In 10g release 1 no new method is introduce but oracle enhancement existing method as follows

1. Partition management enhanced with OEM.

2. We can use LIST/HASH/RANGE partition method for Index Organized table.

3. LOB columns are supported all types of partition method

4. Oracle now automatically maintains global indexes when DDL operation are executed against IOT's tables

Oracle10g (10.2.0)

10gr2 again no new feature introdure but oracle enhanced all existing features for better performance, managebility.

1. Online Redefinition of a single partition is now possible

Oracle provides a mechanism to move a partition or to take other changes to the partition's physical structure without significantly affecting the availibility of the partition of DML. The mechanism is called Online table redefinition.

2. Increased maximum number of partitions per object, before it was 64k-1 now it is 1024k-1.

Oracle11g (11.1.0)

Oracle 11g comes with some more new feature for partitioning

6. Interval partitioning

7. Reference partitioning

8. Composite Range-Range partitioning

9. Composite List-Range partitioning

10. Composite List-Hash partitioning

11. Composite List-List partitioning

12. System partitioning

13. Virtual column partitioning

1. Interval partitioning

In range partition we have to explicity define range for rows to map with partition. And if any new inserted row is not mapped with existing partition range then we getting ORA-14400 inserted partition key does not map to any partition to prevent this error we can use INTERVAL partition. which create SYSTEM generated names partition when new rows not mapped with any existing partition.

SQL> create table TEST

2 ( no number ,

3 name varchar2(20))

4 PARTITION BY RANGE (no)

5 (partition p1 values less than (100),

6 partition p2 values less than (200));
Table created.

SQL> insert into test values (100,'a');
1 row created.

SQL> insert into test values (200,'b');

insert into test values (200,'b')

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

To prevent above error we can use INTERVAL partition option like below

SQL> create table TEST1

2 ( no number,

3 name varchar2(20))

4 PARTITION BY RANGE(no) INTERVAL (100)

5 (partition p1 values less than (100),

6 partition p2 values less than (200));
Table created.

SQL> insert into test1 values (100,'a');
1 row created.

SQL> insert into test1 values (200,'b');
1 row created.

SQL> select table_name,partition_name

2 from user_tab_partitions

3 where table_name='TEST1';
TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

TEST1 P1

TEST1 P2

TEST1 SYS_P41

Now this time no error come and one new partition automatically created with SYSTEM generated name.

7. Reference partitioning

This is new feature and very good benefits from this.

Suppose we have two tables 1. parent and 2. child

Parent table

create table parent

( no number primary key, name varchar2(20))

PARTITION BY RANGE (no)

(partition p1 values less than (100));

Child table

create table child

(childno number NOT NULL,

cname varchar2(20),

constraint fkno foreign key (childno) references parent(no) )

PARTITION BY REFERENCE (fkno);

Parent table is partition by RANGE method on NO Column and if we want same manner partition on child table but it is not possible becuase child table DON'T have NO column so prevent this problem we can use REFERENCE by partition option like above.

NOTE: foreign key column in child table must be NOT NULL otherwise we are getting this error: ORA-14652: reference partitioning foreign key is not supported

8. System partitioning

One more cool feature is system partitioning, when table is not fit for any partition method then we can use system partitioning becuase system partition is not use any PARTITION KEY (range,hash,list). it is just divide table in physical way.

create table test

( no number,name varchar2(20))

PARTITION BY SYSTEM

( partition p1 tablespace tbs1,

partition p2 tablespace tbs2);

Note that there is no partition key or boundaries, table physically divide in two way.

SQL> insert into test values (1,'T');

insert into test values (1,'T')

*

ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs ontables partitioned by the System method

Why we are getting above error, becuase there is no partition key so we need to always mention partition name where rows insert.

SQL> insert into test partition (p1) values (1,'T');
1 row created.

NOTE: for delete or update we don't have to provide partition but when statement like below we need to specify partition name otherwise statement search full table for specific row.

SQL> delete test partition (p1) where no=1

9. Virtual column partitioning

It is very good feature becuase we can create partition on column which column doesn't exist in table.

create table result

( id number,

subject varchar2(20),

totalmarks number,

obtainmarks number);

In above table there is four column 1. id ,2. subject 3. totalmarks 4. obtainmarks and 5. is percentage (which doesn't exists) but it is calculated based on totalmarks and obtainmarks columns and we want partition based on parcentage column, In this case we can use VIRTUAL partition option.

create table result

( id number,

subject varchar2(20),

totalmarks number,

obtainmarks number,

percentage number

generated always as

( obtainmarks/totalmarks*100

) virtual )

partition by RANGE(percentage)

(partition plow values less than ('50'),

partition pmedium values less than ('70'),

partition phigh values less than ('100'))

Now inserting some rows and check

SQL> begin

2 insert into result (id,subject,totalmarks,obtainmarks) values (1001,'a',100,90);

3 insert into result (id,subject,totalmarks,obtainmarks) values (1001,'b',100,70);

4 insert into result (id,subject,totalmarks,obtainmarks) values (1001,'c',100,50);

5 insert into result (id,subject,totalmarks,obtainmarks) values (1002,'a',100,90);

6 insert into result (id,subject,totalmarks,obtainmarks) values (1002,'b',100,70);

7 insert into result (id,subject,totalmarks,obtainmarks) values (1002,'c',100,50);

8 end;

PL/SQL procedure successfully completed.

SQL> select * from result;
ID SUBJECT TOTALMARKS OBTAINMARKS PERCENTAGE

---------- -------------------- ---------- ----------- ----------

1001 c 100 50 50

1002 c 100 50 50

1001 a 100 90 90

1001 b 100 70 70

1002 a 100 90

90

1002 b 100 70 70
6 rows selected.

NOTE: Above logic for percentage column is only for demo.

Oracle Flashback Data Archive (Oracle Total Recall)

It is new feature with 11g.

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

By default, flashback archiving is off for any table. You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table. After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.

While flashback archiving is enabled for a table, following DDL statements are not allowed on that table.
· ALTER TABLE statement that does any of the following:
o Drops, renames, or modifies a column
o Performs partition or subpartition operations
o Converts a LONG column to a LOB column
o Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
· DROP TABLE statement
· RENAME TABLE statement
· TRUNCATE TABLE statement

Creating / Alerting / Dropping Flashback Data Archive

We must have SYSDBA or Flashback archive administrator privilege.
Creating a flashback data archive with the CREATE FLASHBACK ARCHIVE statement

1. Name of the flashback data archive
2. Name of the first tablespace of flashback data archive
3. Quota on tablespace default is UNLIMITED
4. Retention time (In number of days that flashback data archive data for the table is guaranteed to be stored)

For example:
--Create separate tablespace for FLASHBACK option
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create tablespace ORCLFLASHDB
2 datafile '+DGRP1' size 50m;
Tablespace created.
SQL> --Create flashback data archive
SQL> create flashback archive DEFAULT fla1 tablespace ORCLFLASHDB
2 RETENTION 1 YEAR;
Flashback archive created.

Note: DEFAULT keyword is fla1 is default database flashback data archive.

Altering a flashback data archive
We can do following changes with altering flashback data archive

1. change the retention time of flashback data archive

SQL> alter flashback archive FLA1 modify retention 2 year;
Flashback archive altered.

2. Purge some or all its data

SQL> alter flashback archive FLA1 purge all;
Flashback archive altered.

3. add / modify / remove tablespace

SQL> alter flashback archive FLA1 add tablespace tbsname;

Dropping a Flashback Data Archive

SQL> drop flashback archive FLA1;
Flashback archive dropped.

Specifying the default flashback data archive

SQL> alter flashback archive FLA1 set default;
Flashback archive altered.

Enabling and Disabling flashback data archive

By default flashback data archive is disabled for any database table.
We can enable flashback data archive for any table if we have FLASHBACK ARCHIVE object privilege

After enable flashback data archive on table if we want to disable then we must have FLASHBACK ARCHIVE ADMINISTRATOR or SYSDBA privileges
We can enable flashback data archive on table through adding FLASHBACK ARHICIVE keyword with ALTER or CREATE TABLE statement.

For example:
SQL> grant FLASHBACK ARCHIVE on FLA1 to scott;
Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> create table flasheg ( no number) FLASHBACK ARCHIVE;
Table created.

SQL> alter table EMP flashback archive;
Table altered.

SQL> alter table flasheg NO FLASHBACK ARCHIVE;
alter table flasheg NO FLASHBACK ARCHIVE
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

NOTE: Because disable flashback archive required FLASHBACK ARCHIVE ADMINISTRATOR or SYSDBA privileges.

SQL> conn system/oracle
Connected.

SQL> alter table scott.flasheg NO FLASHBACK ARCHIVE;
Table altered.

NOTE: NO FLASHBACK ARCHIVE keyword requried to disable flashback archive data for the table.

Viewing information about FLASHBACK ARCHIVE DATA

*_FLASHBACK_ARCHIVE display information about flashback data archive
*_FLASHBACK_ARCHIVE_TS display tablespaces of flashback data archive
*_FLASHBACK_ARCHIVE_TABLES display information about tables that are enabled for flashback archiving.
NOTE: * means DBA or USER or ALL

Example:

User Scott Create following two tables

13:07:18 SQL> create table empcopy1 as select * from empcopy;
Table created.
13:07:55 SQL> create table empcopy2 as select * from empcopy;
Table created.

Enable Flashback archive on only one tables

13:07:59 SQL> alter table empcopy1 flashback archive;
Table altered.

After that delete both table empcopy1,empcopy2 respectively and commit.

13:08:06 SQL> delete empcopy1;
13 rows deleted.
13:08:13 SQL> delete empcopy2;
13 rows deleted.
13:08:16 SQL> commit;
Commit complete.

13:08:18 SQL> select * from empcopy1;
no rows selected
13:08:26 SQL> select * from empcopy2;

Timing we perform above operation is "17 may 08 01:08 pm"

After Three and half hours SCOTT found his made incorrect mistake and delete incorrect tables data now he want to recover both tables.

As you know on one table (empcopy1) flashback archive is enable for 1 year retention period so scott can able to recover table empcopy1 without any problem
but about empcopy2 there flashback archive is disable so this data depands on undo_retention period which by deafult set is 900 seconds (15 minutes) only.
Means scott can able to recover data only for TABLE EMPCOYP1 and he have to perform incomplete recovery for TABLE EMPCOPY2.

Current time: 04:43:00 pm 17may06

SQL> select count(*) from empcopy1
2 as of timestamp
3 to_timestamp ('2008-05-17 13:08:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)
----------
13

SQL> select count(*) from empcopy2
2 as of timestamp
3 to_timestamp ('2008-05-17 13:09:00','YYYY-MM-DD HH24:MI:SS');

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

Read Only Table with 11G

Read Only Table with 11g ...

Sometime we need to keep table in READ ONLY mode but it is not possible before 11g but now in 11g it is possible to do just one command.

Before 11G
If we want to keep TABLE in read only mode in table

then we have to make TABLESPACE READ ONLY where table is exists or CREATE TRIGGER for this becuase there is no single command exists like below to make table READ ONLY.

SQL> alter table emp READ ONLY;

alter table emp READ ONLY

*

ERROR at line 1:

ORA-01735: invalid ALTER TABLE option

But with 11G or later

We can use below single command to make TABLE read only mode.

SQL> alter table emp READ ONLY;
Table altered.
SQL> delete emp;

delete emp

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP"

When a table is in read-only mode, operations that attempt to modify table data are disallowed. The following operations are not permitted on a read-only table:
All DML operations on the table or any of its partitions
TRUNCATE TABLE
SELECT FOR UPDATE
ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
ALTER TABLE SET COLUMN UNUSED
ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents
Online redefinition
FLASHBACK TABLE

The following operations are permitted on a read-only table:
SELECT
CREATE/ALTER/DROP INDEX
ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
ALTER TABLE for physical property changes
ALTER TABLE DROP UNUSED COLUMNS
ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITION
ALTER TABLE MOVE
ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK
RENAME TABLE and ALTER TABLE RENAME TO
DROP TABLE
ALTER TABLE DEALLOCATE UNUSED
ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

Invisible Index with 11G

Index with Performance it is always big issues with performance experts.

FULL TABLE SCAN is good

or

INDEX SCAN is good

Mr. Tom kyte Oracle Expert said "FULL TABLE SCAN is not bad" always and "INDEX SCAN is not good" always.

Now with 11g we can test both performance means with or without index WITHOUT DROPPING exisiting index on table.

Before 11g if we don't want to use INDEX then we need to drop it.

but now in 11G we no need to drop it we just set INVISIBLE so optimizer will ignore index during query execution.

SQL> create index emp_ename ON emp(ename) visible;
Index created.
SQL> set autotrace traceonly

SQL> select ename from emp where ename = 'SMITH';
Execution Plan

----------------------------------------------------------Plan hash value: 1221021741
------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

------------------------------------------------------------------------------

0 SELECT STATEMENT 1 6 1 (0) 00:00:01

* 1 INDEX RANGE SCAN EMP_ENAME 1 6 1 (0) 00:00:01

------------------------------------------------------------------------------
Predicate Information (identified by operation id):

---------------------------------------------------
1 - access("ENAME"='SMITH')
Statistics

---------------------------------------------------------- 1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> --Now check performance without INDEX means make index INVISIBLE for optimizer

SQL> --for that we need to set INDEX "INVISIBLE" through below command

SQL> alter index emp_ename INVISIBLE;
Index altered.

NOTE: You can use above statement to make it VISIABLE again just replace INVISIBLE to VISIBLE.

SQL> --now check same query WITHOUT index.

SQL> select ename from emp where ename = 'SMITH';
Execution Plan

----------------------------------------------------------Plan hash value: 3956160932
--------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

--------------------------------------------------------------------------

0 SELECT STATEMENT 1 6 3 (0) 00:00:01

* 1 TABLE ACCESS FULL EMP 1 6 3 (0) 00:00:01

--------------------------------------------------------------------------
Predicate Information (identified by operation id)

:---------------------------------------------------
1 - filter("ENAME"='SMITH')
Statistics

---------------------------------------------------------- 218 recursive calls

0 db block gets

49 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> --This time optimizer will not use INDEX in query execution.

There is one new parameter "OPTIMIZER_USE_INVISIBLE_INDEXES" which we can use for INVISIBLE index. Just take above example here I set index status INVISIBLE to optimizer will not use index.

Now if we set OPTIMIZER_USE_INVISIBLE_INDEXES=true then optimizer will use INVISIBLE indexes.

When can set this parameter at SESSION or SYSTEM level.

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;
Session altered.
SQL> select ename from emp where ename = 'SMITH';
Execution Plan

----------------------------------------------------------Plan hash value: 1221021741
------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

------------------------------------------------------------------------------

0 SELECT STATEMENT 1 6 1 (0) 00:00:01

* 1 INDEX RANGE SCAN EMP_ENAME 1 6 1 (0) 00:00:01

------------------------------------------------------------------------------
Predicate Information (identified by operation id)

:---------------------------------------------------
1 - access("ENAME"='SMITH')

Query about INVISIBLE indexes

USER_INDEXES, ALL_INDEXES, DBA_INDEXES

SQL> select index_name,visibility from user_indexes 2 where index_name='EMP_ENAME';
INDEX_NAME VISIBILIT

------------------------------ ---------EMP_ENAME INVISIBLE

Memory Management with 11G

Oracle Memory Managment now make easier with Oracle 11g.

In the previous release (Oracle10g) we need to set TWO parameter for automatic memory management.

1. SGA_TARGET = xxx G

2. PGA_AGGREGATE_TARGET = xxx G

then oracle will automatically allocated size for Buffer cache,Shared pool, large pool etc.

Now In Oracle 11g will reduce two parameter (SGA_TARGET, PGA_AGGREGATE_TARGET) and introduce one new parameter called "MEMORY_TARGET". Which use for SGA or PGA size configuration.

When we specify MEMORY_TARGET parameter for instance then we no need to specify SGA_TARGET or PGA_AGGREGATE_TARGET parameter for memory configuration. Oracle will automatically allocate memory for memory parameter.

How to enable automatic memory management?

As previous parameter SGA_MAX_SIZE in 11g also one new parameter MEMORY_MAX_TARGET which is maximum limit for instance memory for oracle database.

Current database is not configured for MEMORY_TARGET parameter

SQL> show parameter target
NAME TYPE VALUE

------------------------------------ ----------- ------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

pga_aggregate_target big integer 25M

sga_target big integer 172M

1. First set MEMORY_MAX_TARGET parameter for maximum instance memory limit.

SQL> alter system set memory_max_target=200M scope=SPFILE;
System altered.

NOTE: memory_target=(sga_target+pga_aggregate_target)

2. Shutdown & startup database

SQL> shutdown immediate

SQL> startup

NOTE: ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account, see alert log for more information

SQL> --Above error occur when MEMORY_MAX_TARGET parameter value is lower then SGA_MAX_SIZE parameter value. so either remove SGA_MAX_SIZE parameter or Increase MEMORY_MAX_TARGET parameter value

SQL> create pfile='c:\temp\init.ora' from spfile;
File created.
SQL> --after editing recreate new spfile.SQL> create spfile from pfile='c:\temp\init.ora';
File created.

SQL> startup

3. SET MEMORY_TARGET parameter to NON-ZERO & set "0" to SGA or PGA_AGGREGATE_TARGET parameter.

SQL> alter system set memory_target=200m;
System altered.
SQL> alter system set sga_target=0;
System altered.
SQL> alter system set pga_aggregate_target=0;
System altered.
SQL> show parameter target
NAME TYPE VALUE

------------------------------------ ----------- -----------archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 200M

memory_target big integer 200M

pga_aggregate_target big integer 0

sga_target big integer 0

Monitoring and Tuning automatic memory management

New V$ view : V$MEMORY_DYNAMIC_COMPONENTS show the current size of SGA and PGA.

and New V$ view: v$MEMORY_TARGET_ADVICE provides advice for the memory_target init parameter

read more http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#BGBJAHEJ