du command

I was finally able to harness the power of the du command. The -s flag has a cryptic definition in the man page (nothing new for man there). But using the -s flag allows you to summarize the file size for directories.


This command will give you a summary of the directory sizes in root. You can of course target down to other folders like so

Update 8/17/2015

Often I need to sort the output by size. Here you go.

Oracle Index Information

Some queries to help find out infomration about specific indexes.

This will show you the indexes for a specified table

select	i.index_name
,	i.tablespace_name
,	ceil(s.bytes / 1048576) "Size MB"
from 	dba_indexes i
,	dba_segments s
where 	i.index_name = s.segment_name
and 	table_name like '&table'
order 	by 2, 1

This will show you the columns of a specific index

select 	column_name
from 	dba_ind_columns
where 	index_name = '&index'
order 	by column_position 

This will give you some extra details about the index

select index_name, num_rows, last_analyzed from dba_ind_statistics where table_name = 'Table Name';

Tracking Down Busy Oracle Sessions

This query will show the current users and the OS PID…you can use this to then run a top command to watch the session.

ACTIVE users

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
, v$process p
 Where s.paddr = p.addr
 and s.status = 'ACTIVE'
 and s.osuser <> 'oracle'
 order  by to_number(p.spid)

ALL Users (except oracle)

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and s.osuser <> 'oracle'
 order  by to_number(p.spid)

From there you can use the OS PID to run a top command to see what kind of resources the session is consuming

top -p PID

Or you can go the opposite way. Run a top command and then track down the Oracle Sessions using the PID

Find Active Users

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and p.spid IN (22057, 28919, 21972)
 order  by to_number(p.spid)

Find with a grep xargs style

find . -name “*.log” -print | xargs grep “ORA-“

Recreate Oracle Controlfile

Use the following example if you need to create a controlfile script. The script can be used to recreate a controlfile.

 

alter database backup controlfile to trace;

Once you run this head over to your trace files directory and locate the script. Find the following line in the control file script:

CREATE CONTROLFILE REUSE DATABASE "SID" RESETLOGS ARCHIVELOG

From here you can edit the path of the datafiles and redologfiles if necessary. Startup the database in nomount mode and run the script you created as sysdba.

Check on the status of the instance.

 

select status from v$instance;

Query to see the control files.

select name from v$controlfile;

It is a good idea to generate this trace file from time to time. If/when you lose a control file you can use this to rebuild it from scratch.

EXPDP using QUERY option

Took me a while to get this command escaped correctly. The query has a varchar column so you need to make sure and escape the command correctly. Also make sure you set your environment variables using ORAENV before you execute this. I left the credentials off this command so you will be prompted for credentials if you run this as is.

expdp directory=data_pump_dir dumpfile=SY00500T.dmp logfile=SY00500T.log 
tables=schema.tablename query=schema.tablename:"WHERE ENTITY_NAME='AR00200T' 
AND SEQ > 150000000"