Oracle SQL Scripts 23c 21c 19c 18c 12c 11g 10g 9i 8i Oracle DBA Remote DBA Database Administration Remote ORACLE support
Oracle SQL Scripts and Database Commands 23c 21c 19c 18c 12c 11g 10g 9i 8i
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 please send it to us.
To look at other scripts see the Oracle Scripts Tag
Thanks for coming by!!
- 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 Free Space
- 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
1
2grant select on PERSON_TABLE to public with grant option;
1select * from dba_tab_privs where TABLE_NAME = 'PERSON_TABLE';
1select * from dba_role_privs where granted_role = 'PORTMAN_TABLE';
Resizing A Data File
1alter database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m;
Show All Product Information
1select * from product_component_version;
Show Row Counts For All Tables That Have ANALYZE On
1select owner table_name, num_rows from dba_tables where num_rows > 0;
Select All Users Active In The System
1select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;
Show What A Current User Is Doing
1select sid, serial#, status, server from v$session where username = 'BROWNBH';
Create Count For All Tables
1select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;
Show All Indexes
1select 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
1select 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
1select 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
1select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner;
Sum Space by Tablespace
1select 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
1select 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
1select * from V$VERSION;
Identify Segments That Are Getting Close To Their Max-Extent Values
1select 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
1select 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
1select * from v$database;
Display Count Historical Archived Log Information From The Control File
1select count(*) from v$archived_log;
1select min(completion_time) from v$archived_log;
Shows Current Archive Destinations
1select * from v$archive_dest;
Backups Of Archived Logs
1select count(*) from v$backup_redolog;
Display All Online Redo Log Groups For The database
1select * from v$log;
Show All Datafiles For Tablespace And Oracle Stuff
1select * from dba_data_files order by tablespace_name, file_name;