Home » SQL & PL/SQL » SQL & PL/SQL » Zero rows in User_Tables
Zero rows in User_Tables [message #193072] Thu, 14 September 2006 15:07 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

Wanted to see the table names which have zero rows and i used this, but it shows also the tables which have rows >10 in the list.
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

SQL> select count(*) from user_Tables where num_rows=0;

  COUNT(*)
----------
        21

SQL> select table_name from user_Tables where num_rows=0;

TABLE_NAME
------------------------------
ALRT_CD_TMP
ALRT_RTN_TMP
BAD_PSWD
DDA_ACL
DEP_ERR
DEP_TOT_SHDW
INV
INV_MTCH_RULE
INV_OPN_FILE
INV_STS
INV_STS_HIST

TABLE_NAME
------------------------------
IP_ADDR_FLTR
LOCKBOX_HLDY
MICROSOFTDTPROPERTIES
OPER_FCTN
RTN_ALRT_RTN
SBSD_INV_MTCH_RULE
TOAD_PLAN_SQL
TS_LNK_RPT
WIRE_TAG_LOOKUP
WRT_OFF_TLR

21 rows selected.

SQL> select count(*) from WIRE_TAG_LOOKUP;

  COUNT(*)
----------
         0

SQL>  select count(*) from WRT_OFF_TLR;

  COUNT(*)
----------
        12

SQL> select count(*) from RTN_ALRT_RTN;

  COUNT(*)
----------
         0

SQL> select count(*) from INV_MTCH_RULE;

  COUNT(*)
----------
         3
SQL> select count(*) from INV_STS;

  COUNT(*)
----------
         5

How to get the table names which have zero rows.

Thanks




Re: Zero rows in User_Tables [message #193074 is a reply to message #193072] Thu, 14 September 2006 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>How to get the table names which have zero rows.
At what point in time?
Values can & will change over time.
SELECT COUNT(*) from MY_TABLE
could return 0, but before you can report or use that 0 value
a row could be inserted into the table.

[Updated on: Thu, 14 September 2006 15:21] by Moderator

Report message to a moderator

Re: Zero rows in User_Tables [message #193075 is a reply to message #193072] Thu, 14 September 2006 15:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Most reliable method is to count it, as you did.
>>select count(*) from WIRE_TAG_LOOKUP;

The deviation is becuase of the auto sampling.
Try without using it.
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

and be aware that num_rows will not reflect the actual count unless you update the stats.


and anacedant is faster than me Smile

[Updated on: Thu, 14 September 2006 19:51]

Report message to a moderator

Re: Zero rows in User_Tables [message #193261 is a reply to message #193075] Fri, 15 September 2006 08:20 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You might also want to look at the LAST_ANALYZED column in the USER_TABLES to see when those statistics were generated just to give you an idea.
Re: Zero rows in User_Tables [message #193267 is a reply to message #193261] Fri, 15 September 2006 08:55 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks All

It worked after i gather stats (Schema_Owner) without sample size and other parameters now it shows only those tables which have zero rows, Yes i agree this might change if any body inserts a row,


i have another question not related to this but,
i have a DBMS_JOB which calls procedure like this

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => '/* job_name: JOB_EXPIRE*/BEGIN pkg_expire.expire_groups; END;'
     ,next_date => to_date('22/09/2006 00:01:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE + 7, ''MI'')'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

If it fails i know we have to see the column BROKEN in user_jobs,
My question is is there any way if it fails, unless we see in this table we will not know, and suppose if code procedure we are calling there some error occurs how can we trace , i was thinking like after the select statement / or Update or Delete raise exception ON_ERROR insert into a table and every morning check this table, is this approach correct or some other way of tracing a failed Job and loggin errors from the procedure you people can suggest.


Thanks

Thanks for Help,
Re: Zero rows in User_Tables [message #193545 is a reply to message #193267] Mon, 18 September 2006 03:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Errors in jobs are logged in the Alert.Log

If I were you, I'd give serious consideration to writting a logging system for your jobs, so they can record details of their execution in a table for later reference, and possibly email someone if they fail.
Previous Topic: NAN and INFINITE
Next Topic: DB Link between different oracle versions 9i to 10g
Goto Forum:
  


Current Time: Mon Dec 05 05:07:11 CST 2016

Total time taken to generate the page: 0.08978 seconds