Oct 31

Script to generate tablespace creation DDL

SET SERVEROUTPUT ON
SET LINESIZE 100
SET VERIFY OFF
SET FEEDBACK OFF
SPOOL create_tbs.sql
PROMPT

DECLARE

CURSOR cu_ts IS
SELECT *
FROM   dba_tablespaces a
WHERE  a.tablespace_name  NOT IN (‘SYSTEM’,’TEMP’,’UNDO’,’USERS’)
ORDER BY a.tablespace_name;

CURSOR cu_df (p_tablespace  IN  VARCHAR2) IS
SELECT *
FROM   dba_data_files a
WHERE  a.tablespace_name = p_tablespace
ORDER BY a.file_name;

BEGIN

DBMS_Output.Disable;
DBMS_Output.Enable(1000000);

FOR cur_ts IN cu_ts LOOP
DBMS_Output.Put_Line(‘PROMPT’);
DBMS_Output.Put_Line(‘PROMPT Creating Tablespace ‘ || cur_ts.tablespace_name);
DBMS_Output.Put_Line(‘CREATE TABLESPACE ‘ || Lower(cur_ts.tablespace_name));
DBMS_Output.Put_Line(‘DATAFILE’);
FOR cur_df IN cu_df (p_tablespace => cur_ts.tablespace_name) LOOP
IF cu_df%ROWCOUNT != 1 THEN
DBMS_Output.Put_Line(‘,’);
END IF;
DBMS_Output.Put(‘   ”’ || cur_df.file_name || ”’ SIZE ‘ || Trunc(cur_df.bytes/1024) || ‘K’);
END LOOP;
DBMS_Output.Put_Line(‘ ‘);
DBMS_Output.Put_Line(‘AUTOEXTEND ON’);
DBMS_Output.Put_Line(‘NEXT 104857600  MAXSIZE 3200m’);
DBMS_Output.Put_Line(cur_ts.status);
DBMS_Output.Put_Line(cur_ts.contents);
DBMS_Output.Put_Line(‘EXTENT MANAGEMENT LOCAL’);
DBMS_Output.Put_Line(‘SEGMENT SPACE MANAGEMENT AUTO’);
DBMS_Output.Put_Line(‘/’);
DBMS_Output.Put_Line(‘  ‘);
END LOOP;

DBMS_Output.Put_Line(‘      ‘);

END;
/

SET VERIFY ON
SET FEEDBACK ON
spool off

Oct 31

RMAN backup shell script

#!/bin/ksh

export ORACLE_HOME=/u01/app/oracle/product/10.2.0.4
export ORACLE_SID=$1

export VDATE=$(date +”%m-%d-%Y_%H-%M-%S”)
export BACKUP_LOG_DIR=/u99/dba/scripts/backup/log
export BACKUP_LOG_FILE=${BACKUP_LOG_DIR}/${ORACLE_SID}_rman_${VDATE}.log

export DBA=monitor@xxxx.com

$ORACLE_HOME/bin/rman target / log ${BACKUP_LOG_FILE} << EOF
connect catalog rman/rman@RMANPRD
RUN {
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
delete noprompt obsolete;
}
EXIT;
EOF

if [ -f ${BACKUP_LOG_FILE} ]; then
egrep ‘(ERROR|error|Error|RMAN-)’ ${BACKUP_LOG_FILE} > /dev/null
if [ $? = 0 ]; then
RESULT_MSG=”WARNING: Errors occurred during the ${ORACLE_SID} Rman backup full.”
mail -s “$RESULT_MSG” $DBA < ${BACKUP_LOG_FILE}
else
egrep ‘(Recovery Manager complete)’ ${BACKUP_LOG_FILE} > /dev/null
if [ $? = 0 ]; then
RESULT_MSG=”${ORACLE_SID} RMAN Backup Full Completed Successfully.”
mail -s “$RESULT_MSG” $DBA < ${BACKUP_LOG_FILE}
else
RESULT_MSG=”WARNING: ${ORACLE_SID} RMAN Backup full  did not complete.”
echo Backup process was terminated. | mail -s “$RESULT_MSG” $DBA
fi
fi
fi

##mail -s “Oracle ${ORACLE_SID} backup completed” monitor@xxxx.com < ${BACKUP_LOG_FILE}

May 28

How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.

SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

-- connect to session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc

Contents of the tracefile:

*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1′
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1′ console output:

May 28

How to see current utilization of processes/sessions and max utilization?

Using the following SQL one can find the current number of processes and sessions connected and also max utilization so one can check if you need to increase the values of the parameter

SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in (‘processes’,’sessions’);

Output:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
—————————— ——————- —————
processes 146 196
sessions 157 210

May 13

Example of using sql profile to use switch to a different execution plan

Below are the 2 SQL statements, the first one uses the index AAA_IDX and then second one does a full table scan and in this case we want to make the first one that uses the index use the same execution plan as the 2nd one.

SQL statement:
select * from aaa e where ename = 'aaa';
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

By query v$sql we found the sql_id, child_number and plan_hash_value also checked there isn't a sql_profile attached to the SQL.

SQL> select sql_id, child_number, sql_profile, plan_hash_value, sql_text from v$sql where sql_id IN ('63cg18v928540', '0tjtg6yqqbbxk');

SQL_ID CHILD_NUMBER SQL_PROFILE PLAN_HASH_VALUE
————- ———— ————————————————- —————
SQL_TEXT
————————————————-
63cg18v928540 0 2022030255
select * from aaa e where ename = 'aaa'

0tjtg6yqqbbxk 0 864433273
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

Displaying the execution plan of the 2nd SQL with the outline option
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0tjtg6yqqbbxk', 0, 'outline'));
PLAN_TABLE_OUTPUT
——————————————————————————–

SQL_ID 0tjtg6yqqbbxk, child number 0
————————————-
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

Plan hash value: 864433273

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “E”@”SEL$1″)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ENAME”='aaa')

From the outline generated used the hint to add to first SQL to change its execution plan i.e. full table scan.
define SQL_ID = '63cg18v928540';

DECLARE
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM V$sqlarea where sql_id = '&SQL_ID';
dbms_sqltune.import_sql_profile(sql_text => clsql_text,
profile=> sqlprof_attr('FULL(@SEL$1 E@SEL$1)'),
name=>'PROFILE_&SQL_ID',
force_match=>true);
end;
/

Shows now when we execute the SQL it uses the same execution plan as the 2nd one i.e. full table scan
SQL> select * from aaa e where ename = 'aaa';
Execution Plan
———————————————————-
Plan hash value: 864433273

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ENAME”='aaa')

Note
—–
– SQL profile “PROFILE_63cg18v928540″ used for this statement

May 13

Example shows how “DISABLE TABLE LOCK” works

Following example shows what happens when locks are disabled on the TABLE.

-- disable lock on a table
SQL> alter table t disable table lock;
Table altered.

-- shows one can’t drop table as table locks are disable
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- shows one can’t truncate table as table locks are disable
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- DML is okay to run
SQL> delete from t;
1 row deleted.

-- new columns can be added
SQL> alter table t add tt varchar2(1);
Table altered.

-- columns can’t be modified
SQL> alter table t modify tt varchar2(10);
alter table t modify tt varchar2(10)
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- columns can’t be dropped
SQL> alter table t drop column tt;
alter table t drop column tt
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- enable locking on the table
SQL> alter table t enable table lock;
Table altered.

-- table now can be dropped
SQL> drop table t;
Table dropped.

May 13

How to trace a running process?

Using strace one can trace the system calls being executed by a running process. To stop the strace press control-C

To display the system calls being executed
$ strace -p <pid>

or

To display a summary of system calls being executed
$ strace -cfo <logfile> -p <pid>

Example: In this example smon process was being traced
$ strace -p 1234
Process 1234 attached – interrupt to quit
getrusage(RUSAGE_SELF, (ru….
getrusage(RUSAGE_SELF, (ru….
Pressed <control-C>

$ strace -cfo smon_strace.log -p 1234
Process 1234 attached – interrupt to quit
Process 1234 detached
Pressed <control-C> to detach.

$ cat smon_strace.log
% time seconds usecs/call calls errors syscall
—— ———– ———– ——— ——— —————-
nan 0.000000 0 22 getrusage
—— ———– ———– ——— ——— —————-
100.00 0.000000 22 total

May 12

How to see the oldest flashback available?

Using the following query one can see the flashback data available.

SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;

CURRENT_TIME OLDEST_FLASHBACK HIST_MIN
—————- —————- ———-
2012-04-25 07:34 2012-04-25 05:48 106.066667

May 12

How to stop a job scheduled in DBMS_SCHEDULER?

Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job.

SQL> exec sys.dbms_scheduler.STOP_JOB(job_name=>'SYS.ORA$AT_OS_OPT_SY_12856', force=>true);

Output:
PL/SQL procedure successfully completed.

If it can’t find the job then you may see the following error, in the example below I hadn’t specified the user to it was looking for the job as the current user.
ORA-27475: “DBAUSER.ORA$AT_OS_OPT_SY_12856″ must be a job
ORA-06512: at “SYS.DBMS_ISCHED”, line 199
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 557
ORA-06512: at line 1

To see jobs running http://oraclespin.wordpress.com/2010/05/08/dba_scheduler_running_jobs/