Use of Expdp Impdp nologfile=y

Oracle data pump has one option called nologfile and by default its value is N. The purpose of this option is that if you specify the value for this parameter as Y then it will not create the log file for the export or import activity. This is very useful option when you do an import where the oracle user doesn’t have write privilege on the directory where the dump file exists. In such cases the impdp will throw the following error.

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Method to Track Oracle Schema User Password and Modification History; Find out Current, New Password

This is the solution to find out the Oracle user current password by recording the password history. This would be helpful to find out who changed the password for a user, when he changed, for which user with new password. From the recorded history you can find out the previous/old as well as current password for the Oracle user. This is achieved by oracle autonomous_transaction and profile PASSWORD_VERIFY_FUNCTION.

Here are the steps:

Sql Query to Find and Reclaim the Fragmented Space in Oracle Table

How the table hits by space fragmentation?

If a table has large number of records and the particular table is getting updated or the rows getting deleted periodically then there will be unused blank spaces (holes) in the table segments. These blank spaces will get created by the row deletions which will not be used without reorg or reclaiming. Over a period of time the volume of the unused space will get accumulated to a huge size. These unused spaces are called fragmentations. The oracle will not automatically release this space into usable free space whereas we have to perform the reorg activity to claim the fragmented space.

Example for Table Reorg and Reclaim the Fragmented Space Using Shrink Space Command

Advantages of this method:

        - Syntax: alter table

shrink space

        - Most recommended method

        - Applicable for the databases 10g and above

        - No downtime required

        - None of the depended objects will get invalidated.

        - Least time consuming method

Example
//Create a test table and insert testing data

Example for Table Reorg and Reclaim the Fragmented Space Using Table Movement

        - It is time a consuming method

        - Application down time required as the table will not be available during the time of activity

        - There are chances for the depended objects invalidation. It has to be properly verified after reorg

        - Indexes also needs to be rebuild which requires an extra time apart from the table movement

        - This activity requires extra space on other tablespace to move the table.

Difference Between Oracle Table Shrink Space, Space Compact, Space Cascade - with Example

Table shrink is an interesting feature provided by Oracle and this is very useful for the database reorganizations. There are 3 different options available with table shrink command. The table shrinking is achieved by moving the rows between the Oracle blocks. To perform the table shrinking we need to enable the row movement for the table.

Option 1. Alter table table_name shrink space

How to Shrink a Table in Oracle Database - With Example

Database reorg is the major activity for the Oracle DBAs. Oracle provides shrink table feature to reclaim the space within table after doing the deletes. Shrink is accomplished by rearranging or moving the rows between the oracle blocks. To achieve this table shrinking we need to enable the row movement feature which is associated with the table.

Example
//Creating an example table
SQL> create table objlist as select * from  all_objects;