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;

Recursive Function, Procedure, PLSQL in Oracle

In oracle we can write PLSQL blocks with recursive calling.

1.   See an example of recursive function for printing multiple table.

SQL> create or replace function mul (n number) return number as

begin
      IF n <= 1 THEN
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));
            RETURN 1;   
      ELSE
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));   
            return mul(n-1);

DGMGRL ORA-16600: not connected to target standby database for failover

Error Description:

DGMGRL data guard failover filed with following error.
DGMGRL> failover to 'MQPRD';

Performing failover NOW, please wait...

Error: ORA-16600: not connected to target standby database for failover

Failed.

Unable to failover

DGMGRL Error: ORA-16647: could not start more than one observer

Problem Description:

DGMGRL start observer failed with Error: ORA-16647: could not start more than one observer
Failed.
DGMGRL> start observer;
Error: ORA-16647: could not start more than one observer
Failed.
DGMGRL> exit
Solution Description

The problem is the observer is already started.

DGMGRL Error: ORA-16820: fast-start failover observer is no longer observing this database

Error Description

Data guard DGMGRL show configuration command gives Error: ORA-16820: fast-start failover observer is no longer observing this database

DGMGRL> show configuration verbose

Configuration - TRPROCONFIG
  Protection Mode: MaxAvailability
  Databases:
    TRPRO         - Primary database
      Error: ORA-16820: fast-start failover observer is no longer observing this database
    TRPRO_STANDBY - (*) Physical standby database

ORA-06512 ORA-06503: PL/SQL: Function returned without value

Error Description:
Select from the oracle function return the following error
SQL> select fib(3) from dual;
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "STHOMAS.FIB", line 4
Solution Description:

A function must return a value when you select from the same.

Oracle Session Disable Commit in Procedure

This is an interesting feature. This is a very useful feature to test a Oracle procedure without committing the actual changes made by the procedure. If you disable the commit in procedure you will get an error while executing the procedure.

See an example here.

SQL> create procedure xyz is

begin

Oracle Query to find out the SQL Text Using the Unix Process ID SPID, SID and for Long Running Sessions

If you have process id (SPID) with use following query

set pages 1000

set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT  T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S

WHERE   S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'

Sequence Default Cache value 20; ORA-04013: number to CACHE must be less than one cycle

Error Description

SQL>   create sequence seq_norm start with 1 maxvalue 100 increment by 25 cycle;

  create sequence seq_norm start with 1 maxvalue 100 increment by 25 cycle

*

ERROR at line 1:

ORA-04013: number to CACHE must be less than one cycle