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;

savepoints

Savepoints work something like this ...

dml a
savepoint a;
dml b
rollback to savepoint a;
commit;
dml a will be commited, but not dml b.

Unsure when a good time to use it, but it does work a treat.

truncate partitions

Use the following to truncate specific partitions

alter table ... truncate partition ...

add new partitions

Use the following to add new date partitions ...

alter table ... add partition ... values less than (to_date(...))
...

outer joins

An outer join is essentially a join that grabs all intersecting records from two tables and all the records from one of joining tables.

select ... from t1 a, t2 b
where a.col1 = b.col1(+);

This will grab all intersecting records as well as all records from t1.

execute immediate exceptions

Use the following to generate some exceptions for execute immediate

SQL> BEGIN
EXECUTE IMMEDIATE 'garbage';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK );
RAISE;
END;


where the dbms_output statement can be used instead of some logging procedure.

The output would be something like ...

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

adding quotes in dynamic sql

Use the following to add a quote when writing dynamic sql. Useful when you can't remember ascii codes.

SELECT 'select ... '|| ' where username like ''A%'' ;'

The output looks something like the following ...
select ... where username like '%A' ;

hiding CODE

Use the following to hide pl/sql code in the database.

wrap iname="sql in file" oname="sql out file"
compile in the database.

Works an absolute trick !!!

database upgrade

Below are some steps when upgrading / patching a database:

1. Install SOFTWARE in new OH
2. BACKUP database
3. SHUTDOWN DB
4. Update ORATAB for database so that it is pointing to new OH.
5. RUN oraenv for DB and check that env parameters are pointing to the new OH.
6. COPY network and database parameter files are copied to new OH.
7. RUN PREUpgrade Steps
8. RUN UPGRADE STEPS
9. RUN POST UPGRADE STEPS (backup configuration, monitoring, etc)
10. BACKUP Database.

XPLAN PLANS Made Easy

Found this on David Aldridge's blog ... http://oraclesponge.wordpress.com

he DBMS_XPLAN supplied package gives results that are so much more usable than the old "set autotrace traceonly explain", or any manual method of reading the plan_table, that the only thing holding me back from using it more often was the lengthy syntax.

No more: I have created views to save my aching fingers. Feel free to use them, modify them, whatever for your own purposes -- just don't blame me if they break something ;)

create or replace view xplan_basic as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));*

create or replace view xplan_typical as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));

create or replace view xplan_parallel as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));

create or replace view xplan_serial as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));

I am now a mere "select * from xplan_parallel" away from my execution plans.

Thanks David.

wireless keyboard

Brought a wireless keyboard and mouse a few days ago. It works a treat. On the mac it asked me to configure before I logged in and away it went.

To my delight, most of the special keys on the keyboard work on the mac. pretty good work from microsoft. The only problem is that it is black !!!

Keyboards

Purchased a new keyboard and mouse (of the wireless variety) today. My lovely wife spilt water over the old keyboard and now it doesn't work. boo hoo.

As I have a mac at home, the keyboard was a mac. Seems to me as though apple design some really nice hardware, but ofter they don't think about the practacality of things (like building a keyboard that still works after some water is thrown on it).

snapshot too old errors ...

There is a bug with oracle 9i and materialized views whereby there are numerous snapshot too old errors. This bug is around the source database whereby the optimizer is giving an incorrect execution plan, when the parameter star_transformation_enabled is set to TRUE.

This bug is fixed in oracle 10.0. The workaround is to change the parameter to temp_disable.

Direct Path Write wait

The 'direct path write' wait seems to occur when the disk the database is writing to can't keep up with requests. It is normally an indicator that there is something amiss with the disk, or possibly the DR sync is working as expected.

SIZING Tables

TABLES

To work out the sizings of x number of records then need to be added to a table, use the following ...

Analyze the table or partition. Find the avg_row_len, from either dba_tables or tab_tab_partitions. Then calculate the space required by ...

space_required = avg_row_len * number_of_rows

This formula is really simple and is not perfect as it doesn't take block headers, pct_free etc into consideration, but should be good as a guide.

ORACLE LINKS

metalink
tahiti

db link passwords

To work out what database links are using what passwords, run the following sql.

select name,ctime,host,userid,password from sys.KU$_DBLINK_VIEW;

This should work upto 10G.

STORAGE

Storage in an oracle database goes something like this ...

An oracle database is made up of one (two from 10g) or tablespaces. Each tablespace can have one or more datafiles. A datafile can be a file or pointer to a raw device or use ASM (automatic storage management).

When creating a tablespace you can specify locally managed or dictionary managed. Local managed should mean quicker dictionary response as all the info relating to extents held in the tablespaces etc are held in the tablespace and not the data dictionary.

When creating locally managed tablespaces, you have the option of setting the extents to uniform or automatic. I always think uniform is the best then you can control what segments go into what tablespace. This is probably quite old school, but there you go.

undo created at db startup

It appears undo at database startup is dependent on

- transactions_per_rollback_segment
- max_sessions

oracle (9.2.0 at least) uses the following formula to work out how many undo segments it uses when the undo tablespace is created.

max_rollback_segments = (max_sessions * 1.1) / transactions_per_rollback_segment

oracle will always have at least this number. We had the problem that max_sessions was changed and the undo tablespace was not recreated. Consequently the database was only ever creating what the old session level was set at.

We were also getting some weird errors around ORA-1555, snapshot too old errors when we kick a number of jobs off at once and they fail immediately with ORA-1555 errors.

We are in the process of making this change to production so time will tell !!!

UNDO SQL

Below is some sql I used to get an idea about how undo is being managed in an oracle database.

Firstly some simple stuff ...

Look at the segments in the UNDO tablespace.

select segment_name, sum(bytes)/(1024*1024)
from dba_extents
where tablespace_name = 'tablespace_name'
group by segment_name
order by segment_name;

LOOK at current rollback segments, by status, either ONLINE or OFFLINE.

select status, count(*) from dba_rollback_segs
where owner = 'PUBLIC' group by status;

MERGE the above two statements together ... This can take a minute or so to run, but is quite useful as it shows the total number of segments that can be used. Bearing in mind that the database will try and add some more.

select a.segment_name, b.status, sum(a.bytes)/(1024*1024)
from dba_extents a, dba_rollback_segs b
where a.segment_name = b.segment_name AND b.owner = 'PUBLIC'
group by a.segment_name, b.status;

UNDO, Some Discussion

OK, below are issues relating to the undo problems with oracle 9.2.0.3.

Seems as though there are a few bugs. The problem we seem to have is when we do either materialized view refreshes and truncate table type commands.

The problem seems to be that oracle can not create enough undo segments. When running these again, often straight after the failure it works. This generally seems to be the case.

The way undo seems to normally work is that it creates segments when required. There are particular rules around when it grabs new extents, how much space it grabs etc. The smon process is responsible for dropping undo segments that are not required any more. It generally does this every 12 hours. There is an event to stop this from happenning.

Welcome

Hi,

This is my first ever BLOG !!! WOW!!!

Quick introduction about this BLOG. Its all about me, my work life, my home life and other bits and tads.

enjoy !!!