Backing up and restoring HP Systems Insight Manager 6.3 database in a Windows Environment

Thequerywouldlooklike:

exec

dbms_stats.gather_schema_stats(‘HPSIM’,dbms_stats.auto_sample_size)

;

12. After making sure statistics is up to date, run the query to locate empty tables:

Select table_name from dba_tables where owner=’<dbuser>’ and

num_rows=0;

Note that in the query, you should use dbuser in uppercase. For example, if the dbuser is

‘hpsim’, use ‘HPSIM’ in the query above, as that is how it is stored in the database.

The query would look like:

Select table_name from dba_tables where owner=’HPSIM’ and

num_rows=0;

Select table_name from dba_tables where owner=’hpsim’ and

num_rows=0; may not work.

13. If there are any tables returned by the query above, you have empty tables that will not be

exported by Oracle 11g exp utility.

14. A workaround for this problem.

For each of the empty tables, running the below command would make the table visible to

Oracle 11g exp utility and therefore get exported as expected.

Alter table <table_name> allocate extent;

15. There could be several tables that are empty. To generate a convenient script that includes all of

the empty tables, Enter:

Sqlplus <dbuser>/<dbpassword>

>set echo off;

>set feedback off;

>set heading off;

>set pagesize 0;

>spool fix_empty_tables.sql;

>select ‘alter table ‘ || table_name || ‘ allocate extent;’ from

dba_tables where owner=<dbuser> and num_rows=0;

>spool off;

>quit;