Tablespace reorganization

In our datawarehouse we often run into the "problem" that tablespaces contain a lot of free space within the tablespace itself. Most of the time, this is caused by the fact that the warehouse programs first build up new objects and afterwards destroy old objects.
In the future we might need to have a more definitive solution for this, like converting the tablespaces to one with an uniform size.

However until then I use to do a tablespace reorganization. This means I pick a block_id of the tablespace datafile, somewhere around the point of total usage.

For this procedure to work, you need a tablespace that can temporary store the objects that lie beyond the free space in the tablespace you want to reclaim. This temporary store tablespace should NOT be an oracle temporary tablespace, but just a permanent tablespace that temporary can hold these objects.
It is preferred to create a seperate tablespace for this, as this can afterwards be removed again.

How the reorganizing works:
E.g If we have a tablespace of 10Gb in size and its datafile ( let's assume we have a single file ), has 4Gb of free space, I use the following query to determine the upper-bound block_id:

SQL> select /*+ rule */
               max(block_id)*0.7
               from dba_extents a,
               where a.tablespace_name='USERS' ;

If a tablespace has more datafiles, then you need to specify a certain datafile by adding the the file_id column in the query. In that case first look up the datafile number by joining v$tablespace and v$datafile.


After the block_id has been calculated, I use the following complex query to generate table move and index rebuild statements:



set pages 9999 lines 200
accept "Reorg tablespace : " &tabsource;
accept "Temporary store tablespace (not TEMP) : " &tabtemp;


select distinct cmd from 
(SELECT  /*+ rule */  
         'alter '
        || DECODE (segment_type
,                 'TABLE SUBPARTITION', 'TABLE'
,                 'TABLE PARTITION', 'TABLE'
,                 'INDEX', 'INDEX'
,                 'INDEX PARTITION', 'INDEX'
,                 'TABLE','TABLE'
,                 'TABLE'                   -- TABLE is the default                  
                    )
       ||' '
       ||owner
       ||'.'
       || segment_name
       || ' '
       || DECODE (segment_type, 'INDEX', 'REBUILD','INDEX PARTITION','REBUILD','MOVE')
       || ' '
       || DECODE (segment_type
,                 'TABLE SUBPARTITION', 'SUBPARTITION'
,                 'TABLE PARTITION', 'PARTITION'
,                 'INDEX PARTITION','PARTITION')
       || ' '
       || partition_name
       || ' tablespace '
       || &tabtemp
       || DECODE (segment_type, 'INDEX', ' ONLINE','INDEX PARTITION',' ONLINE')
       || ';' cmd
  FROM dba_extents
 WHERE tablespace_name = &tabsource
 --AND FILE_ID=10
 AND block_id > [Calulated Block_size]) ;


In the above query the use of FILE_ID is commented out. This because we assumed that we have only one datafile. Otherwise enter the file_id that you've found in v$datafile;


The result of this query is a lot of object-rebuild statements, that move tables and tablepartitions to the tablespace defined by &tabtemp. The indexes and index-partitions are online rebuild in the same tablespace.

Remember to SAVE the script, as you need it to move everything back to the original tablespace. 

Run the script outside business hours, as moving table objects will lock the tables,  invalidate indexes and all dependent packages/procedures.


As soon as the script is finished, purge the tablespace of its 'recyclebin' contents, by issuing as SYS:

SQL> purge tablespace USERS;

Afterwards the tablespace datafile can be shrunk to the desired size, preferably subtracting the size of the empty space in the beginning from the total datafile size.
As soon as that is finished, reopen the script you saved, edit it and change back all tablespace names, to the original source tablespace ( in this example USERS ).
Then rerun the script .

Finally use UTLRP to rebuild all invalidated packages/procedure and indexes. Also, if you created a temporary tablespace for this procedure, drop that again.

In our databases, this procedure usually frees lots of free space. Last week also I was able to return 29Gb of free space back to the OS.

Copyright: This script can be freely used. If you use it as an explanation on your own site/blog, a link to the source (my blog) is appreciated.

Which rights are Granted PUBLIC

During a schema copy action from an old database to a new one, we ran into problems that certain packages where not compiled successfully.
Some dependencies failed, because the schema user in the new database failed to have execution priviliged on certain SYS packages, like DBMS_LOCK.

The settings of both schema's where identical, so the rights must have been set in the past by using the PUBLIC account.
For those who don't know, the PUBLIC role is a role used for setting database global rights, which every user is allowed to have.


What I needed was a list of GRANT statements for the PUBLIC user that existed in the "old" database but not (yet) in the "new" database.

I started browsing the usual tables like DBA_ROLE_ROLES, DBA_ROLES, DBS_ROLE_PRIVS, but nowhere I could find the right for the PUBLIC role.

After spending almost an hour on Google, I came accross a set of views called KU$_ROGRANT_VIEW, KU_SYSGRANT_VIEW and KU$_OBJGRANT_VIEW

Especially the last one contained the information I was looking for. This table contains columns like 'GRANTEE', 'OBJ_NUM' and the privilege name 'PRIVNAME'.
Joining this table with DBA_OBJECTS, using the below query, resulted in -at least- the list with all EXECUTION grants for the PUBLIC role.
I was looking for these EXECUTION grants, because the uncompilable packages complained about dependencies, not about missing tables.

The statement:


SQL> SELECT    'grant '
       || a.privname
       || ' on '
       || b.owner
       || '.'
       || b.object_name
       || ' to '
       || a.grantee
       || ';'
  FROM KU$_OBJGRANT_VIEW a, dba_objects b
 WHERE     grantee = 'PUBLIC'
       AND a.obj_num = b.object_id
       AND a.privname='EXECUTE'
       AND b.object_name not like '%/%'
       order by b.object_name;

Using this statement on both the "old" and "new" database I was able to create a list of missing GRANT statements (there were 10 of them) that had to be executed on the "new" database.

Afterwards all packages were compiled successfully.