Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts

Thursday, November 12, 2009

ORACLE - DBA Tips Corner

ORACLE - DBA Tips Corner: "The BECOME USER System Privilege

"Oracle has an undocumented OCI function called upicui that will allow a given oracle user to become another user. This user has to have the 'BECOME USER' system privilege."

Tuesday, December 02, 2008

Oracle JOB cheatsheet

begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin insert into jobtest(ztime) values(systimestamp); end; '
,start_date => trunc(sysdate, 'CC')
,repeat_interval => 'FREQ=MINUTELY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;

begin
DBMS_SCHEDULER.DROP_JOB('DEMO_JOB_SCHEDULE');
end;

-- show all jobs

select *
from dba_scheduler_jobs
where owner='MHTEST';


-- job history

select log_date
, job_name
, status
from dba_scheduler_job_log
where owner='MHTEST';

-- running jobs

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs

Friday, October 19, 2007

Oracle's Sample Schemas: Saying Goodbye to Scott

Oracle's Sample Schemas: Saying Goodbye to Scott: "With the new sample schema, the users have a dependency upon one another. Oracle points out the order of the creation for you in case you want to create these schemas manually. The names - HR, OE, PM, QS, and SH - are in alphabetical order, and that order specifies the order of creation. All installation options (personal, standard and enterprise) receive the first four schemas, and only the enterprise edition with partitioning receives the SH schema. For your convenience, Oracle provides a master script that creates the entire sample schema, and it is found in ORACLE_HOME\demo (the mksample SQL script)."

Wednesday, September 12, 2007

oracle: how to defragment a table

ALTER TABLE emp ENABLE ROW MOVEMENT;
ALTER TABLE emp SHRINK SPACE COMPACT;
ALTER TABLE emp SHRINK SPACE;

Monday, January 15, 2007

oracle tracing

oracle tracing
FLOWS schema flows_010309 has been granted "alter session" & "create any trigger"

SQL> connect flows_010309@rcdb
Enter password:
Connected.
SQL> create or replace trigger login_trigger
2 after logon on database
3 begin
4 execute immediate 'alter session set sql_trace=true';
5 end;
6 /

Oracle Locks

Oracle Locks: "Oracle Table Locks
Version 10.2"