Shrink Table

To shrink an object

ALTER TABLE employees SHRINK SPACE [CASCADE]

To shrink a LOB (must be issued separately since SHRINK SPACE CASCADE does not cascade to LOB)

ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE)

Shrink the overflow segment of an Index Organized Table (IOT)

ALTER TABLE employees OVERFLOW SHRINK SPACE
Advertisements

Use DBMS_SCHEDULER to replace DBMS_JOB

DBMS_JOB has been around forever, and now it is deprecated. Although DBMS_JOB still exists in 10g and 11g, but only for backward compatibility. No new features are being added to dbms_job and you will likely quickly run into its limitations. Oracle recommends the use of DBMS_SCHEDULER in releases 10g and up. DBMS_SCHEDULER is a much more robust package and fully-featured than DBMS_JOB. To use the DBMS_SCHEDULER package a user must be granted the CREATE JOB privilege.

DBMS_SCHEDULERincludes the following features that DBMS_JOB does not have :

  • logging of job runs (job history)
  • simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
  • running of jobs outside of the database on the operating system
  • resource management between different classes of jobs
  • use of job arguments including passing of objects into stored procedures
  • privilege-based security model for jobs
  • naming of jobs and comments in jobs
  • stored, reusable schedules

Features in releases after 10g Release 1 include :

  • dependencies between job units (10gR2 and up)
  • scheduling based on financial calendars and fiscal quarters (10gR2 and up)
  • event based jobs which run when an event is received (10gR2 and up)
  • running of jobs on remote machines (11gR1 and up)
  • e-mail notifications on job events of interest (10gR2 and up)
  • starting a job based on arrival of a file (10gR2 and up)

Here simple comparison for the codes :

  • Old using DBMS_JOB scheduler.VARIABLE l_job NUMBER;
    BEGIN
    DBMS_JOB.submit (
    job => :l_job,
    what => ‘BEGIN NULL; /* code to execute*/ END;’,
    next_date => SYSDATE,
    interval => ‘SYSDATE + 1 /* 1 Day */’);COMMIT;
    END;
    /
    PRINT l_job
  • New with DBMS_SCHEDULER scheduler.BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => ‘dummy_job’,
    job_type => ‘PLSQL_BLOCK’,
    job_action => ‘BEGIN NULL; /* code to execute */ END;’,
    start_date => SYSTIMESTAMP,
    repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);
    END;
    /

After replace DBMS_JOB with DBMS_SCHEDULER for all jobs successful, the job_queue_processes parameter can now be set to zero.

alter system set job_queue_processes=0;

There is also a forum dedicated to questions about dbms_scheduler here : http://forums.oracle.com/forums/forum.jspa?forumID=193

How to clear Oracle Enterprise Manager’s Alert

first, check this

– SELECT * FROM SYSMAN.MGMT_CURRENT_SEVERITY ORDER BY COLLECTION_TIMESTAMP

if the result is that you want to delete then go with this sql,

– DELETE SYSMAN.MGMT_CURRENT_SEVERITY

^_^

History startup & shutdown DB

1. create table to store history startup – shutdown DB

CREATE TABLE WI.TMP_DATABASE_ACTIVITY
(
USER_NAME      VARCHAR2(20 BYTE),
USER_TIME        DATE,
DESCRIPTION  VARCHAR2(30 BYTE)
)
TABLESPACE TBLSPC_01;

2. create trigger after startup

CREATE OR REPLACE TRIGGER WI.TRIG_STARTUP
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO TMP_DATABASE_ACTIVITY
VALUES (USER, SYSDATE, ‘STARTUP’);
END;
/

3. create trigger before shutdown

CREATE OR REPLACE TRIGGER WI.TRIG_SHUTDOWN
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO TMP_DATABASE_ACTIVITY
VALUES (USER, SYSDATE, ‘SHUTDOWN’);
END;
/

4. to see the history

SELECT * FROM TMP_DATABASE_ACTIVITY ORDER BY USER_TIME;

Manipulate NULL column values for fast sql performance

By default, NULL values means “not present”. Therefore, Oracle indexes will not include NULL values.

If you wonder about how slow your sql query, you must check whether your sql include where condition that use IS NULL.

When you use IS NOT NULL, your sql run normally. But if where condition include IS Null, sql performance slow down,espesially when there are join table condition.
So, i try to change it with use of index that change NULL values to ‘NULL’ string values.

for example, usually we use this,
create index ename_idx on emp (ename);

select * from emp where ename is null;

so, we can change it, this way
create index ename_idx on emp (nvl(ename,’NULL’));

select * from emp where nvl(ename,’NULL’) = ‘NULL’;

——————————————————————————————-
then as conclusion, it can said as
1. IS NOT NULL in WHERE clause use an index
2. IS NULL in WHERE clause doesn’t use an index

the solution is create an index with manipulate column with NULL values to another values
(string values or number values or any type of data).

^_^