Managing Resumable Space Allocation ...

Use the following to setup resumable statements.

Alter session enable resumable; or Alter session enable resumable timeout <seconds> or Alter session enable resumable timeout <seconds> 'some descriptive name'

… run the sql …

Alter session disable resumable;

 

"This communication is confidential and may contain privileged and/or copyright material. If you are not the intended recipient you must not use, disclose, copy or retain it. If you have received it in error please immediately notify me by return email, delete the emails and destroy any hard copies. ANZ National Bank Limited does not guarantee the integrity of this communication, or that it is free from errors, viruses or interference."

Test blog ...

This is another test blog.

Sam Matthews
Senior Systems Engineer (Database)
Development and Build
ANZ National (NZ) Ltd
 
+64 4 494 4430 (x54430) – mon, fri
+64 4 496 7634 (x47634) – tue, wed, thr

 

"This communication is confidential and may contain privileged and/or copyright material. If you are not the intended recipient you must not use, disclose, copy or retain it. If you have received it in error please immediately notify me by return email, delete the emails and destroy any hard copies. ANZ National Bank Limited does not guarantee the integrity of this communication, or that it is free from errors, viruses or interference."

Certify - Certification Matrix: Oracle Database - Enterprise Edition on Solaris Operating System (SPARC)

Certify - Certification Matrix: Oracle Database - Enterprise Edition on Solaris Operating System (SPARC)

_____

Server Certifications

OS Product Certified With Version Status Addtl. Info. Components Other Install Issue

9 8.1.7 (8i) 64-bit N/A N/A Desupported Yes None N/A N/A
8 8.1.7 (8i) 64-bit N/A N/A Desupported Yes None N/A N/A
2.6 8.1.7 (8i) N/A N/A Desupported Yes None N/A N/A
9 8.1.7 (8i) N/A N/A Desupported Yes None N/A N/A
8 8.1.7 (8i) N/A N/A Desupported Yes None N/A N/A
7 8.1.7 (8i) N/A N/A Desupported Yes None N/A N/A
8 8.1.6 (8i) 64-bit N/A N/A Desupported Yes None N/A N/A
7 8.1.6 (8i) 64-bit N/A N/A Desupported Yes None N/A N/A
2.6 8.1.6 (8i) N/A N/A Desupported Yes None N/A N/A
8 8.1.6 (8i) N/A N/A Desupported Yes None N/A N/A
7 8.1.6 (8i) N/A N/A Desupported Yes None N/A N/A
7 8.1.5 (8i) 64-bit N/A N/A Desupported Yes None N/A N/A
2.6 8.1.5 (8i) N/A N/A Desupported Yes None N/A N/A
8 8.1.5 (8i) N/A N/A Desupported Yes None N/A N/A
7 8.1.5 (8i) N/A N/A Desupported Yes None N/A N/A

db character set

To find the character set that is being used.

SELECT value
FROM sys.props$
WHERE name = 'NLS_CHARACTERSET'

db character set

To find the character set that is being used.
SELECT value$
FROM sys.props$
WHERE name = 'NLS_CHARACTERSET'

Changing owner of discoverer reports

To change the ownership of reports in discoverer try the following.

1. Make sure noone is accessing discoverer.
2. Find the internal discoverer user id
3. run the following ...

update eul5_documents
set doc_eul_id = [new disco user id],
doc_created_by = [db user id],
doc_updated_by = [db user id]
where doc_eu_id [old disco user id]

4. verify by logging into discoverer and checking that ownership of reports has changed.

default attributes

This post is related to tablespaces with regard to partitioned indexes, but can be used with other storage attributes.

When creating local indexes make sure that an index tablespace is specified before you start specifying the individual partitions. For Example:


create index ... on ... tablespace ...
partition ... local ...


To change the default tablespace for an index partition then use the following.


alter index ... default attributes tablespace ...


note: When a local index is created as part of a new partition, then the tablespaces specified above will also be created.

tablespaces and autoextending datafiles

Use the following command to add a a datafile and make that datafile automatically extend upto the maxsize specified.


ALTER TABLESPACE [ tablespace name ]
ADD DATAFILE [ datafile ] SIZE [ size of datafile ]
AUTOEXTEND ON NEXT [ how much to extend the file by ]
MAXSIZE [ size datafile upto this much ]

drop tables and materialized views

When dropping tables and mv's, you need to make sure that there are no foreign keys refrencing the primary keys or unique keys of the tables you are wanting to drop.

Otherwise you will get the following type of errors.


ORA-02449: unique/primary keys in table referenced by foreign keys


With tables, you can use the cascade constraints option, but there appears to be no such option for materialized views.

To drop such mvs, you need to drop the relevent FKs, disabling the FK does nothing.

Use the following query to drop these constraints.

SQL> select 'alter table ' || owner || '.' || table_name || ' drop constraint ' || constraint_name || ';' from dba_constraints where r_constraint_name = [constraint name]

create temporary tablespace ...

The following is the create tablespace for temporary tablespaces.

CREATE TEMPORARY TABLESPACE {tablespace name}
TEMPFILE '{file name}' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE {tablespace name}
/

be carefull with these files as they are sparse. This means that you create a 10g file, but it only uses 128k of actual space. Over time (as the temp tablepsace is used), the actual space used of this file will increase. The problem here is that other files may come along and grab this space so when the temp tablespace wants to use it, it just then runs out of space and reports some non obvious error message.

resize online redo logs

alter database drop logfile group n;

alter database add logfile group n
('...') size nnn reuse;

ensure that the logfile you are dropping is not the current or active.

db block get + consistent gets

Oracle accesses blocks in one of two modes, current or consistent.

A 'db block get' is a current mode get. That is, it's the most up-to-date
copy of the data in that block, as it is right now, or currently. There
can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does
a db block get, and does not take a lock. That is, when it does a full
table scan or fast full index scan, Oracle will read the segment header
in current mode (multiple times, the number varies based on Oracle version).

A 'consistent get' is when Oracle gets the data in a block which is consistent
with a given point in time, or SCN. The consistent get is at the heart of
Oracle's read consistency mechanism. When blocks are fetched in order to
satisfy a query result set, they are fetched in consistent mode. If no
block in the buffer cache is consistent to the correct point in time, Oracle
will (attempt to) reconstruct that block using the information in the rollback
segments. If it fails to do so, that's when a query errors out with the
much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".

As to latching, and how it relates, well, consider that the block buffers
are in the SGA, which is shared memory. To avoid corruption, latches are
used to serialize access to many linked lists and data structures that point
to the buffers as well as the buffers themselves. It is safe to say that
each consistent get introduces serialization to the system, and by tuning
SQL to use more efficient access paths, you can get the same answer to the
same query but do less consistent gets. This not only consumes less CPU,
it also can significantly reduce latching which reduces serialization and
makes your system more scalable.

db block get + consistent gets

Syed,

Oracle accesses blocks in one of two modes, current or consistent.

A 'db block get' is a current mode get. That is, it's the most up-to-date
copy of the data in that block, as it is right now, or currently. There
can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does
a db block get, and does not take a lock. That is, when it does a full
table scan or fast full index scan, Oracle will read the segment header
in current mode (multiple times, the number varies based on Oracle version).

A 'consistent get' is when Oracle gets the data in a block which is consistent
with a given point in time, or SCN. The consistent get is at the heart of
Oracle's read consistency mechanism. When blocks are fetched in order to
satisfy a query result set, they are fetched in consistent mode. If no
block in the buffer cache is consistent to the correct point in time, Oracle
will (attempt to) reconstruct that block using the information in the rollback
segments. If it fails to do so, that's when a query errors out with the
much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".

As to latching, and how it relates, well, consider that the block buffers
are in the SGA, which is shared memory. To avoid corruption, latches are
used to serialize access to many linked lists and data structures that point
to the buffers as well as the buffers themselves. It is safe to say that
each consistent get introduces serialization to the system, and by tuning
SQL to use more efficient access paths, you can get the same answer to the
same query but do less consistent gets. This not only consumes less CPU,
it also can significantly reduce latching which reduces serialization and
makes your system more scalable.

Well, that turned out longer than I planned. If you're still reading,
I hope it helped!

flashback queries

create table t as select * from dba_users;
variable scn number;
exec :scn := dbms_flashback.get_system_change_number;
delete from t;
SQL> delete from t;
43 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t as of scn :scn;
COUNT(*)
----------
43
SQL> flashback table t to scn :scn
2 /
flashback table t to scn :scn
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table t enable row movement;
Table altered.
SQL> flashback table t to scn :scn
2 /
Flashback complete.
SQL> select count(*) from t;
COUNT(*)
----------
43

flashback queries

Use the following example as a guide on using flashback query.

create table t as select * from dba_users;
variable scn number;
exec :scn := dbms_flashback.get_system_change_number;
delete from t;

SQL> delete from t;
43 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(*) from t as of scn :scn;
COUNT(*)
----------
43
SQL> flashback table t to scn :scn
2 /
flashback table t to scn :scn
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table t enable row movement;
Table altered.
SQL> flashback table t to scn :scn
2 /

Flashback complete.

SQL> select count(*) from t;
COUNT(*)
----------
43

vi handy tips

:g/$/s//;/g Stick a ; on the end of every line
:g/^/s//;/g Stick a ; on the start of every line
:1,$s/a/b/g Replace a with b on every line

Tracing ...

TRACING SESSIONS ...

Things you will want to know about tracing

* The timed_statistics init.ora parameter must be set to true in
order to get any meaningful timing data out of tkprof.

* I prefer to set the max_dump_file_size init.ora parameter to either
unlimited or a really large value (2G).
If you're going through the extra work of tracing a process you don't want to
potentially miss the most important part.

-- set identifier in output file
SQL> alter session set tracefile_identifier='mytest';

-- Turn on session level tracing
SQL> alter session set sql_trace=true;

-- Turn on TRACING IN YOUR CURRENT SESSION.
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

-- Turn on some trace level.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

-- SOME DBA STUFF
-- trace another session
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

-- trace another session with some trace level
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');

XPLAN PLAN

Use the following to setup an explain plan table for a database.

I tested this using 10.2.0.2 so the plan table may differ, but theory should be the same from 9i onwards.


drop table plan_table;
create global temporary table plan_table(
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob
) on commit preserve rows;
drop public synonym plan_table;
create public synonym plan_table for plan_table;
grant all on plan_table to public;

create or replace view xplan_basic as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));
drop public synonym xplan_basic;
create public synonym xplan_basic for xplan_basic;
grant all on xplan_basic to public;

create or replace view xplan_typical as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));
drop public synonym xplan_typical;
create public synonym xplan_typical for xplan_typical;
grant all on xplan_typical to public;

create or replace view xplan_parallel as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));
drop public synonym xplan_parallel;
create public synonym xplan_parallel for xplan_parallel;
grant all on xplan_parallel to public;

create or replace view xplan_serial as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));
drop public synonym xplan_serial;
create public synonym xplan_serial for xplan_serial;
grant all on xplan_serial to public;

dynamic sql - dml

The following is some code that will generate some dml via pl/sql. May be useful, although probably not the best way of implementing from a performance issue.

declare
cursor c_users is
select table_name from user_tables where table_name in ('T1','T2','T3');

begin
for a in c_users loop
execute immediate (
'insert into t4 select username from ' ||
a.table_name ||
' where username not in (select username from dba_users)');
commit;

end loop;
end;
/

blocking sessions

select
d.session_id sid,
d.lock_type,
d.mode_requested,
d.mode_held,
d.blocking_others
from
dba_locks d,
v$session v
where
d.session_id = v.sid;