Oracle Scripts - Oracle SQL Plus Scripts

CatalogShop.info
Shop Till Ya Drop!
Click Here

junk mail Opt Out

Oracle 9i SQL Scripts and Database Commands

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 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

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)