remidian

Can't find what you're looking for? Use of one of the search websites below …

Home → Archive by category "oracle" (Page 2)

Category Archives: oracle

delete large number of records with intermediate commits

Have to delete a large number of records and running out of undo tablespace ‘ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDO’? Script below does a delete with intermediate commits ever ‘x’ records. 

declare
 
  -- commit every 'i_commit' rows
  i_commit   pls_integer := 1000;
 
  -- row counter, 'i_rowcount' rows deleted
  i_rowcount pls_integer := 0;
 
begin
 
  -- define 'infinite' loop
  loop
 
    -- delete 'i_commit' rows
    delete from obj
    where rownum <= i_commit;
 
    i_rowcount := i_rowcount + sql%rowcount;
 
    -- now it's time to exit the loop
    if sql%rowcount = 0 then
       commit;
       exit;
    end if;
 
    commit;
 
  end

  • / 10g
  • # 2570
  • AUG, 30 2006

Invalidated synonyms

In Oracle 10G synonyms are marked invalid when you perform DDL on the referenced object. Luckily the synonym is marked valid again when you simply touch the invalidated synonym. The status of the synonym becomes valid again and the DBA_OBJECTS.LAST_DDL_TIME and DBA_OBJECTS.TIMESTAMP columns reflect the time the synonym was validated again. Listing below shows an example for a create or replace of a view with an associated synonym. 

[REMI@DB01.REMIDIAN.COM]
SQL> create view v_demo as select * from dual;
 
View created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> create synonym s_demo for remi.v_demo;
 
Synonym created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select object_name, object_type, status
  2    from all_objects
  3   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME                      OBJECT_TYPE    STATUS
-------------------------------- -------------- ---------------
S_DEMO

overcome SP2-0734 when using purge command

Issue new Oracle 10G ‘purge‘ command results in ‘SP2-0734: unknown command beginning “purge user…” – rest of line ignored.’

Purge Command in pre 10G SQL*Plus release

Whenever you want to issue the ‘purge’ command in any pre 10G SQL*Plus client you will notice that SQL*Plus denies to send your command to the server and it will raise an ‘SP2-0734′ error. You can easiliy overcome this by making use of dynamic sql (or just upgrade your client of course). 

[REMI@DB01.REMIDIAN.COM]
SQL> create table demo(k int);
 
Table created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> drop table demo;
 
Table dropped.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BIN$HE/mKBNyRajgRAADuhSWrA==$0 TABLE
 
[REMI@DB01.REMIDIAN.COM]
SQL> 
[REMI@DB01.REMIDIAN.COM]
SQL> drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0";
drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0"
           *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
 
[REMI@DB01.REMIDIAN.COM]
SQL> purge user_recyclebin;
SP2-0734: unknown

To trace or not to trace

Oracle Trace reference

g-se-tr.sql

Script below enables trace in another user session. It will list all three possible ways to achive this, through a) SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, b) SYS.DBMS_SYSTEM.SET_EV and c) ORADEBUG. 

col set_trace for a136 heading "ENABLE TRACE IN ANY SESSION"
 
select
 'EXEC sys.dbms_system.set_bool_param_in_session -
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''timed_statistics'', bval => true);' 
 || chr&#40;10) ||
 'EXEC sys.dbms_system.set_int_param_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''max_dump_file_size'', intval  => 2147483647);' 
 || chr&#40;10) || chr&#40;10) ||
 'EXEC sys.dbms_support.start_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ', waits => true, binds => true);' 
 || chr&#40;10) ||
 'EXEC sys.dbms_support.stop_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ');' 
 || chr&#40;10) ||

  • / 10g
  • # 1644
  • JUL, 2 2006

Automatic Workload Repository

A very short howto for the ‘Automatic Workload Repository’, the successor of perfstat.

Interval

Some queries;

Listing below shows one snapshot is taken every 20 minutes, the snapshot is stored for a week (7 days). 

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /
 
SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 00:20:00.0                +00007 00:00:00.0

Alter the interval to once every hour and store it for a month (31 days); 

[SYSTEM@DB01.REMIDIAN.COM]
SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings
  3        ( interval   => 60
  4        , retention  => 31 * 24 * 60);
  5  end;
  6  /

dba$log_blocker

Code below will save the information of the ‘blocking session’ in an ORA-00060 situation.

Since both sessions are equally guilty of the deadlock Oracle picks one session at random to get the ORA-00060, the update will fail for this session and a trace file is written to the user_dump_dest. In this trace file you can find the current SQL statement of the session that got the ORA-00060 and the os pids of the sessions on the server, however there is not as much information as to the session that ’caused the deadlock’ as you might want to see.

The ‘servererror’ triggers (around since 8i), enable you to write a trigger and storing some usefull information for later analysis.

Please note the triggers firing on the ‘servererror’ start a separate transaction and commits it after firing the trigger. No need to start an autonomous transaction, since it already is so to say, see