Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Getting list of populate tables in a database
OU2
post Feb 9 2012, 11:42 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 9-February 12
Member No.: 46,743



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.
Go to the top of the page
 
+Quote Post
burleson
post Feb 9 2012, 02:12 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
OU2
post Feb 10 2012, 04:44 AM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 9-February 12
Member No.: 46,743



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.


QUOTE (burleson @ Feb 9 2012, 07:12 PM) *
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

Go to the top of the page
 
+Quote Post
burleson
post Feb 10 2012, 07:44 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Feb 10 2012, 07:54 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Feb 10 2012, 08:06 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 01:57 PM