Re: table disk usage?

Any specific reason for restricting yourself to the SQL Plus command line? I re-emphasize that OEM will help your situation and provides guided tours as mini-lessons. (OEM is included in the license)

However, to answer your questions directly:

1. Mapping table to database file ... using a DBA account other than SYS,

SELECT owner, table_name, file_name
FROM dba_tables, dba_data_files
WHERE table_name.tablespace_name=dba_data_files.tablespace_name;

Note: tables are not the only objects that can consume space. Check the Static Data Dictionary Views in the "Reference" Manual at http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/nav/docindex.htm

(which is accessed through http://docs.oracle.com or http://otn.oracle.com > documentation).

The shown select is only a possible pattern - you will need to order, add columns, etc.

Oracle internals .... objects (such as tables) go into tablespaces. Tablespaces are instantiated by one or more datafiles. At (almost) all cost avoid putting user objects into the SYSTEM tablespace.

Tablespace of type TEMP are a special case - can fill & extend with impunity. Concepts & DBA manuals discuss why.

2) Monitoring is done inherently by the (extra cost) Diagnostics pack for the OEM. It will also do time projections and alerting/paging when things go critical.

3) Drop tables - look up the "DROP TABLE" command in the SQL Reference manual for the exact syntax.

4) Reclaim space of a dropped table is inherent

5) Resize disk space allocations is dependant on a lot of other factors - simple answer may be wrong for your situation. Concepts & Database Administrators Guide.

There could be many, many reasons for creating a lot of disk usage. These include Large Object storage, temporary tables and other temporary stuff, warehouse interim tables or materialized views. You need to have someone document the purpose of each app. Received on Mon Jun 23 2003 - 11:18:17 CDT