Wednesday, February 20, 2013

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.

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.

Thursday, February 14, 2013

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!

Tuesday, February 12, 2013

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);