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).
Looking at the syntax I would expect that COLUMS size_clause is a global setting, that can be overridden by specific size_clause for a single column. So in case of FOR COLUMNS SIZE 5 col1 SIZE 6, col2 means SIZE 6 takes precedence for col1, but col2 falls back to the default SIZE 5. This is what I would certainly expect, but I haven't verified this experimentally.
Best Answer
Are you using Oracle 7? If not, please make sure to always use current versions of documentation. Oracle is already on 12C, and while some concepts still the same, a huge part of features has changed.
You actually won't get column size. This is why it is only an "estimate". You need to have an idea of what you are going to need, and then you can use the package dbms_space with the create_table_cost procedure to get an idea of space required.
For example:
I need to create a table with 3 columns (val1, val2, val3), and I know each one is going to take up 4bytes (3 of literals, like abc, and the leading byte that varchar2 takes). I also estimate that this table will have approximately 1000 rows. Now just think about cartesian graph:
and use the following to estimate the size:
Regarding your last question, to estimate the size of an existing table, you can check the view dba_segments (also to query index and other objects size).
Example: