The syntax and examples are written for use with Oracle 8i other versions may vary.
Moving a Table from one tablespace to anotherALTER TABLE ????? MOVE TABLESPACE ??????;
User objects ( table/index ) size / space used
The follwoing statement requires that you login as the user you'd like the statistics on.SELECT tablespace_name, name, mb
FROM (
SELECT tablespace_name, ltrim(rtrim(segment_name)) name,
sum(bytes)/1024/1024 mb
FROM user_extents
GROUP BY tablespace_name, ltrim(rtrim(segment_name))
);
All user objects ( table/index ) size / space used
The ability to see all user objects requires that you have DBA privilegdes.SELECT tablespace_name, owner, name, mb
FROM (
SELECT tablespace_name, owner,
ltrim(rtrim(segment_name)) name, sum(bytes)/1024/1024 mb
FROM dba_extents
GROUP BY tablespace_name, owner, ltrim(rtrim(segment_name))
);
Flushing the Shared Pool
The following statment can resolve the "ORA-04301: unable to allocate ... bytes of shared memory" errors and other shared memory errors, but should not be used as a solution.ALTER SYSTEM FLUSH SHARED_POOL;
Scheduling a procedure as a job
First create the procedure you want to run as a scheduled job.Then run the following from SQL*Plus:
variable x number;
execute dbms_job.submit(:x, 'procedure_name', startdate, interval); commit;
Where procedure_name is the name of the procedure you want to run as a job, startdate is the date / time to first run the job and interval is the frequency to run it (i.e. 'sysdate+1' would be once a day and 60/1440 would be once an hour).
View all running jobs
Run the following query from SQL*Plus
SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what
FROM dba_jobs_running a, dba_jobs b, v$session c
WHERE a.job = b.job AND a.sid = c.sid order by a.this_date
View all jobs
Run the following query from SQL*Plus
SELECT job, to_char(last_date, 'mm/dd/yyyy hh:mi pm') lastdate, to_char(next_date, 'mm/dd/yyyy hh:mi pm') nextdate, failures, broken, what
FROM dba_jobs
ORDER BY next_date
Change a job
execute DBMS_JOB.CHANGE(job, procedure_name, next_run_date, interval);
commit;
Killing a Session
First find the thread you want to kill by using this query:
SELECT sid, spid as thread, osuser, s.program
FROM sys.v_$process p, sys.v_$session s
WHERE p.addr = s.paddr
Then run the following for Oracle on Windows NT:
Where the sid is the name given to the Oracle Instance (the name given in the TNSnames.ora file)
Killing a running Job
It's usually a good idea to break the job to keep it from restarting on you. To do that run the following from SQL*Plus:
execute dbms_job.broken(job_id, true);
Where the job_id is obtained from the "View all running jobs" query.
Then kill the session by one of two ways.
See "Killing a Session" (the best / fast way to kill a session)or
Run the following from SQL*Plus:
alter system kill session 'sid, serial#'
Where the sid and serial# came from the "View all running jobs" query. The problem with this option is it can sometimes take a long time to kill the session.
To completely remove the job, run the following from SQL*Plus:
execute dbms_job.remove(job_id)
Where job_id is obtained from the "View all running jobs" query.
If you were merely stopping the job to fix it and didn't completely remove it, make the fix and restart the job by running the following from SQL*Plus:
execute dbms_job.broken(job_id, false);
Where the job_id is obtained from the "View all running jobs" query.
Clearing unused Temp Segments
Starting with Oracle8 you can also use an event to force the cleanup of temp segments that are not currently locked:DROP_SEGMENTS - set at session level, where TS# is the tablespace number as in the query (select name, ts# from ts$;) and adding 1 - if the value is 2147483647, then all tablespaces are cleared
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';
|
0 blogger-disqus:
Post a Comment