Important Oracle Scripts for Database Administrators (Part-2)

Show row counts for all tables that have Analyze ON
select owner table_name, num_rows from dba_tables where num_rows > 0;


Select all users active in the System
select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;


 Show what a current user is doing
select sid, serial#, status, server from v$session where username = 'SHAHEER';


Create count for all tables
select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;


Show all indexes
select owner, index_name, table_type, tablespace_name from dba_indexes where  owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, index_name, tablespace_name;


Show all tables
select owner, table_name, table_type, tablespace_name from dba_all_tables where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, table_name, tablespace_name;


Show space used
select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM';


Comments

Popular posts from this blog

Create Tables in Oracle Database using TOAD

How to create Oracle stored Procedures using TOAD for Oracle

How to Create Packages in Oracle Database using TOAD for Oracle