Oracle 8i 9i 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.
Thanks for coming by!!
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'
alter database datafile '/u04/oradata/wpk/temp01.dbf'
resize 500m;
select * from product_component_version;
select owner table_name, num_rows
from dba_tables where num_rows > 0
select sid, serial#,user#, Username,
machine, program, server, status, command, type from v$session order
by username
select sid, serial#, status, server
from v$session where username = 'BROWNBH';
select 'Select count(*) from '
||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner,
table_name
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
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
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'
select owner, sum(blocks) Totalblocks,
sum(bytes)TotalBytes from DBA_SEGMENTS group by owner
select tablespace_name, sum(blocks)
Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by
tablespace_name
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#
select * from V$VERSION
select owner,tablespace_name,segment_name,bytes,extents,max_extents
from dba_segments where extents*2 > max_extents
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)
select * from
v$database
select count(*) from v$archived_log
select min(completion_time) from
v$archived_log
select * from v$archive_dest
select count(*) from v$backup_redolog
select * from v$log
select * from dba_data_files order
by tablespace_name, file_name