Home → oracle → New Features → Archive by category "10g"

Category Archives: 10g

/ 10g
# 1104
SEP, 5 2007

new explain plan options (dbms_xplan)

I still need to upgrade to 10g …

I still tend to use this old fashioned ‘set autot trace exp’

[REMI@DB01.REMIDIAN.COM]
SQL> set autotrace trace exp
[REMI@DB01.REMIDIAN.COM]
SQL> select * from dual;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes
          =2)

While in 10g the explain plan is pretty awseome – dbms_xplan package

[REMI@DB01.REMIDIAN.COM]
SQL> explain plan for select * from dual;
 
Explained.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3543395131
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |

/ 10g
# 1544
AUG, 30 2006

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

/ 10g
1
# 1877
AUG, 30 2006

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

/ 10g
# 1522
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  /

remidian