We are in the process of building a list of simple scripts used every day by DBA and others. If you have your own scripts you reuse in a text file on you workstation and would like to send it to us. Please Contact Us. You might find some of these helpful in your daily activities.
- Security Grants
- Resizing A Data File
- Show All Product Information
- Show Row Counts For All tables That Have ANALYZE On
- Select All Users Active In The system
- Show What a Current User Is Doing
- Create Count For All Tables
- Show All Indexes
- Show All Tables
- Show Space Used
- Sum Space By Owner
- Sum Space By Tablespace
- Show Reads And Writes By File Name in Oracle DB
- Show Versions Of Software
- Identify Segments That Are Getting Close To Their Max-Extent Values
- Identifies Segments That Are Getting Close To Running Out Of Contiguous FreeSpace
- Displays Archived Redo Log Information
- Display Count Historical Archived Log Information From The Control File>
- Shows Current Archive Destinations
- Backups of Archived Logs
- Display All Online Redo Log Groups For The Database
- Show All Datafiles For Tablespace And Oracle Stuff
Security Grants
grant select on PERSON_TABLE to public with grant option; select * from dba_tab_privs where TABLE_NAME = 'PERSON_TABLE' select * from dba_role_privs where granted_role = 'PORTMAN_TABLE'
Resizing A Data File
alter database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m;
Show All Product Information
select * from product_component_version;
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 = 'BROWNBH';
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'
Sum Space By Owner
select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner
Sum Space by Tablespace
select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by tablespace_name
Show Reads And Writes By File Name In Oracle DB
select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file#
Show Versions Of Software
select * from V$VERSION
Identify Segments That Are Getting Close To Their Max-Extent Values
select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents
Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space
select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes)
Displays Archived Redo Log Information
select * from v$database
Display Count Historical Archived Log Information From The Control File
select count(*) from v$archived_log select min(completion_time) from v$archived_log
Shows Current Archive Destinations
select * from v$archive_dest
Backups Of Archived Logs
select count(*) from v$backup_redolog
Display All Online Redo Log Groups For The database
select * from v$log
Show All Datafiles For Tablespace And Oracle Stuff
select * from dba_data_files order by tablespace_name, file_name