Gaius is right, use DBA_TABLES for NUM_ROWS and DBA_SEGMENTS for size:
select owner,table_name,num_rows,last_analyzed from dba_tables;
The num_rows count is as of LAST_ANALYZED date, which should be close enough even without running DBMS_STATS.
For sizing:
select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;
or (depending what level of details you need):
select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;
For LOBs you'll need to join it to DBA_LOBS, for indexes to DBA_INDEXES, for tables to DBA_TABLES. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:
select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';
Your solution would really work only with small DB as it would not be feasible to read all data on large DB.
I am hesitant to post how to address ORA-1555 as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention in your database (the ixora link is relevant to a DB without auto undo management).
You should be able to use the newer EZConnect format: host:port/service:connection_type saving you the hassle of adding a TNS entry.
The connection_type and port are both optional and will default to dedicated and 1521 respectively.
Changing the connect parameter of your monitoring command as follows should do the trick:
--connect "oracle-server:1551/OFFDB1"
Best Answer
As always: it depends. However in most cases the default values are adequate.
The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50?
INITRANS specifies the minimum number of transaction slots (ITL slots) in a database block. Of course, the number of ITL slots can go higher if needed, the database manages that. The maximum number depends on the database block size (as the ITL entries are not allowed to occupy 50% of more of a database block, and 1 ITL entry takes 24 bytes), and MAXTRANS (=255 from version 10.2).
So, if:
then yes, the database block will need the 50 ITL slots. But even then, most likely you will not need to set INITRANS to 50, because the database will manage this automatically, and 50 is well below the actual limit in a standard 8K database block.
This may not be true for
DELETE,UPDATE,SELECT ... FOR UPDATE, as when they happen, the database block is most likely full and the number of ITL slots may not increase in that case. Imagine a full database block that stores 50 rows and has 2 ITL slots, and 50 concurrent transactions want to lock 1 row each withSELECT ... FOR UPDATE. But withINSERT, that uses empty database blocks or continues writing to existing ones with free space in them, it should not be a problem.Note that there are many ifs, mays and questions here, and as I said, in most of the cases, the default values are adequate. Yes, I can create an artificial example that works as above, but real scenarios are fortunately different.