Tips & Tricks 5 : Insert data over dblink from partition

Inserting data over a dblink from specified partition gave following error,

insert into testtbl select * from testtbl@TESTLINK partition (p1);

ORA-02070: database TESTLINK does not support extended partition name in this context

Briefly , partition clause is not supported over the dblink.In order to solve this issues , view is created at remote site that uses partition clause

Remote site : create view testtblp1_v as select * from testtbl partition (p1);

Local site    :  insert into testtbl select * from testtblp1_v@TESTLINK;

Tips & Tricks 4 : Conditional index

Client asked me if it is possible to create unique index on any table on condition. For example only 1 record is acceptable for any document which has active_flag as '1'. For active_flag '0' there can be more than 1 record.

In order to do this  we will create unique index with condition

SQL> create table t1 (document_id number,active_flag char(1));

Table created.

SQL> create unique index t1_idx on t1(case when active_flag='1' then document_id else null end);



For active_flag as '1' index will be populated with document_id , and for '0' not.
 
SQL> insert into t1 values (1,'1');

1 row created.

SQL> insert into t1 values (2,'1');

1 row created.

SQL> insert into t1 values (1,'0');

1 row created.

SQL> insert into t1 values (1,'0');

1 row created. -- able to insert same record

SQL> insert into t1 values (1,'1');
insert into t1 values (1,'1')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_IDX) violated

-- Not able to insert same record for active_flag '1' value .

Tips & Tricks 3 : Exclude table ,schema from Expdp

From command line if you would like to exclude table , tables or schema following command can be used .

For specific table(s)

expdp system/pass directory=dp_dir dumpfile=sample.dmp exclude=TABLE:\ "IN \(\'TAB1\',\'TAB2\',\'TAB3\'\)\"

For schema(s)

expdp system/pass directory=dp_dir dumpfile=sample.dmp exclude=SCHEMA:\ "IN \(\'SCHEMA_NAME1\',\'SCHEMA_NAME2\'\)\"

Tips & Tricks 2 : Disable autostart of High Availability Service

Whenever  any components like ASM,listener,database is down, or when server restarted, component called HAS (High Availability Service) restarts the failed components and bring them up. This is done automatically. Sometimes it is not wanted to start itself automatically. In order to disable these property below command is executed ,

[root@test01 bin]# ./crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@test01 bin]#

http://docs.oracle.com/cd/E18283_01/server.112/e17120/restart004.htm#CIHGFJEB

Tips & Tricks 1 : Give debug privilege to user

Sometimes small tips and tricks may be useful for administrating db, apps etc... I would like to add related posts into my blog contains tips and tricks.

Tips & Tricks 1 : Give debug privilege to user.

Any user with whom you need debug procedure,function or package , related user must have

DEBUG CONNECT SESSION

DEBUG ANY PROCEDURE

privileges.

In order to give these ,

grant DEBUG CONNECT SESSION userx;

grant DEBUG ANY PROCEDURE userx