Month: July 2014

Posted on by

Managing empty tables

Does your database have a lot of empty tables of (potentially) unknown origin? While the returned space is usually insignificant, there are some simple things you can do to help manage these.
An empty table may or may not have a segment in a tablespace. It does not need one, now that we have defered segment creation, so let’s get rid of those. This SQL is helpful with that:
— empty table management
SELECT ‘exec DBMS_STATS.GATHER_TABLE_STATS(”’||owner||”’,”’||table_name||”’);’ stats_cmd,
‘select count(*) from ‘||owner||’.’||table_name||’;’ cnt_cmd, ‘truncate table ‘||owner||’.’||table_name||’ drop all storage;’ trunc_cmd
FROM dba_tables WHERE num_rows = 0 AND temporary = ‘N’ AND nested = ‘NO’ AND segment_created = ‘YES’
AND owner NOT LIKE ‘%SYS%’ AND owner NOT LIKE ‘APEX%’ AND owner NOT IN (‘OUTLN’, ‘PUBLIC’, ‘DBSNMP’, ‘XDB’, ‘ORDDATA’);
There are 3 columns here, the first gathers new stats for the tables. This is very safe and very fast and will eliminate from consideration any tables that actually have rows.
The second column is a set of count commands, you can run these to verify that all the tables are empty, because the third column is DDL to truncate the tables and remove the segments. You want to be careful with this of course, hence the first 2 columns. I would visually check the listing before running it, and of course a backup of the tables is always a good idea. In fact, exporting the tables is an other way to confirm they are truly empty since the expdp logs show the records exported.
Note that if there are foreign key dependencies on any table, the truncate will fail. It’s up to you if it is worth disabling and re-enabling the dependent FK around the truncate.
If these tables were created by your own development process, you might want to give a list of tables to development for possible removal. You can check that the tables are not used by any objects inside the db with the following SQL. This does not prove that no outside code (or dynamic SQL inside PL/SQL) uses the tables, so you still must be cautious and remove these first in an environment where thorough testing takes place.
— empty tables with no “in db” dependencies
— empty tables
SELECT owner, table_name FROM dba_tables
WHERE num_rows = 0 AND temporary = ‘N’ AND NESTED = ‘NO’
AND owner NOT LIKE ‘%SYS%’ AND owner NOT LIKE ‘APEX%’ AND owner NOT IN (‘OUTLN’, ‘PUBLIC’, ‘DBSNMP’, ‘XDB’, ‘ORDDATA’)
MINUS
— empty tables with “in db” dependencies
SELECT referenced_owner, referenced_name FROM dba_dependencies d
WHERE (referenced_owner, referenced_name) IN
(SELECT owner, table_name FROM dba_tables
WHERE num_rows = 0 AND temporary = ‘N’ AND NESTED = ‘NO’ AND owner NOT LIKE ‘%SYS%’ AND owner NOT LIKE ‘APEX%’
AND owner NOT IN (‘OUTLN’, ‘PUBLIC’, ‘DBSNMP’, ‘XDB’, ‘ORDDATA’));
Posted on by

Is it safe to drop that tablespace?

When dropping tablespaces, I like to use the “including datafiles” clause so I don’t have to find and remove the underlying datafiles. That presents a problem because that clause is only accepted if you add the “contents” clause (including contents and datafiles), meaning it should drop any objects with segments in the tablespace. Not a problem since you checked the dba_segments first, right?
WRONG!!!  if you tell Oracle to drop the contents, it will drop objects that have no segments, but would create a segment there if a row was inserted. By default, 11g will defer segment creation for a table or index if there are no rows when the object is created (very common case). So you’d better check for this before you drop the tablespace. If only Oracle would allow us to leave off the contents clause (check and fail if there are any dependent objects, but drop the datafiles if the TS is dropped), but alas it does not, so we must be diligent to check this if we want to drop datafiles as part of the tablespace drop.
Here is the contents of a SQL script I use to check that a tablespace has no dependencies. Try this on a tablespace you know has objects and also a non-existent tablespace, to see the output.
set ver off pages 0 feed off
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_segments!’) FROM dba_segments WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_tables!’) FROM dba_tables WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_tab_partitions!’) FROM dba_tab_partitions WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_tab_subpartitions!’) FROM dba_tab_subpartitions WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_indexes!’) FROM dba_indexes WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_ind_partitions!’) FROM dba_ind_partitions WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_ind_subpartitions!’) FROM dba_ind_subpartitions WHERE tablespace_name = ‘&&1’;
exit
Posted on by

Cleaning up invalid objects

Many databases seem to accumulate invalid objects over time. If you are monitoring for objects becoming invalid, you have to learn to ignore those and then we have a tendency to ignore all the warnings about objects being invalid, and that is not good.
Since these objects cannot be used, why not remove them? Getting rid of code is a bit scary, maybe we will have to write something like it again in the future, so it would be nice to have a backup. Regular backups from before the time you dropped the objects would work, but will you have them, or want to use them, then the time comes to get that code back? Better to make a specific backup of those objects and keep it around for a long time, “just in case”.
This SQL will list the invalid objects, along with DDL to back up the code and to drop them after the backup is completed and archived somewhere safe.
SELECT ‘Invalid ‘ || object_type || ‘: ‘ || owner || ‘.’ || object_name listing,
‘select dbms_metadata.get_ddl(”’ || object_type || ”’,”’ || object_name || ”’,”’ || owner || ”’) from dual;’ backup_cmd,
‘drop ‘ || object_type || ‘ ‘ || owner || ‘.’ || object_name || ‘;’ drop_cmd
FROM dba_objects WHERE status = ‘INVALID’ ORDER BY owner, object_name;
The first column could be emailed to interested parties to let them know what you are about to do.
The second column can be put into a script and wrapped with spool commands to make the backup file.
The third column is the DDL to actually perform the drops.
I used these commands around the backup lines to make a backup into a .sql file:
set long 300000 pages 0 lines 140
set trimspool on
set head off
spool invalid_code.sql
…
spool off