Oracle Spin – Flimatech Blog

Sharing Our Database Experience

How to change properties/attributes of partition table?

Posted by Amin Jaffer on June 9, 2012

At times one would want to change properties/attributes of a table so new partitions are affected by change not the partitions already created, like example compression, change PCTFREE, default tablespace.

-- enable compression for all operations and change PCTFREE
SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES COMPRESS FOR ALL OPERATIONS PCTFREE 5;

-- Change default tablespace for new partitions
SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE;

It can be done on partition indexes too.
SQL> ALTER INDEX scott.local_index MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE_IDX;

Posted in General DBA | Tagged: , , , , , | Leave a Comment »

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

Posted by Amin Jaffer on May 28, 2012

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:

Posted in General DBA, trace | Tagged: , , , , | Leave a Comment »

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

Posted by Amin Jaffer on May 27, 2012

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

Posted in General DBA | Tagged: , , , , , | Leave a Comment »

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

Posted by Amin Jaffer on May 13, 2012

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

Posted in General DBA, Tuning | Tagged: , , , , | Leave a Comment »

Example shows how “DISABLE TABLE LOCK” works

Posted by Amin Jaffer on May 13, 2012

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.

Posted in General DBA | Tagged: , , , , | Leave a Comment »

How to trace a running process?

Posted by Amin Jaffer on May 13, 2012

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

Posted in General DBA | Tagged: , , , , , , | Leave a Comment »

ORA-00353: during startup of instance from archiver process

Posted by Amin Jaffer on May 12, 2012

When starting one of the databases we received the ORA-00353 error. It indicates that archiver process couldn’t archive one of the redo logs due to corruption. Oracle tried to read both the logmembers from the same group but they both were corrupted and instance crashes when trying to open it.

ARC0: STARTING ARCH PROCESSES COMPLETE
ARCH: Log corruption near block 88727 change 12629319 time ?
CORRUPTION DETECTED: thread 1 sequence 221 log 2 at block 88727. Arch found corrupt blocks
Errors in file i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_3796.trc (incident=25175):
ORA-00353: log corruption near block 88727 change 12629319 time 05/07/2012 21:13:47
ORA-00312: online log 2 thread 1: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
ORA-00312: online log 2 thread 1: ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’

USER (ospid: 3796): terminating the instance due to error 16038
Instance terminated by USER, pid = 3796

So to confirm the first step was after database was open in mount state to verify both online redo logfiles in the group had corruption.

SQL> ALTER SYSTEM DUMP LOGFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG';
ALTER SYSTEM DUMP LOGFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 88727 change 12629410 time 05/08/2012
09:01:28
ORA-00334: archived log: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’

SQL> ALTER SYSTEM DUMP LOGFILE ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG';
ALTER SYSTEM DUMP LOGFILE ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 88727 change 12629410 time 05/08/2012
09:01:28
ORA-00334: archived log: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’

In this case both files had corruption but note if only of them had corruption you one can copy the file that is no corruption to the corrupted one.

After it was confirmed both files had corruption, using the following steps one can startup the database it will require changing the database to noarchivelog mode so the archiver process doesn’t try to archive the online redo logs. Note: You should take a full backup before you start this step and after this step is done.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 388354048 bytes
Fixed Size 2175968 bytes
Variable Size 331353120 bytes
Database Buffers 50331648 bytes
Redo Buffers 4493312 bytes
Database mounted.

SQL> ALTER DATABASE NOARCHIVELOG;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 388354048 bytes
Fixed Size 2175968 bytes
Variable Size 331353120 bytes
Database Buffers 50331648 bytes
Redo Buffers 4493312 bytes

SQL> alter database mount;
Database altered.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

Posted in General DBA | Tagged: , , , , , | Leave a Comment »

How to see the oldest flashback available?

Posted by Amin Jaffer on April 26, 2012

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

Posted in Flashback | Tagged: , , | Leave a Comment »

How to stop a job scheduled in DBMS_SCHEDULER?

Posted by Amin Jaffer on April 2, 2012

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/

Posted in General DBA, Scheduler | Tagged: , , , | Leave a Comment »

dba_jobs and interval

Posted by Amin Jaffer on March 15, 2012

Few examples of some scenarios of setting intervals for dba_jobs. Note: Updating the interval will not update the NEXT_DATE run of the job until the job runs.

Setting a job to run every monday at 10:00 am
SQL> exec dbms_job.interval(5, 'TRUNC(NEXT_DAY(SYSDATE, ''MONDAY''))+(10/24)');
SQL> commit;

– assume job was run and this SQL was run after it was run
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 19-MAR-2012 10:00:00

Setting a job to run every hour on the 1/2 hour
SQL> exec dbms_job.interval(5, 'TRUNC(SYSDATE, ''HH'')+(1.5/24)');
SQL> commit;

Setting a job to run every hour after the last time the job was started
SQL> exec dbms_job.interval(5, 'SYSDATE+(1/24)');
SQL commit;
— assume job was run and this SQL was run after it was run, the time NEXT_DATE would be a hour after the job was last started.
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 15-MAR-2012 22:45:00

Posted in General DBA, Scheduler | Tagged: , , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 39 other followers