Oracle SQL Scripts 23c 21c 19c 18 12c 11g 10g 9i 8i Oracle DBA Remote DBA Database Administration Remote ORACLE support Harman Research Inc

calendar Nov 8, 2023 · 4 min read · Oracle Scripts SQL  ·
Share on: linkedin copy

Oracle 23c 21c 19c 18 12c 11g 10g 9i 8i SQL Scripts and Database Commands

Below are some Handy Dandy SQL Scripts Used on a regular basis.  You might find them helpful in your daily activities.  If you need help or are interested in remote DBA services please Contact Us .

Check out the the Books/Manuals that we use Regularly.

Also, feel free to check out our Solaris UNIX page or the SQL Server page. There is also an Oracle SQL Scripts page

Thanks for coming by!!



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;

There is also an Oracle DBA SQL site with further examples

Posts in this Series