• Home Page
• Utilities
• App Server
• Auditing/Tracing
• Background Procs
• Datafile Add
• Functions
• Import/Export
• Indexes
• IPCS
• Locks
• NT
• oraenv
• Parameters
• Procedures
• Process Size
• Redo
• Retrieving a log file
• Rollbacks
• RMAN
• Security
• Sorts
• Sqlnet
• SQL Tools
• Tables

	MetaLink
Oracle Education	Oracle Technology Network
Oracle Support	Certification
Prometric

Oracle Documentation - 8i

Other Oracle Links

Oracle Contacts

Oracle Switchboard:	01189 240000
Oracle Customer Relations (CD Orders/Upgrades):	0870 5622300

Metalink Notes

To update your account with multiple SAC codes:

    uk_metalink_uk@oracle.com

198160.1 - Summary of technical articles relating to LOBS
169547.1 - Understanding and obtaining Oracle RDBMS patchsets

Application Server

OAS Installation

The default boot port is on 2649. The boot port is the
port where the Oracle Request Broker listens for
connection requests.

Web listener listens on port 80 by default and is called www

The multicast IP is randomly generated on the range given
above, you just have to check it does not clash with an
existing one.

The format for connecting to the node manager is:

    http://hostname.domain:port_number

You have in effect 5 ports in action:

• Node Manager Port - Default is 8888
• Admin Listener Port - Default is 8889
• Boot Port - Default 2649
• Multicast IP address and Port - 224.0.0.0 to 224.0.0.255
• Web listener - Default Port 80

To bounce the cartridges:

    owsctl stop -c all

    owsctl reload -w all

Auditing/Tracing/Accounts

Run the Script cataudit.sql or catnoaudit.sql to undo the tables

The table that will get populated is SYS.AUD$

Step 2:

Issue the audit command. e.g.

    audit delete any table by access whenever not successful;
    audit session by scott, lori;
    audit session;
    audit delete on emp;
    noaudit session;

Step 3:

Set the AUDIT_TRAIL parameter to 'TRUE'

Step 4:

Remember to manage the sys.aud$ table by clearing out
unwanted data.

Sample Queries:

    select * from sys.dba_stmt_audit_opts;
    select * from sys.dba_priv_audit_opts;
    select * from sys.dba_obj__audit_opts;
    select * from all_def_audit_opts;

Tracing

    'immediate trace name ERRORSTACK level 10';

Accounts

To unlock an account:

    ALTER USER username ACCOUNT UNLOCK;

Background Processes

QMN - use for the Oracle Advanced Queuing (up to 10)

RECO - for resolving pending transactions in a parallel server environment.

SNPn - use when creating snapshots (up to 36)

Database Startup

Think of mounting the database as linking the
background processes, SGA with the physical database.

SGA

You can lock the SGA in real memory with the LOCK_SGA
and LOCK_SGA_AREAS - I think this would be pretty
anti-social on the other processes running on the server

You can set the start size of the SGA with:
    SHARED_MEMORY_ADDRESS
    HI_SHARED_MEMORY_ADDRESS

DBLINK

create public database link cimlink connect to read_only identified by read_only using 'cimlink';

The 'using' clause requires a valid TNSNAMES alias. Below is the extract from the tnsnames.ora file:

cimlink =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= lehp01)(Port= 1521))
(CONNECT_DATA = (SID = dc_p0)) )

To drop a database link:

    drop public database link ;

Import / Export

Cross platform exp/imp via a pipe

A simple Import via a pipe

mknod ./small.compresspipe p uncompress -c /warehse/NEXUS/PGAS2/PGAS2.dmp.Z> ./small.compresspipe & (imp parfile=./import_pgas_small.par \ file=./small.compresspipe \ log=./small.log \ 1>./small.out)

Datafiles/Tablespaces

Offline/Read Only Tablespaces

When you take a tablespace off-line it creates a deferred
rollback segment in the system tablespace (I didn't see this)

You can drop a table in a read only tablespace!

Would datafiles for a read-only tablespace be updated with the current SCN? I would think not

Datafile Add

Datafile Extend

alter database datafile '/p18/oradata/dc_p0/dcindex14_02.dbf'
resize 1000M ;

Functions

nvl(o.name,n.name) name (If the first value is null,
use the second.

Indexes

2 null values in a column does not violate a unique index.

Think of bitmap indexing for large tables that are fairly
static. Each bit position relates to a rowid. This helps the
identification of a row, and is very useful when you retrieve
based on multiple conditions in the where clause.

Btree indexes store the keyed data-values as well as the rowid.
Therefore you could have an index bigger than the table!

If you think about the structure of a btree, a bitmap index it
is excellent for a column with low cardinality.

Bitmap indexes include nulls.

Although you can end up duplicating a great deal of
information in an index, and therefore the idea of just
having an index-organised table might seem like a good
idea. You do have to watch out for restrictions, dependent
on the version of Oracle you are using.
You can't add secondary indexes, you have to use
the primary key, you can't impose a unique constraint etc.

Rebuild

Alter index <> rebuild;

Validate Structure

Alter index validate structure; (This will give you index_stats information)

Sizing

5 elements:

Header Size
Data Space per Block
Combined column lengths
Avg index value size
Blocks & Bytes required per index

Header Size:

Fixed Header - 113 Bytes
Variable 24 * Initrans (default 2)
Default therefore 113 + ( 24 * 2) = 161

Free Space:

Note: If you are dealing with a read only database the pctfree figure will 0.

Each column has an overhead of 1 byte if < 250 bytes. Else the overhead is 3 bytes.

Rowsize = (sum of columns) + row header (3 * ( select type_size from v$type_size where component = 'UB1') );

Space used per row = max (ub1 * 3 + UB4 + SB2, rowsize ) + SB2)

Create Index Example

IPCS

To see Oracle shared memory segments:

    $ ipcs -bm | grep oracle

To see which shared memory segments a database has allocated:

    svrmgrl> oradebug ipc

To remove a shared memory segment:

    $ ipcrm <id>

To list semaphores:

    ipcs -sa

Rollbacks

9i

Parameter: UNDO_MANAGEMENT = AUTO/MANUAL
>    create undo tablespace UNDO1
>    datafile '/ora/ora901/oradata/V901/undo01.dbf' size 10M;

undo_retention

Default = 900secs. If you want a flashback query, you must
have a 'undo_retention' time set to allow for it. If you need to flashback 12 hours, you need undo retention set to 43200.

    alter system set undo_retention = 10

Locks

From the excellent Note: 102925.1

When access is required by a session, a lock structure is obtained and a request is made to acquire access to the resource at a specific level (mode)is made. The lock structure is placed on one of three linked lists which hang off of the resource, called the:

• OWNER (if the enqueue could be acquired)
  
• WAITER (if the session is waiting to acquiring the enqueue)
  
• CONVERTER (the session is holding the enqueue in one level but wants to convert it to another) lists.

Types of Enqueue:

• JQ - Job Queue
• ST - Space Management. (e.g. Allocating / De-allocating extents.
• TM - Table level lock.
• TX - Transaction lock. Each Rollback segment has a transaction table. This has a slot number and a sequence number.
• UL - User lock. User takes an explicit lock using dbms_lock.request.

Setting ENQUEUE_RESOURCES:

About estimating the number of enqueues required, there is no formula documented anywhere on this. Some have said number of tables + 40%, but we do also hold an enqueue for each datafile. Also, each lob column of a table would require 2 add'l enqueues: one for the lob data segment, and one for the lob index.

NT/2000

oradim -new -sid jove -intpwd **** -startmode auto -pfile c:\oradata\ora81\dbs\initJOVE.ora

oraenv

Parameters

Setting traces files to be viewable by public:

    _trace_files_public = true

Procedures

#!/bin/ksh
export ORACLE_SID=GMED
sqlplus system/cvr4ak <run.log
exec dbms_utility.analyze_schema( 'PMSPAD', 'ESTIMATE',null, 20 , null);
exit;
EOF

Redo

Security

Cerberos - a central security management system

If you have multiple dba's get them to use their
own accounts so that you can see who is creating files etc.

Use a separate admin account, in the dba group
perhaps, that is externally authenticated that can run standard admin
scripts.

ORAPWD

Dropping a User

Alter system kill session '<sid>,<serial>'

Privileges

Revoking object grants cascades. System grants don’t.

IMP_FULL_DATABASE is a powerful role. Create any table.

Can you specify a column level privlege to a role?

Sorts

2 Types:

DISK - Heavy IO. Order of magnitude slower than memory sorts..

MEMORY - Quickest

TEMPORARY_TABLESPACE - These are done in a user's
Temporary tablespace. The default is SYSTEM. If you
don't change this you could run out of space in the
system tablespace which can hang the database and will
lead to fragmentation in the tablespace.

If a sort exceeds SORT_AREA_SIZE it will require a
disk sort.

SORT_AREA_RETAINED_SIZE - The idea behind it is
that the initial memory allocated in the UGAS will
be SORT_AREA_RETAINED_SIZE. If your sort needs more
space then it will expand to SORT_AREA_SIZE. Once the
sort is done it will shrink back to SORT_AREA_RETAINED_SIZE.

SORT_DIRECT_WRITES - If set to TRUE, each sort
allocates additional buffers in memory for direct writes
to disk avoiding the buffer cache. Can speed things up
by a factor of 6. How much will it allocate?

To enable this you must make the SORT_AREA_SIZE 10
times the size of DB_BLOCK_SIZE#

SORT_BUFFER_SIZE - Set as 32768 bytes - increase to 65536 to improve speed

SORT_WRITE_BUFFERS - Default is 2 - set up to 8 to
speed up process

SORT_READ_FAC - Set at 20 - Number of blocks read from disk in a single operation for a sort. Set larger for larger sorts.

SORT_SPACEMAP_SIZE - Used for generating a disk map used to map disk areas
used for sorting. 512 bytes. Only need to increase it
for really big sorts - e.g. index builds.

Query

Select name,value from v$sysstat where name like '%sort%';

SQL TOOLS

Define_editor=vi

To select a ' character:

select '''' from dual;

To echo the sql command before it is run:

set echo on

To Split a heading across lines:

col s for a1 head 'S|T'

To Split a heading across lines:

To find duplicate keys from a table tx:     select key, count(key) no_of_duplicates
     from tx
     group by key
     having count(key) > 1;

To remove duplicates from table tx when IT DOES NOT MATTER WHICH ONE IS SAVED:

    delete from tx
     where key not in
     select min(key) from tx group by key);

How to show the time difference between two date columns?

select floor((date1-date2)*24*60*60)/3600) || ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' timediff
from ...

To Display a Long field:

    set long 4000
    set array 1

Ascii

Global Name

    alter database rename global_name to TST1;

Queries as tables

    select
    g.group#,g.members,x.mx,m.member
    from v$log g, v$logfile m,
    (select ma(members) mx from v$log) x
    where g.members >< x.mx
    and g.group# = m.group#

Sqlnet

Dead Connection Detection

To setup dead connect detection enter the following:

    sqlnet.expire_time = 10

It may take longer than you think for sessions to be cleared up. This is because TCP itself will try and resend the server probe to the client. This is setup in:

    /usr/include/netinet/tcp_timer.h

Tables