Oracle 10g 11g Data Pump EXPDP Query Parameter option

Datapump query option is used to export the subset of table data according to the WHERE filter clause. Please find the examples below.

EXPDP with parameter file- parfile

Suppose if you wanted to export 2 tables using where clause. For each table you can specify the where clause.

Oracle 11g ORA-12916: cannot shrink permanent or dictionary managed tablespace; Oracle Tablespace Shrink Command Failed

Error Description:

Oracle tablespace shrink command failed with following error.

SQL> ALTER tablespace data01 shrink space keep 31g;

ALTER tablespace data01 shrink space keep 31g

*

ERROR at line 1:

ORA-12916: cannot shrink permanent or dictionary managed tablespace

Relation / Difference : O7_DICTIONARY_ACCESSIBILITY = TRUE / FALSE, SELECT ANY DICTIONARY, SELECT CATALOG ROLE and SELECT ANY TABLE

O7_DICTIONARY_ACCESSIBILITY Parameter: It is a Boolean parameter which controls the access to the objects in the sys schema. O7 indicates Oracle 7. It is a Boolean variable with values true or false. The default value for this parameter is false which will restrict the access to the sys objects.

SELECT_CATALOG_ROLE: As the name indicates it is role which will provide the SELECT access ONLY on all data dictionary views to the users.

userenv('ISDBA') : To check the Current Oracle Session has DBA Privilege

USERENV provides the current oracle session details. Userenv(‘ISDBA’) will give you true or false information whether your session has SYSDBA privilege through operating system or a password file.

Please find some example on the same.

How to Find Out Oracle Trace File Name for TKPROF or backup controlfile to trace?

Suppose if you traced your own session and you wanted to find out the trace file name from the database itself. You can use the following query. One thing you need to change the slash which is marked in red below depends on the operating system. For Windows use \ and for unix use /. The same query can be used for finding out the trace file name when you trace the control file also.          

How to Find Out Your Current / Own Oracle Session ID - SID?

For RAC Database with Instance ID

SQL> select username,inst_id, sid, serial# FROM gV$SESSION WHERE audsid = userenv('sessionid');

USERNAME                          INST_ID        SID    SERIAL#

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

APPINSTALL                              2          2        149

Oracle ASM Datafile add / resize failed with ORA-01237 ORA-01110 ORA-17505 ORA-15041: diskgroup "DATA" space exhausted

Error Description: Datafile addition or resize on ASM diskgroup is failing with following error message.

ORA-01237: cannot extend datafile 9
ORA-01110: data file 9: '+DATA/testdb/datafile/data01__2.dbf'
ORA-17505: ksfdrsz:1 Failed to resize file to size 1664000 blocks
ORA-15041: diskgroup "DATA" space exhausted

How to Refresh / Recalculate Microsoft Excel Column Formula Automatically

Normally the Excel column formulas get updated or refreshed automatically. There is one option in Excel to update the columns automatically.

In Excel 2003: Tools -> Options -> Calculation. Make it Automatic for automatic refresh functionality.

RMAN Duplicate Database Failed with RMAN-06136, ORA-15001: diskgroup "DATA_CEX" does not exist or is not mounted

Error Description: RMAN duplicate database command failed with following error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/18/2012 07:12:15
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-00349: failure obtaining block size for '+data_cex'
ORA-15001: diskgroup "DATA_CEX" does not exist or is not mounted

Prepare / Generate repetitive SQL Scripts Using Microsoft EXCEL CONCATENATE Function

Suppose if you wanted to create a series users like user1 to user100 with default tablespace is USER and temporary tablespace TEMP. I mean all the details for these users are same apart from the user name and password. In this scenario you can create the scripts using Microsoft Excel Concatenate function.

Oracle 11G SQL Command Syntax to Check Free Space, Shrink Temporary Tablespace or Tempfile

1. SQL Command to Check Free Space in Temporary table space.

SQL> select tablespace_name ,ALLOCATED_SPACE/1024/1024/1024 allocatedGB,    FREE_SPACE/1024/1024/1024 FreeGB, TABLESPACE_SIZE/1024/1024/1024 TABLESPACEGB   from dba_temp_free_space;

Oracle 10g 11g Data Pump EXPDP SAMPLE Parameter Option

SAMPLE parameter is used for exporting the sample number of rows. The parameter value mentioned will be considered as the sample percentage. Suppose if you don’t want the entire data from a table or schema and just need few numbers of records you can use this sample parameter of expdp utility. See the example below.

Oracle Data Pump EXPDP IMPDP REMAP_DATA Parameter for Data Transformation with Example

EXPDP REMAP_DATA parameter is used to transform the column data to some other value using the database packages. Using this parameter you can translate or transform the critical informations like address, credit card number to some other values taking the export itself. This parameter can be used with import (IMPDP) utility also.

Using REMAP_DATA option data can be manipulated during the export and import job.

Oracle 10g 11g RMAN Useful Commands

Command to find out backup piece for an archive log thread and sequence

RMAN>LIST BACKUP OF ARCHIVELOG ALL;

RMAN>LIST BACKUP OF ARCHIVELOG sequence 196;

RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 196;

Command to see the list/summary of backups available with RMAN catalog

RMAN>LIST BACKUP;

RMAN> LIST BACKUP SUMMARY;

RMAN> LIST BACKUP OF DATABASE;

Fix RMAN-04014 ORA-00439: feature not enabled

Error Description:

RMAN Duplicate command fails with following error message.

RMAN-00571: =======================================================

RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: =======================================================

RMAN-03002: failure of startup command at 08/23/2006 11:14:56

RMAN-04014: startup failed: ORA-00439: feature not enabled: Real Application Clusters

Fix RMAN Duplicate Database Failed with ORA-20 Connecting to ASM Instance

Error Description:

RMAN duplicate database command failed with following error.

ORA-19870: error reading backup piece /u01/app/oracle/e1mvvcmm_1_1
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA

SQLPLUS Connect, Startup Fails with ORA-09925: Unable to create audit trail file SVR4 Error: 13

Error Description:

Sqlplus / as sysdba or database startup command fails with ORA-09925: Unable to create audit trail file.

The subsequent OS errors will be different like

Linux-ia64 Error: 28: No space left on device

Linux-x86_64 Error: 27: File too large

SVR4 Error: 13: Permission denied

Fix RMAN-04014 ORA-07446: sdnfy: bad value '…/bdump' '…/udump'

Error Description:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: ===========================================================

RMAN-03002: failure of startup command at 12/25/2011 01:05:21

RMAN-04014: startup failed: ORA-07446: sdnfy: bad value

Expire Oracle User Password in Few Minutes Using Profile Resouces PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME

Values for the parameters PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME can be in number of days or can be in hours or minutes. You can specify the hours or minuts in decimal values.

In below mentioned example I am going to expire the login in 4 minutes (2 mins PASSWORD_LIFE_TIME and 2 mins PASSWORD_GRACE_TIME). In this example I mentioned a number 1440 which is equivalent for 24hr*60Min*1day.

ORA-28011: the account will expire soon; change your password now

If you are getting a error/warning message (ORA-28011) while connecting to oracle user which means that the particular user password life time is over and it is running on the password grace period.

After the grace period time the password will expire and it will ask for new password.

How to Disconnect or Expire the Idle Oracle 10g 11g Sesssions Automatically Using Profile IDLE_TIME Resouce

In many times we wanted to implement the automatic session expiry for the idle session in the databases which are having huge number of user session. Suppose if you wanted to expire the Oracle sessions which are idle more than 3 minutes.

Step 1. Create new profile with IDLE_TIME parameter as 3.

DBMS_SCHEDULER. Create_job Fails with ORA-06512 ORA-27486: insufficient privileges

Error Description:

ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 376
ORA-06512: at line 2
27486. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a scheduler operation without the

How to Add / Install Syntax Highlighter for Scripts in Blogger Post – SQL, Java, Python, HTML etc

Step 1: Changes to the blogger script.

Open Blogger-> Design -> Edit HTML

Search for </head> tag

Add the following code Jut above the </head> tag

a.   Green Color Codes are mandatory