Getting list of populate tables in a database Options

[OU2]

Hi, recently started a new company and new to Oracle 10g. The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.

[burleson]

Hi, and welcome to the world of Oracle!


You want to get a code collection:

http://www.dba-oracle.com/oracle_scripts.htm

And ser set of beninner Oracle books:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy

**********************************
>> What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables)


Not current but as of last analyze, an easy solution:

CODE

select
table_name,
num_rows counter
from
user_tables
where
owner = num_rows > 0
order by
num_rows;

shell script

CODE

spool runme.sql

select 'select count(*) from '||table_name||';' from user_tables'||';';

spool off

@runme

SEE HERE:

http://www.dba-oracle.com/t_count_rows_all...s_in_schema.htm


Join user_tables into a PL/SQL or a sghell script:

http://www.dba-oracle.com/t_pl_sql_cursor.htm


NOT TESTED

CODE

declare

tabname   varchar2(50);

cursor c1 as
select
   table_name
from
user_tables
having count(*) > 0

BEGIN
open c1;
loop;
   fetch c1 into :tabname;

   dbms_output.put_line (:tabname);
end_loop;
close c1

http://www.dba-oracle.com/t_select_tables_rows.htm

[OU2]

Hi thanks for the reply below:

I think you are correct, I need to get some books on Oracle.

I tried the SQL statement:

select
table_name,
num_rows counter
from
user_tables
where
owner = num_rows > 0
order by
num_rows;

I ran this in a tool called Aqua Data Studio 4.7.2 and got this error:
ORA-00933: SQL command not properly ended

Do I have to substitute something in the SQL above first?

We only have 4 people in IT here and no DBA so I question if anything like last dbms_stats analyze.

is ever done.
thanks.

Hi, and welcome to the world of Oracle!


You want to get a code collection:

http://www.dba-oracle.com/oracle_scripts.htm

And ser set of beninner Oracle books:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy

**********************************
>> What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables)


Not current but as of last analyze, an easy solution:

CODE

select
table_name,
num_rows counter
from
user_tables
where
owner = num_rows > 0
order by
num_rows;

shell script

CODE

spool runme.sql

select 'select count(*) from '||table_name||';' from user_tables'||';';

spool off

@runme

SEE HERE:

http://www.dba-oracle.com/t_count_rows_all...s_in_schema.htm


Join user_tables into a PL/SQL or a sghell script:

http://www.dba-oracle.com/t_pl_sql_cursor.htm


NOT TESTED

CODE

declare

tabname   varchar2(50);

cursor c1 as
select
   table_name
from
user_tables
having count(*) > 0

BEGIN
open c1;
loop;
   fetch c1 into :tabname;

   dbms_output.put_line (:tabname);
end_loop;
close c1

http://www.dba-oracle.com/t_select_tables_rows.htm

[burleson]

Hi,

In 11g, the data will have analyzed at some point in the past, just understand that the num_rows won;t match the tables' today!

>> where owner = num_rows > 0

When using user_table you connect as your application schema owner.

And you cannot combine like that!

try

CODE

select table_name from user_tables where num_rows > 0;

or

select table_name from dba_tables where owner = 'SAPR3' and num_rows > 0;

*******************************************************
>> need to get some books on Oracle.

Well I write and published these, and I can persoally vouch for them:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy

BTW, it take YEARS to get good at Oracle, please read:

http://dba-oracle.com/googlesearchsite_pro...&q=becoming

Good Luck!

[burleson]

Hi Again,

One more thing, you may want to add the date_last_analyzed to each table:

CODE

select table_name, data+last_amalyzed from  . . .

Alkso, you can re-analyze your schema, but it is time consuming, to be done on a weekend

And BEWARE, fresh statistoics may cause some SQL execution plans to run different (usually faster) . . . .

CODE

spool runnme.sql
select
'exec dbms_stats.gather_table_stats('||OWNER||','|| 'MYTABLE'||', cascade => TRUE);'
from user_tables;
spool off
@runme.sql

http://www.dba-oracle.com/art_builder_dbms_stats.htm

[burleson]

Hi,

>> We only have 4 people in IT here and no DBA

I can be your DBA if you want . . .

I have a remote DBA company, very popular in Europe, where for a fixed fee, I take care of all of your Oracle work:

http://www.remote-dba.net/plans.htm

Just an option to consider . . . .

Good Luck!