Expdp Export Failed: ORA-00439: feature not enabled: Dump File Data Compression

Error Description:

Data pump expdp export failed with following error

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

ORA-39002: invalid operation

ORA-00439: feature not enabled: Dump File Data Compression

Solution Description:

The advanced compression options of oracle works only with Enterprise Edition. I have used compression=all option and the Oracle version which I am using is standard edition. That is the reason for the error.

Expdp / Impdp Tablespace Parameter Benefits and Examples

Oracle data pump provides tablespace parameter to export and import the data in tablespace level. You can club one or more tablespaces in single dump while export and you can import one or more tablespace from the full database dump during the import. The benefits of this option are as below.

a.   It would be very helpful during the tablespace level database reorganizations. The backup can be performed only on the targeted tablespace.

How to Perform a Date Test Case with Old or Upcoming Sysdate in Oracle? - FIXED_DATE Parameter

Oracle provides a parameter called FIXED_DATE to set the oracle sysdate to a fixed date which can be a prior or upcoming date. By default the fixed_date parameter value would be null or NONE. You can set the fixed_date parameter value using alter system command. This parameter mainly used for testing purpose by setting the sysdate to an old or upcoming date.

Initially the parameter value would be null and it will show the current system date.

Oracle Delete and Update Commands Executes With Set Autotrace Traceonly Explain

When you set autotrace traceonly explain in SQLPlus prompt, generally the select command will not execute but it will show the execution plan. However, it will differ in case of DMLs like delete and update. It executes the query first and will provide the execution plan.

SQL >select * from tab;

SQL Developer, Data Modeler Failed: Unable to Launch the Java Virtual Machine Located at Path:

Error Description:

SQL Developer Data Modeler launch fails with following error in windows 7 64bit machine.

Unable to Launch the Java Virtual Machine Located at Path:

Solution Description:

The issue is because the windows x64 Java JDK kit will not work with SQL developer/data modeler. 

How to Check Which User is Connected to Current Oracle Session? 3 different methods

There are three different simple ways to identify the current user session in oracle sqlplus.

1.     Using DBMS_UTILITY. OLD_CURRENT_USER function.

select DBMS_UTILITY.OLD_CURRENT_USER from dual;

SQL> /

OLD_CURRENT_USER

How to Modify / Set SQLPlus Prompt With Connected Username and SID?

It is always better have the username and the database SID to be set in the sqlplus prompt to avoid confusions. It will be very useful to identify the database where you connected especially your work involved in multiple databases.

        1.   Setting the prompt temporarily with the current connected session.

set sqlprompt "_CONNECT_IDENTIFIER'-'_USER> "

esbtst-SYS>

How to Avoid / Stop /Restrict the Dropping of User Schema by Mistake from Oracle Database?

Suppose multiple DBAs are working on simultaneously in different databases, there are chances of dropping the user or schema by mistake. Eg: Wrongly dropping the user schemas in similarity of the names etc. Suppose if the schema is having objects it will throw the error if you are not using the cascade option. If you use cascade option with drop command it will delete the user without asking for any kind of confirmation. It can be avoided by using the following trigger which can be created under sys/system schema.

How to Exclude MLOG$ Materialized View Log Tables from the Data Pump IMPDP

Generally if you use exclude parameter with materialized view, materialized view log, it will not exclude the mlog$ tables from the import. You have to useTABLE also with the exclude parameter. Please see an example here.

Example

I am going to create a materialized view and a log for the same.

SQL> create materialized view log on dept;

Oracle dbms_utility.get_dependency to Get Dependent Objects or Dependencies of a Table

It is a common requirement for a DBA or developer to find out the dependent objects or dependencies for a table. You can use dbms_utility .get_dependencypackage for this purpose.

Syntax:

exec dbms_utility.get_dependency  ('TABLE', 'TABLEOWNER', 'TABLENAME');

See some examples here.

SQL> SET SERVEROUTPUT ON

SQL> CREATE SYNONYM SYN_EMP FOR EMP;

Synonym created.

How to Kill or Resume [1]+ Stopped Unix Jobs

When you press ctrl-z on actively running job or command it will suspend the job and release the prompt. But the job will be stopped and remain idle in the back ground. If you try to logout the window or session you will get a message saying “There are stopped jobs.” You can stop or resume the jobs using following commands.

jobs –p command will give the process id for the suspended job.

jobs –l command will list the details of the suspended job

See examples below.

How to Add or Insert Current Log Time and Date to a Notepad File Or Whenever You Open the file by Default

Suppose if you have habit of logging all tasks to be performed or already performed with time and date, then you can use windows notepad easily for this purpose. This trick works with all major versions of the Windows.

There are two options

1. Insert time stamp whenever and wherever you needed.

Open the notepad file and move the cursor to the location where you need the timestamp and just press Function Key 5(F5).