Home » SQL & PL/SQL » SQL & PL/SQL » table_name wise no. of records
table_name wise no. of records [message #220639] Wed, 21 February 2007 08:26 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all

sir actually i want to display table_name wise no of records for the corresponding tables for all the tables in the current schema. can i do this ?

i.e
table count(record)
------- --------------
emp 12
dept 4

i m confused tooo.
can any one help me on this .

to get no of column i did

select ut.table_name, count(utc.column_name) from .....

to get no of records from each table is a little bit difficult.

regards
Re: table_name wise no. of records [message #220645 is a reply to message #220639] Wed, 21 February 2007 08:33 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
SELECT table_name, COUNT(*) total
FROM user_tab_columns
GROUP BY table_name
Re: table_name wise no. of records [message #220658 is a reply to message #220639] Wed, 21 February 2007 09:04 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello pmaupoil


this time u mis-understand my request.

i asked to get no of rows of their table which is present in sam schema.

your query give no of column of their respective table.

waiting for the another help Cool

regards
Re: table_name wise no. of records [message #220662 is a reply to message #220658] Wed, 21 February 2007 09:10 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
SQL> exec dbms_stats.gather_schema_stats('HR');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows
  2  from dba_tables
  3  where owner = 'HR';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
COUNTRIES                              25
DEPARTMENTS                            27
EMP                                    14
EMPLOYEES                             107
JOBS                                   19
JOB_HISTORY                            10
LOCATIONS                              23
REGIONS                                 4

8 rows selected.



regards
Taj
Re: table_name wise no. of records [message #220663 is a reply to message #220639] Wed, 21 February 2007 09:10 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
sorry can't understand what you are precisely asking...
Re: table_name wise no. of records [message #220665 is a reply to message #220639] Wed, 21 February 2007 09:15 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
ok I am going back to bed guys... Embarassed
Re: table_name wise no. of records [message #220667 is a reply to message #220665] Wed, 21 February 2007 09:16 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

.

[Updated on: Wed, 21 February 2007 09:58]

Report message to a moderator

Re: table_name wise no. of records [message #220816 is a reply to message #220639] Thu, 22 February 2007 04:48 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member

sir,
what query u have given me is not working, might be i m doing something wrong. please go through it.




TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------
IOT_NAME                         PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT
------------------------------ ---------- ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS
----------- ----------- ----------- ------------ ---------- --------------- --- - ----------
    BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
---------- ------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE     T S
------------------- ---------- ---------- ----- -------- ----------- --------- --- ------------ - -
NES BUFFER_ ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN
--- ------- -------- --- --- --------------- -------- --- ------------------------------ --------

SALGRADE                       SYSTEM
                                       10         40          1        255          12288
      12288           1         249           50          1               1 YES N


27 rows selected.


SQL> ED
Wrote file afiedt.buf

  1* SELECT * FROM USER_TABLES



SQL> SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA
ACCOUNTS
AREA
ASH
ASHI
BATCH_STU_CLU_EX
BONUS
CIRCLE
CUSTOMER
DEMO_STUDENT
DEMO_STUD_BOOKS_ISSUED
DEPARTMENT
DEPT
D_HALF
EMP
EMP1
EMP12
EMP2
EXAM
HALF
HALF_1
MEMBER_DET
PASS_RES_DETAILS
RAMU
RAMU1
SALGRADE
SMP_VDN_NODE_LIST
STUDENT_CLU_EX
TIM
TIMEIN
TRANS_MAS_NEW

31 rows selected.


from the above i get names of table but unable to get its value on right side (means-no. of rows).

regards
Re: table_name wise no. of records [message #220820 is a reply to message #220639] Thu, 22 February 2007 04:57 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
u have to gather statistics of the perticular schema of which u want to get the no of rows.then only u will be able to view the no of records for the corresponding tables
Re: table_name wise no. of records [message #220826 is a reply to message #220816] Thu, 22 February 2007 05:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
YOu have to first gather schema statitics.

What is your schema/user name ?
sql>exec dbms_stats.gather_schema_stats('&put_your_schema/user_name');

SQL> select table_name, num_rows
  2  from dba_tables
  3  where owner = '&put_your_schema_name';

[pre]
sql>show user >>> to findout what is your schema name.
conn with system user and execute above ( dbms_stats) package.

regards
Taj
Re: table_name wise no. of records [message #220844 is a reply to message #220639] Thu, 22 February 2007 06:28 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all

as per suggestion i did but failed to get.


SQL> CONN
Enter user-name: SYSTEM
Enter password: *******
Connected.
SQL> SET SERVEROUTPUT ON
SQL> exec dbms_stats.gather_schema_stats('SCOTT/TIGER');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows
  2  from dba_tables
  3  where owner = 'SCOTT';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA
ACCOUNTS
AREA
ASH
ASHI
BATCH_STU_CLU_EX
BONUS
CIRCLE
CUSTOMER
DEMO_STUDENT
DEMO_STUD_BOOKS_ISSUED
DEPARTMENT
DEPT
D_HALF
EMP
EMP1
EMP12
EMP2
EXAM
HALF
HALF_1
MEMBER_DET
PASS_RES_DETAILS
RAMU
RAMU1
SALGRADE
SMP_VDN_NODE_LIST
STUDENT_CLU_EX
TIM
TIMEIN
TRANS_MAS_NEW

31 rows selected.



what more i have to do ?

regards
Re: table_name wise no. of records [message #220847 is a reply to message #220844] Thu, 22 February 2007 06:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
SQL> exec dbms_stats.gather_schema_stats('SCOTT/TIGER');oh ...

you have to put just SCHEMA/USER_NAME not PASSWORD.

SQL> exec dbms_stats.gather_schema_stats('SCOTT/TIGER');

PL/SQL procedure successfully completed.


Really it is execute ???

Actually Your username is SCOTT not SCOTT/TIGER.

SQL> select table_name, num_rows
2 from dba_tables
3 where owner = 'SCOTT';

Re: table_name wise no. of records [message #221012 is a reply to message #220639] Fri, 23 February 2007 04:00 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
how will i know what schema is ?
what command i execute to know about my schema.



regards
Re: table_name wise no. of records [message #221031 is a reply to message #221012] Fri, 23 February 2007 05:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your schema is the user you logged in as.
Shouldn't be hard to remember.

You can use the builtin USER to get it if you need to use it programatically.
Re: table_name wise no. of records [message #221104 is a reply to message #221012] Fri, 23 February 2007 22:20 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Actually you are not read my previous post clearly i already mention there how can you know about which user you want to gather schema.
sql>show user >>> to findout what is your schema name.
conn with system user and execute above ( dbms_stats) package.



regards
Taj
Previous Topic: Explain plan changes drastically using CTAS....
Next Topic: ORA-00600: [17012],
Goto Forum:
  


Current Time: Sun Dec 04 22:58:34 CST 2016

Total time taken to generate the page: 0.08647 seconds