How helpful was this page?

ORACLE TIPS & TRICKS

The syntax and examples are written for use with Oracle 8i other versions may vary.

Moving a Table from one tablespace to another

ALTER 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-04301i: 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:

      C:\>orakill sid thread

    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$;i) 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';


For more information, please send e-mail to:
Info@Elliott-Technologies.com