Home > Oracle > Performance problems with tablespace queries

Performance problems with tablespace queries

Check your oracle recycle bin as your query explain plan is probably using info from the recycyle bin

the recycle bin is enable by default

to switch the recycyle bin off


To disable for the entire database
SQL> ALTER SYSTEM SET recyclebin = OFF;
To disable for a session
SQL> ALTER SESSION SET recyclebin = OFF;

show content of your recycle bin


show recyclebin ;

Clear Recycle bin


to remove  dropped objects for the current user
SQL> PURGE RECYCLEBIN;

ro remove dropped objects for all users
SQL> purge DBA_RECYCLEBIN;

Check space occupied by the Recycle bin 8K database

select allocated_k, free_k, recycle_able_k
  from
( select nvl(sum(bytes)/1024,0) free_k from dba_free_space
   where tablespace_name = '&&tsname'),
( select sum(bytes)/1024 allocated_k from dba_data_files
   where tablespace_name = '&&tsname'),
( select nvl(sum(space) * 8,0) recycle_able_k from dba_recyclebin
   where ts_name = '&&tsname')
/

if freespace reported aganist the dba_free_space does not include the space occupied by the recycyle objects
Objects in the Recycle Bin will be automatically purged by the space reclamation process if

1. A user creates a new table or adds data that causes his/her quota to be exceeded.
2. The tablespace needs to extend its file size to accommodate create/insert operations.

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: