February 28, 2008

Space Management

1) Tablespace Utilization Report:
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name

2) Show data files usage:
SELECT SUBSTR(max(A.TABLESPACE_NAME),1,16) "Tablespace",A.FILE_ID "File ID",substr(max(A.file_name),1,43) "Data file",substr(max(A.status),1,10) "Status",(MAX(A.BYTES)-nvl(sum(B.BYTES),0))/1024/1024 "USED SIZE(Mb)",MAX(A.BLOCKS)-nvl(sum(B.BLOCKS),0) "USED BLOCKS",TO_CHAR((MAX(A.BYTES)-nvl(sum(B.BYTES),0))*100/MAX(A.BYTES),'999.99')'%' "USED USAGE",nvl(sum(B.BYTES),0)/1024/1024 "FREE SIZE(Mb)",nvl(SUM(B.BLOCKS),0) "FREE BLOCKS",TO_CHAR(nvl(SUM(B.BYTES),0)*100/MAX(A.BYTES),'999.99')'%' "FREE USAGE",MAX(A.bytes)/1024/1024 "TOTAL SIZE(Mb)",MAX(A.blocks) "TOTAL BLOCKS"
from dba_data_files A,DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID(+)
group by a.file_idorder by 1,2;

3)Tables that are Highly fragmented:
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) )
FROM dba_segments
GROUP BY segment_name)

Check locked object

1. Check locked object:
selectc.owner, c.object_name,c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a ,
v$session b,
dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

2. Then use Sid from above queries to see what its doing.
select s.sid, s.username, s.schemaname, s.osuser, s.machine, s.terminal, s.program, sa.sql_text from v$sqlarea sa, v$session s
where s.sql_hash_value = sa.hash_value
and s.sql_address = sa.address
and s.sid=&sid
select sid,username,status,blocking_session,wait_class,seconds_in_wait,state
from v$sessionwhere sid=&sid

Find & Recompile invalid objects

-- Find invalid objects
select OWNER , OBJECT_TYPE , OBJECT_NAME,STATUS
from dba_objects
where status != 'VALID'
order by owner, object_type;

-- Recompile invalid objects
select 'ALTER ' OBJECT_TYPE ' ' OWNER '.' OBJECT_NAME ' COMPILE;'
from dba_objects
where status = 'INVALID'

Oracle Scheduled Jobs

1. To change the scheduled time of oracle job and force it to run at certain time, use the set_scheduler_attribute procedure.e.g. To start job 'JOB_TEST' in next 10 min, use below script.
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
NAME => 'JOB_TEST',
ATTRIBUTE => 'start_date',
VALUE => sysdate + 10/1440
);END;


2. "RESTARTABLE" attribute specifies whether a job can be restarted in case of failure. By default, jobs are not restartable and this attribute is set to FALSE. Setting this to TRUE means that if a job fails while running, it will be restarted from the beginning point of the job.Note that setting this attribute to TRUE might lead to data inconsistencies in some situations, for example, if data is committed within a job.The Scheduler will retry the job a maximum of six times.The Scheduler will stop retrying a job when:One of the retries succeeds.All of its six retries have failed.The next retry would occur after the next regularly scheduled run of the job.You can set RESTARTABLE attribute for existing job as follows:
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'JOB_TEST' ,
attribute => 'RESTARTABLE' ,
value => TRUE
);END;

3. If you want to run job right away, there are two wayas to do this.1. Run the job in current session.
Begin
dbms_scheduler.run_job(Job_Name);
End;

This would run the job in current session (i.e. Synchronously). Coutrol would come back to you only when the job finishes running. All the error messages (if the job fails) would be logged to the screen and you won't have to dig into the job log dictionary views( dba_scheduler_job_log, dba_scheduler_job_run_details) to find the error messages. This run will not update any columns in dictionary views. i.e. There won't be any record of this execution.

2.Run the job in backgroundIf you are running a job which takes a long time to run, and therefore you want to run the job in background and not in your current session. You can do that by setting the parameter use_current_session to false in run_job procedure. This would result in the job to be run in the background by the job threads and control would immediately return to the your session.
Begin
dbms_scheduler.run_job(Job_Name,false);
End;
This run will update all the dictionary columns to record the exeution. If the job fails then the error message won't be displayed on the screen, but you will have to query the job log dictionary views ( dba_scheduler_job_log, dba_scheduler_job_run_details) to find the error message.