transportable tablespaces

References: metalink note:77523.1

Refer to the relevant database documentation, correct version for restrictions when using this feature.

note:

a Ensure the following scripts have been run in the database. These would normally be run at database creation time.

$ORACLE_HOME/rdbms/admin/catplug.sql
$ORACLE_HOME/rdbms/admin/dbmsplts.sql
$ORACLE_HOME/rdbms/admin/prvtplts.plb

sql> desc dbms_plugts

b If running this procedure by a user other than sys then ensure the user has dba privileges and PUBLIC SYNONYMS for the following following have been created.

DBMS_PLUGTS
DBMS_TTS


1. Identify tablespaces that need to be migrated. note: Dependent data objects need to be incapsuated in the process. i.e.: Data + Index objects need to be included.

2. Put relevent tablespaces into readonly mode.

sql> alter tablespace ... readonly;

3. Export metadata from tablesspaces
unix> exp userid/password file=... tablespaces=... transport_tablespaces=y

note: multiple tablespaces need to be delimited by a comma.

4. Copy datafiles that make up the tablespaces identified above into the target environment.

5. Copy the export dat file created in step 3.

6. Import metadata into target database.

unix> imp ... tablespaces=... datafiles=... transport_tablespaces=y
note: tablespaces and datafiles need to be seperated by comma's.

7. Verify tablespaces are updated correctly by querying relevent views, dba_tablespaces and v$datafile.

8. Put tablespaces into read write mode in source and target databases.
sql> alter tablespace ... read write;

rank

RANK and its mates can be useful functions to use when you need to order a list of records.

For example, I needed to transform a database from OMF (oracle managed files) to OSF (oracle standard files). As you may know, OMF bear no resemblance to the datafile standard most corporates seem to run, a typical omf data file might look like o1_mf_ts_ofsaa_2gt2g1hr_.dbf, while a osf might look like user_01.dbf

Being a good dba I wanted to script and automate the CREATE CONTROLFILE statement. My soltion to the above problem was to use the rank function.

So something like -

select tablespace_name, file_name, rank() over (PARTITION BY tablespace_name order by file_id) "id"

solved my problem and produced an output something like -

tablespace1 somefile1 1
tablespace1 somefile2 2
tablespace2 somefile1 1
...

I was then able to use the above statement to generate the datafile section of the create controlfile file.

EXIT from PL/SQL procedure block

A simple method to EXIT from a PL/SQL block is to use the RETURN keyword. You should also be able to return an EXIT status.

So if we run something like -

1 BEGIN
2 dbms_output.put_line('one.');
3 return;
4 dbms_output.put_line('two.');
5* end;
n990538@edrtst> /
one.

PL/SQL procedure successfully completed.

instr, substr

A couple of usefull oracle functions for manipulating strings.

instr(string1, string2, start position, nth appearance) returns the position where the string exists.

where
string1 is the original string to search from
string2 is the string to search in string 1
start posititon is optional and is the start position to start searching from
nth appearance is optional and is the number of occurances of string2 in string1.

instr('Tech on the net', 'e', 1, 3) would return 14.

substr(string1, start position, length) returns a string.

where
string1 is the string to check.
start position is the position in the string to start the substring from
length is optional and the length of the position.

substr('Tech on the net',1,5) would return 'Tech '

Often using a combination of the above functions is useful.

substr('Tech on the net',1, instr('Tech on the net', 'e', 1, 3)) would return ''Tech on the ne'

bug - 2942857 - OERI:12327 from complex view merging

Bug 2942857 - OERI:12327 from complex view merging
DOC ID 2942857.8

Fixed: 9.2.05, 10.1.0.2

Complex view merging may fail with ORA-600[12327] if an COUNT(*) is used and the query has a subquery predicate against the COUNT(*) .

Workaround: Set "_complex_view_merging"=false

Affects EDR, EDMT, DWH.

sed & awk script - find num SID entries in oratab

sed and awk command to
- remove blank lines from the start of each line
- Find entries based on input parameter - SID.
- find the number of entries.

cat oratab sed -e 's/^[ \t]*//' awk -F: '/^'"$SID"'/ { print $1 }' wc -l

truss

Truss is a command that enables you to trace a unix program. The truss output will show the libraries, executables, log files etc the command is touching including the return code.

Use the following commands -
truss -ae -o truss.txt -p 123456
truss -f -o truss.txt abc

where

truss.txt is the output file.
123456 is the process id that the truss will run on.
abc is the program that truss will run on.

SGA

SGA is made up of the following -

Fixed - Small and always constant depending on platform, os and database version
Variable (large pool, shared pool, java pool)
Database Buffer
Redo Log Buffer

10G
Oracle introduced memory management. Basically you set the following parameters and oracle manages the memory for you. The theory is the database will adjust the memory components as on a as required basis.

sga_taget <> 0
statistics_level = TYPICAL

zfs parameters

Parameters -

Show Parameters

zpool get
zfs get

There are many parameters that can be used, some which may prove useful are:

compression: Turn compress on at the file system level. Could be useful for staging area.

acl

ACL - Fine grain permissions -

Scenario 1:

databases like oracle, can create files etc on the database server. The os account that starts the database needs to have write permission on the directory structure. The ability that the database has should be conducted as part of the application and therefore it will depend on where these application directories are located. Best practice would suggest that these files should be available and even owned by a application owner.

Use ACL's to resolve this issue. In a nutshell, the owner and group of the file generated by the database is owned by an application owner and group. Give the oracle user or an oracle group read/write/execute permission on the underlying directory. In unix, ensusure setid for group is set. This ensures that dependent files adopt the permissions of the directory.

unix> chmod g+s dirname
unix> chmod A+group:grpname:read_data/write_data/execute:allow dirname

where grpname is the name of the group that is owned by the database owner.

When dealing with a hierarchical file structure, all directories up to parent should be included.

Warning 1:

if you chmod group permissions, then ACL's may be changed.

ls

ls -V - shows acl attributes for a file.

TNS Basics

If you are getting TNS-03505 when using tnsping or sqlplus then check the following -

sqlnet.ora - NAMES.DEFAULT_DOMAIN = world

then ensure your entry in the tnsnames.ora file has either a .world fixed to the ens of the database alias or there is an extention.

i.e. -

RISST01P.WORLD = ...

SQLPLUS Reports

Use some of the following guidelines when writing SQLPLUS Reports

Compute - use perform computations on groups of data. Types of computiations include AVG, MAX, MIN, STD, SUM, VAR.

break on tablespace_name skip 2
compute sum label "SIZE (M) " of size on tablespace_name

use multiple columns to perform multiple computionation and grand computations etc.

datapump

Use the following as a guide when using datapump to extract data and load data into a 10 or above oracle database.

1. create datapump directory and give read, write privilege to required users.

2. Use the following examples to unload and load data from an oracle database.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Refer to the following link for more details.

http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

MAX_DUMP_FILE_SIZE

Limit the size of alert log files with this parameter.

Parameter can be changed at system or session level.
i.e. - alter system or alter session

Options are UNLIMITED or a number with size attributes, i.e.: - K, M, G.

To make life easier set this parameter to UNLIMITED.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams129.htm

Defaut Attributes

To change default attributes for partitioned tables and indexes - so tablespace or pctfree is set correctly for new partitions, use the following -

alter table owner . table modify default attributes attribute ...

Subpartitions are slightly different, try -

alter index "owner" . "index" modify default attributes for partition "partition name" ...

where attribute can be -
pctfree, tablespace, pctused etc

scp

Use the following for using scp:

Assumptions - ssh to server has been setup correctly - see note on setting up ssh.

scp -prvqC files user@host target directory

options:
-p --> preserve time stamps
-r --> copy subsdirectories
-v --> verbose mode
-q --> quiet - don't include progress counter.
-C --> compress. Note: When running in compress mode, scp runs at a slower rate.

java thin client

JAVA Thin Client is used by some development / reporting tools to connect to multiple databases.

One such example is dbvisualizer.

The thin driver is useful as it allows users to connect to oracle databases without the requirement of any oracle client software to be installed on desktop / client etc.

Steps:

1. Download the oracle thin driver - http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/winsoft.html

2. Copy into software location or application may load load (and copy to correct location. DBVisualizer does this for you).

3. Create a connection string, something like - jdbc:oracle:thin:@servername:dbport:dbname

Once done, you should be able to connect.

DB Links

Database Links cabn be created as PUBLIC or PRIVATE.

create public database link "dblink name" connect to "target db user" identified by "target db user password" using 'sid';

create database link "dblink name" connect to "target db user" identified by "target db user password" using 'sid';

DB Links use Oracle Networking from the installed oracle home.

Public Database Links will be used before private DB Links.

usage:

select * from dual@dblink

Password File

Create Password File.
orapwd file=password_file_name password=the_secret_password
orapwd file=password_file_name password=the_secret_password entries=n

Add users to password files:
grant sysdba to "user"
grant sysoper to "user"

View users who have SYSDBA or SYSOPER priv:
select * from v$pwfile_users;

Connect:
sqlplus ""user" as sysdba"

note:
in 9.2, connection info shows connection as sys.
in 8.1.x, connection info shows connection as self.

remove db parameter from spfile

Required when obsolete db parameter needs to be removed or reset from the spfile.

alter system reset "parameter name" scope=spfile sid='*'

clustering factor

Clustering Factor shows how synchronized the indexes are. Clustering Factor is updated after running dbms_stats on a particular index.

A good clustering factor is when the clustering factor is near the number of blocks in the index.
A bad clustering factor is when the clustering factor is near the number of distinct rows in the index.

So for each key,
If the child records are found in the same data block then access will be quicker. Good clustering factor.

If the child records are found in may data blocks then access will be slower. bad clustering factor.

tracing

More information on tracing.

Use dbms_system to trace sessions ...

Turn on level 12 tracing
exec sys.dbms_system.set_ev(sid, serial#, 10046, 12,'');

Turn off tracing
exec sys.dbms_system.set_ev(sid, serial#, 10046, 0, null);

To analyze with tkprof
tkprof

sys=yes sort='(prsela, exeela, fchela)'

where sys=yes, means data dictionary queries are included.

External Tables

Some useful information on directories and external tables

External tables rely on database directories, so firstly create a database directory if required.

create or replace directory ... as '...'
note: don't use parameters such as $HOME (as oracle can't work out what these are).

grant privileges to the directory created above:

grant read on directory ... to ...;

Next, create a table (see below for an example) and then start querying the external table.

CREATE TABLE ... ( ... some columns ...) ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY ...
ACCESS PARAMETERS
(
records delimited by newline
fields REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'add_partition.cfg'
)
) REJECT LIMIT unlimited

ssh-UNIX

To setup ssh auto connect between servers, run the following:

on from-server run:
cd $HOME/.ssh
ssh-keygen -t dsa

This will generate two files:
- id_dsa
- id_dsa.pub

copy the contents of id_dsa.pub to the target server

append the authorized_keys file on the target server with the contents of the generated id_dsa.pub. i.e.: cat id_dsa.pub >> authorized_keys

example:

ssh user@server ls -l

should run with no passwords being asked for.

user can be ignored if the user you are wanting to ssh to is the same as the user running the ssh command, obviously on different servers.

server should accept DNS entries.

Problems -

If ssh does not noto connect then check the permissions on the $HOME directory.
It should be 750 or drwxr-x---

explorer hints / tips

Use the following to make better use out of explorer.

start at the downloads folder - /e, u:\downloads

NOT NULL Check Constrailts

Check constraints will stop particular sets of data from been added to a column in a table. One type of check constraints is the NOT NULL keyword when adding a column or creating a table.

note: With the NOT NULL keyword a system generated identifier is used so the name of the check constraint will be different through the SDLC (assuming entire physical database refresh is not completed.)

drop check constraint:
alter table t1 drop constraint c1;

convert scn numbers

The scn number is very important. Whenever a change to the database is made (including the data dictionary) then this scn number is incremented. Oracle uses the scn number for backup / recovery purposes, flashback, recycle bin etc etc etc.

There are a few functions that can be used to convert scn numbers to dates and vice a versa, which may make life easier when working out what date these scn numbers refer to. These functions must look through control file as they will fail if the scn number is too old.

select dbms_flashback.get_system_change_number from dual;
select current_scn from v$database;

select scn_to_timestamp(to_char(timestamp_to_scn(sysdate),'99999999999')) from dual;
select to_char(timestamp_to_scn(sysdate),'99999999999') from dual

note:
These queries will convert the scn number to a timestamp and vice a versa.

recycle bin

The concept of the recycle bin is essentially the same as the recycle bin on the mac or windows.

if you drop a table, the table is not renamed and not dropped. You can query the recyclebin view to look at what objects are currently in the recycle bin. You can actually select from the table that was dropped, although it is quite an ungamely name. i.e.: Something like BIN$HGnc55/7rRPgQPeM/qQoRw==$0

You can restore the table by issuing:
flashback table "bla" to before drop;

scattered read and sequential read

Scattered Read - normally attributable to full table scans. These wait events occur through full table scans (FTS) as FTS normally scan multiple blocks at the same time, re: multi_block_read_count.

Sequential Read - read 1 block at a time are normally attributable to index scans. As index scans read 1 block at a time, multi block read doesn't apply with indexes and performance with sequetial reads could go down.

If any of these events are high then ensure stats are accurate, explain plans look OK and there isn't too much disk contention going on.

note: The amount of blocks that an index has to scan through before completing could have a big impact on performance. This also has implications for the cluster factor.