ORA-00054: resource busy and acquire with NOWAIT specified

A common error we face on day to day life. Happens when someone tries to modify a table which is already locked by other user.

Now there are couple of ways to get rid of it.

   1. Check to see which user is locking the object and then kill the session.

select a.sid, a.serial#

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME='EMP';

alter system kill session 'sid,serial#'; 

2. In 11g we can set ddl_lock_timeout to allow DDL to wait for the object becomes available. We can specify how long we’d want to wait for executing DDL.

SQL> alter session set ddl_lock_timeout = 600;

Session altered.

SQL> alter table emp add (gender varchar2(10));

Table altered.

  3. If you don’t want to hamper other users, then either try executing DDL at off-peak hours or

  4. Wait for few minutes until the other user releases the lock on same object.

  5. In 11g you can put a table in readonly mode to ensure no one locking it and then execute your command.

SQL> alter table emp read only;

Session altered.

 SQL> alter table emp add (gender varchar2(10));

Table altered.

ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

1. Login as sysdba

 sqlplus / as sysdba

2. Check Current Setting of Parameters

 sql> show parameter sessions

 sql> show parameter processes

 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase "processes and "transactions" parameters.

A basic formula for determining  these parameter values is as follows:

  processes=x

  sessions=x*1.1+5

  transactions=sessions*1.1

4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.

 sql> alter system set processes=500 scope=spfile;

 sql> alter system set sessions=555 scope=spfile;

 sql> alter system set transactions=610 scope=spfile;

 sql> shutdown abort

 sql> startup 

How to SCRUB/MASK data using expdp (REMAP_DATA feature)

This is a common requirement for DBA to export data from production for various purposes. In a restricted & compliant environment it’s a must to mask / scrub particular data while exporting from production.

Here’s a simple demonstration. I’ve got the hint from metalink.

  1. Create a package to mask data

Note: The package needs to be created under the schema which would be used to connect to datapump utility. I’ve used SYSTEM user for this case.

create or replace package datapump_remap_test

as

  function toggle_case(p_value varchar2) return varchar2;

end;

/

create or replace package body datapump_remap_test

as

  function toggle_case(p_value varchar2) return varchar2 is

  begin

    return translate(p_value,    

      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()_+-=\/ ',

        'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

end;

end;

/

  2. Datapump export with REMAP_DATA parameter:

We’d be using the created package to scrub data for particular table columns. My package would basically replace all mentioned column data into ‘xxxxx’

For this example, I’ve chosen to scrub HR.FIRST_NAME & SALES. The export dump would export the FNADVI schema as well as the the table mentioned with scrubbed column.

expdp system dumpfile=fnadvi.dmp directory=dump logfile=fnadvi.dmp.log schemas=fnadvi remap_data=fnadvi.HR.first_name:system.datapump_remap_test.toggle_case remap_data=fnadvi.SALES.address:system.datapump_remap_test.toggle_case

  3. Import the schema

Now you the import can be done anywhere with scrubbed data.

WRAPPING Oracle procedure/function

In order to hide any procedure/function we can use oracle wrap utility. This would hide the code from user, but still the procedure can execute.

Here is the demonstration.

1   1. Create an sql file on database OS.

[oracle@lab ~]$ cat wrapping_proc.sql

CREATE OR REPLACE PROCEDURE wrapping_proc

IS

BEGIN

   DBMS_OUTPUT.PUT_LINE('WRAP TESTING');

 END;

/

    2. WRAP the sql file, this would create a wrapped file with .plb extension. Try the below command.

[oracle@lab ~]$ wrap iname=wrapping_proc.sql

PL/SQL Wrapper: Release 11.2.0.2.0- 64bit Production on Thu Feb 14 15:33:26 2013

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing wrapping_proc.sql to wrapping_proc.plb

3. Now copy the content of .plb file, and execute it after logging into the database.

[oracle@lab~]$ cat wrapping_proc.plb

CREATE OR REPLACE PROCEDURE wrapping_proc wrapped

a000000

1

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

50 92

AjmXfCjRF6msrZKufRFrtUPnC5gwg5nnm7+fMr2ywFwWlvJWFoVHDNCWFpeui5t0i8DAMv7S

hglpabhSm7JK/iiyveeysx0GMCyuJOqygaXKxqYCL7GPL64kvzIu9tFE6iQf9jmms29vnw==

/

4. Logging into the database to execute it.

[oracle@lab~]$ sqlplus scott/tiger

SQL> CREATE OR REPLACE PROCEDURE wrapping_proc wrapped

  2  a000000

  3  1

abcd

  4    5  abcd

abcd

  6    7  abcd

abcd

  8    9  abcd

 10  abcd

 11  abcd

 12  abcd

abcd

 13   14  abcd

 15  abcd

 16  abcd

 17  abcd

 18  abcd

 19  7

 20  50 92

 21  AjmXfCjRF6msrZKufRFrtUPnC5gwg5nnm7+fMr2ywFwWlvJWFoVHDNCWFpeui5t0i8DAMv7S

 22  hglpabhSm7JK/iiyveeysx0GMCyuJOqygaXKxqYCL7GPL64kvzIu9tFE6iQf9jmms29vnw==

 23

 24

 25  /

Procedure created.

   5.  Try executing and it works!

SQL> exec wrapping_proc;

PL/SQL procedure successfully completed.

SQL> drop procedure wrapping_proc;

Procedure dropped.

Now the interesting part is Even though Oracle claims the wrapped code can't be unwrapped. But it does!!!
There are couple of weblink where you can easily unwrap it. So the point of wrapping has become useless :(

The following link is one of them.
http://www.codecrete.net/UnwrapIt/

ORA-39083/ORA-02304 during impdp

Was facing the following errors while impdp. Reason for this error is OID should be unique in each DB. In this case, OID in the impdp was using old schema value thus failing.

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

Solution is to use transform=OID:n on my impdp parameter.

Example :

impdp username DIRECTORY=DUMP transform=OID:n DUMPFILE=schema.dmp

Now all good!

Moving LOBS along with Tables

Moving a regular table to a different tablespace is straight forward.

Alter table move tablespace  ;

And then rebuild the indexes as it becomes invalid. But what if the table has LOB segments? Similar to INDEX, the LOB doesn’t moves where the table just relocated.

You can run the followings to see where it’s located. And take decision where do you want the LOB to move.

select owner, table_name, column_name,tablespace_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name = 'SCOTT_DATA' );

Example:

select owner, table_name, column_name,tablespace_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name like '%DATA' )

and table_name like '%LOG%'

and owner like 'SL%';

If you want to move only lob segment to a new tablespace then your command will be,

ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);

We can also move table and LOB altogether with the below command:

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace
LOB (lobcol1,lobcol2) STORE AS
(TABLESPACE new_tablespace);