Beware!!. That select will only return you the bytes allocated up to the
HWM. To know the real used space issue this for all the tables:
select count( distinct substr(rowid,15,4)||substr(rowid,1,8)) from table;
...7.X
or
select count( distinct substr(rowid,1,15)) from
You don't need to analyze. DBMS_SPACE was a solution for estimating space
without statistics.
Regards.
-Mensaje original-
De: Feng, Jun [SMTP:[EMAIL PROTECTED]]
Enviado el: viernes 16 de marzo de 2001 16:21
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Script to
Thanks a lot Ruth ,
It works fine... : )
sincerely yours,
=bambang=
Bambang Setiawan
"Ruth Gramolini" [EMAIL PROTECTED] 03/16/01 08:00
To get the number of bytes in each table in a schema you can use the
following:
select segment_name, sum(bytes) from dba_segments where
Ya , you're right : )
but I think I have to appreciate all the answers for my question ...
one replied that DBA_SEGMENTS can be used to calculate
the size of each table in a schema ( in MBytes ).
It's big clue , I still found difficulty in its implementation...
Do you have the script Tapas ?
and yes i have a pl/sql script to run the dbms_space package is anyone
is interested.
Joe
Bambang Setiawan wrote:
Ya , you're right : )
but I think I have to appreciate all the answers for my question ...
one replied that DBA_SEGMENTS can be used to calculate
the size of each table in
To get the number of bytes in each table in a schema you can use the
following:
select segment_name, sum(bytes) from dba_segments where segment_type='TABLE'
and owner='SCHEMA_NAME'
group by segment_name
This gives bytes but you can get blocks, extents, or bytes from
dba_segments.
Ruth
-
there's a fairly good script using dbms_space in the Advanced Oracle Tuning
and Administration book from Oracle Press (at least it was there in the
first edition)
From: "Joseph S. Testa" [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Hi,
Is the analyze table required when dbms_space is used to calculate the
space? I wrote a script to calculate space usage in the allocated space in
the segments. Every time I run the script, the analyze table has to be run
first.
Jun
-Original Message-
Sent: Friday, March 16, 2001
Joe,
I have an idea.
What if we create a script that opens (with the user's default editor,
of course)
every .sql file in the folder:
NT: %ORACLE_HOME%\rdbms\admin
*nixif you can get through the *nix install - you know how to rtfm
technically, it would be a virus - it could be the
Use DBA_SEGMENTS to discover the size of each table.
Platinum had a very good poster with diagrams of the metadata views for
Oracle. You may be able to get it from CA (www.cai.com) I don't think it
showed relationships, though...I can't remember.
Diana
-Original Message-
Sent:
Query DBA_SEGMENTS to get this information on table
sizes. You can query by owner and segment_type to get
schema and table information.
--- Bambang Setiawan [EMAIL PROTECTED]
wrote:
Dear Listers,
I just made script to know the size of datafile in a
tablepace ,
but I still have no idea to
You can spool the output of this SQL and run it.
select 'select '''||table_name||''', count(*) from '||table_name||';' from
dba_tables where owner='SCHEMA_OWNER';
To get the table name in the output you have to put 3 ' s before and after
the first ||table_name||.
HTH,
Ruth
- Original
Hi ,
Thanks a lot for your help , Ruth : )
sincerely yours,
=bambang=
Bambang Setiawan
"Ruth Gramolini" [EMAIL PROTECTED] 03/15/01 11:40
You can spool the output of this SQL and run it.
select 'select '''||table_name||''', count(*) from '||table_name||';' from
dba_tables where
HEY,
THERE IS SOMETHING WRONG IN THE UNDERSTANDING.
BY THE MENTIONED SCRIPT ONE CAN ONLY KNOW THE NO. OF ROWS
PRESENT IN EACH TABLE FOR THAT PARTICULAR SCHEMA NOT THE SIZE.
IS N'T IT.
-Original Message-
From: Bambang Setiawan [SMTP:[EMAIL PROTECTED]]
Sent: Friday, March 16, 2001
Dear Listers,
I just made script to know the size of datafile in a tablepace ,
but I still have no idea to make a script to know the size of each table in a schema ?
is there anyone who has the script ?
furthermore , I need some information about relationship diagram which describe
the
15 matches
Mail list logo
