SQL Statement Hints – A Summary See Note 35934.1 for CBO issues. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ General: – Syntax: /*+ HINT HINT … */ (In PLSQL the space between the ‘+’ and the first letter of the hint is vital as otherwise the hint may be ignored so /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause…
Job Execution Interval: ‘SYSDATE + 7’ Exactly seven days from the last execution ‘SYSDATE + 1/48’ Every half hour ‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’ Every Monday at 3PM ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’ First Thursday of each quarter Remobe the job: BEGIN DBMS_JOB.REMOVE(14144); END; / Change the job: In this example, job number 14144 is altered to…
VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT(:jobno, ‘DBMS_DDL.ANALYZE_OBJECT(”TABLE”, ”HR”, ”EMPLOYEES”, ”ESTIMATE”, NULL, 50);’, SYSDATE, ‘SYSDATE + 1’); COMMIT; END; / PRINT jobno JOBNO ———- 14144
col name format a50 col UNRECOVERABLE_CHANGE# format 999999999999999999999 col dt1 format a30 set lines 120 pages 200 SELECT NAME, UNRECOVERABLE_CHANGE#, TO_CHAR (UNRECOVERABLE_TIME,’DD-MON-YYYY HH:MI:SS’) dt1 FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE# != 0;
—————— CS Sequences maintenance——————- column last_number format 9999999999999999 select * from user_sequences where sequence_name in (‘UOC_ORDER_SEQ_ID’); select 2300000000 – CurrVAl – 20 LAST_NUMBER from dual; alter sequence UOC_ORDER_SEQ_ID increment by NOCACHE; Increment Once. select UOC_ORDER_SEQ_ID.nextval from dual; Confirm Value comes to 2300000000. After that alter sequence UOC_ORDER_SEQ_ID increment by 1 NOCACHE; alter sequence UOC_ORDER_SEQ_ID maxvalue…
SQL> SQL> CREATE OR REPLACE PROCEDURE AlterSession( 2 p_SessionString IN VARCHAR2) AS 3 4 v_CursorID INTEGER; 5 v_Dummy INTEGER; 6 BEGIN 7 v_CursorID := DBMS_SQL.OPEN_CURSOR; 8 9 DBMS_SQL.PARSE(v_CursorID, p_SessionString, DBMS_SQL.NATIVE); 10 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); 11 END AlterSession; 12 / Procedure created. SQL> SQL> column sysdate format a30 SQL> SELECT SYSDATE FROM dual; SYSDATE —————————— Jun…
CREATE TABLE STATSUSER.STATS$TOTALUSERS ( MEASURED_DATE DATE, TOTAL_USERS NUMBER, ACTIVE_USERS NUMBER, UOCWEBSPHERE_USERS NUMBER, UOCSCRIPTS_USERS NUMBER ); CREATE TABLE STATSUSER.STATS$USERSESSIONS ( START_DATE DATE, SYSTEM_SESSIONS NUMBER, ACTIVE_USERS NUMBER, INACTIVE_USERS NUMBER, DEDICATED_SERVERS NUMBER, USER_SESSIONS NUMBER, TRANSACTIONS NUMBER ); CREATE INDEX STATSUSER.STATS$USERSESSIONS_STDTE_I ON STATSUSER.STATS$USERSESSIONS (START_DATE); /* Following procedure and shell program is for Solaris */ #!/bin/ksh # This is the…
CREATE TABLE STATSUSER.STATS$TOTALUSERS ( MEASURED_DATE DATE, TOTAL_USERS NUMBER, ACTIVE_USERS NUMBER, UOCWEBSPHERE_USERS NUMBER, UOCSCRIPTS_USERS NUMBER ); CREATE TABLE STATSUSER.STATS$USERSESSIONS ( START_DATE DATE, SYSTEM_SESSIONS NUMBER, ACTIVE_USERS NUMBER, INACTIVE_USERS NUMBER, DEDICATED_SERVERS NUMBER, USER_SESSIONS NUMBER, TRANSACTIONS NUMBER ); CREATE INDEX STATSUSER.STATS$USERSESSIONS_STDTE_I ON STATSUSER.STATS$USERSESSIONS (START_DATE); /* Following procedure and shell program is for Solaris */ CREATE OR REPLACE PROCEDURE STATSUSER.vmstat3…
set lines 132 set pagesize 3000 spool lob_mvmt select ‘alter table ‘ || table_name || ‘ move lob(‘|| column_name || ‘) store as ( tablespace pnet_data ) ;’ from user_tab_columns where data_type like ‘%LOB%’; spool off
In this Document Goal Solution References Applies to: PL/SQL – Version: 8.1.7.4 to 10.1.0.2 Information in this document applies to any platform. Goal This article presents one suggested way of killing a session using an “ALTER SYSTEM KILL SESSION ‘sid,serial#’ ” statement in PL/SQL dynamically where the sid and serial# are values being passed in…
Read More “Kill a session dynanically using execute immediate” »
