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