Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Friday, July 16, 2010

shrink table in Oracle 10g/11g

In oracle 9i and below you need to export the table, drop the table and import the table to reclaim the unused space. In oracle 10g you need to perform following sql commands to reclaim the table unused space.

SQL>alter table sachi enable row movement;
SQL>alter table sachi shrink space;
SQL>alter table sachi shrink space cascade;(shrink tables and indexes on this tables)


That's it.

Finding Candidates for Shrinking:

you can use Oracle 10g/11g segment advisor to check if particular table or index requires shrinking.

Before performing an online shrink, you may want to find out the biggest bang-for-the-buck by identifying the segments that can be most
fully compressed. Simply use the built-in function verify_shrink_candidate in the package dbms_space.
Execute this PL/SQL code to test if the segment can be shrunk to 1,500,000 bytes:

begin
if (dbms_space.verify_shrink_candidate
('GUEST','SACHI','TABLE',1500000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------
T

If you use a low number for the target shrinkage, say 10,0000:

begin
if (dbms_space.verify_shrink_candidate
('GUEST','SACHI','TABLE',100000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;

the value of the variable x is set to 'F', meaning the table cannot be shrunk to 10,0000 bytes.


Summary:
1.Create table As Select (CTAS) - This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space.
2.Online reorg - Using the dbms_redefinition package you can use parallel CTAS to reorganize tables, while the tables continue to accept updates.
3.Data pump (expdp, impdp) - Rarely used in high-speed production apps, except for backups.
4.Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.
5.Alter table shrink space - Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command

Thursday, July 15, 2010

Problem with "library cache pin' when deplyoing the package

Problem: Today i have been asked to deploy a package in the production. Same package has been deployed in staging and UAT successfully with in a minute. When I deployed the same package in production it did not go through. after 10 min I got following error.

SQL> @PKGALERTS.pkb;
CREATE OR REPLACE PACKAGE BODY Pkgalerts AS
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

I tried it couple of time but got the same error again. Please help.

Solution:

select sid, event, p1raw, seconds_in_wait,wait_time
from v$session_wait
where event = 'library cache pin'
and state = 'WAITING';

SID,EVENT,P1RAW,SECONDS_IN_WAIT,WAIT_TIME
4962,library cache pin,070000031733BDF8,51,0



SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob WHERE kglhdadr='070000031733BDF8'

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status,
kglpnmod "Mode", kglpnreq "Req"
FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND
kglpnhdl='070000031733BDF8'

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='070000031733BDF8'

and then compile the object.
SQL> @PKGALERTS.pkb

Package body created.