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