Re: Generic database queries

In article <53938_at_arctic.nprdc.navy.mil>, vhall_at_nprdc.navy.mil (Valerie Hall) writes...
>
> I'am looking for some generic software that you may have written that
>will do numerous Oracle DBA database status queries. I know that I can

 Stuff ...

>
>Thanks in advance.
> Valerie Hall

I too would like any handy SQL scripts you might have for DBA tasks. Here are a few I've got.

• cut here =============================================================

/* FREE.SQL */ select file_name,

   sum(dba_free_space.bytes) "Free Bytes",    dba_data_files.bytes "Total Bytes",
   sum(dba_free_space.blocks) "Free Blocks",    dba_data_files.blocks "Total Blocks"
from SYS.dba_data_files, sys.dba_free_space where sys.dba_free_space.file_id = sys.dba_data_files.file_id group by file_name
/

• cut here =============================================================

/* SHOW_TABLES.SQL */
select table_name, tablespace_name, pct_free /* Oracle 7 only
, num_rows
*/
from user_tables
/

• cut here ============================================================= /* SHOW_USERS.SQL */ set pagesize 22 col username format a10 col user_id format a10 col password format a10 col default_tablespace format a15 col temporary_tablespace format a15 col created format a10 col expires format a10

select username, user_id, password,

   default_tablespace, temporary_tablespace,    created, expires
from sys.dba_users;

• cut here =============================================================