|
QUETZALANDIA | ||
| OracleConfig 101 | |||
|
|||
|
|||
| HOME :: BUSINESS :: COMPUTING :: LIFESTYLE :: PASTIME ::SCIENCES :: | |||
|
|||
|
|||
| |
|||
The oracle_home variable specifies the base directory in which Oracle has been installed.
The oracle_sid variable contains the name of the Oracle instance, usually three or four characters. Examples:
ORACLE_HOME=/apps/oracle ORACLE_SID=DB20
initialization file - initxx.ora DB_NAME = DB40 CONTROL_FILES = (/data/disk50/cfile1.con, /data/disk51/cfile2.con) DB_BLOCK_SIZE = 4096 DB_BLOCK_BUFFERS = 1000 SHARED_POOL_SIZE = 20000000 DML_LOCKS = 200 LOG_BUFFER = 65536 PROCESSES = 100 ROLLBACK_SEGMENTS = (rbs01) LICENSE_MAX_SESSIONS = 100 LICENSE_SESSION_WARNING = 90 LICENSE_MAX_USERS = 100
# To start Server Manager (Unix): svmgrl # Connecting to an Oracle instance, within Server Manager > CONNECT internal/oracle sysdba # Starting up an Oracle instance, without mounting the database STARTUP NOMOUNT PFILE=initXX.ora # Starting an Oracle instance > STARTUP OPEN PFILE=/data/config/initd50.ora # Shutting down an Oracle instance, immediately > SHUTDOWN IMMEDIATE # Shutting down an Oracle instance, and allowing pending # transactions to commit > SHUTDOWN TRANSACTIONAL
In previous versions of Oracle, the "internal" or super-user account was limited to one login / password. With Oracle 8, several IDs can be granted the internal profile's capabilities.
Using O/S password authentication is necessary as internal users may need to get into server manager, and the instance may not be online.
orapwd Usage: orapwd file= password= entries= where file - name of password file (mand), password - password for SYS and INTERNAL (mand), entries - maximum number of distinct DBAs and OPERs (opt), There are no spaces around the equal-to (=) character. GRANT SYSDBA TO scott In the inixx.ora file: REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE /* view accounts using external authentication */ SELECT name, sysdba, sysoper FROM V$PWFILE_USERS
-- Example #1 --
SPOOL output.log
STARTUP NOMOUNT PFILE=initXX.ora
CREATE DATABASE XX MAXLOGFILES 3 MAX
LOGMEMBERS 2 MAXDATAFILES 100
MAXLOGHISTORY 1 MAXINSTANCES 1 LOGFILE
GROUP 1 ('/data/disk1/log1a.rdo','/data/disk2/log1b.rdo') SIZE 10 M,
GROUP 1 ('/data/disk3/log2a.rdo','/data/disk3/log2b.rdo') SIZE 10 M
DATAFILE '/data/disk8/system01.dbf' SIZE 50 M AUTOEXTEND ON,
'/data/disk9/filexx01.dbf' SIZE 1000 M AUTOEXTEND ON,
'/data/disk10/filexx02.dbf' SIZE 1000 M AUTOEXTEND ON,
'/data/disk11/sortxx99.dbf' SIZE 100 M CHARACTER SET WE8ISO8859P1 ;
SPOOL OFF
-- Example #2 --
connect internal/oracle
SPOOL output.log
STARTUP NOMOUNT PFILE=/apps/oracle/admin/DB19/pfile/init.ora
CREATE DATABASE DB19 MAXLOGFILES 4 MAXLOGMEMBERS 2
MAXDATAFILES 100 MAXLOGHISTORY 1 MAXINSTANCES 1 LOGFILE
GROUP 1 ('/data1/oradata/DB19/log1a.rdo','/data1/oradata/DB19/log1b.rdo') SIZE 20 M,
GROUP 2 ('/data1/oradata/DB19/log2a.rdo','/data1/oradata/DB19/log2b.rdo') SIZE 20 M,
GROUP 3 ('/data1/oradata/DB19/log3a.rdo','/data1/oradata/DB19/log3b.rdo') SIZE 20 M,
GROUP 4 ('/data1/oradata/DB19/log4a.rdo','/data1/oradata/DB19/log4b.rdo') SIZE 20 M
DATAFILE '/data1/oradata/DB19/system01.dbf' SIZE 100 M AUTOEXTEND ON,
'/data1/oradata/DB19/system02.dbf' SIZE 100 M AUTOEXTEND ON,
'/data1/oradata/DB19/system03.dbf' SIZE 100 M AUTOEXTEND ON CHARACTER SET WE8ISO8859P1 ;
SPOOL OFF ************************************************** Next Steps:
2) The following SQL scripts should be run, as internal, following the DB creation:
catalog.sql catproc.sql dbmspool.sql catblock.sq catparr.sql pupbld.sql
See oracle/rdbms/admin directory.
3) Creation of temp (sort) tablespace
CREATE TABLESPACE temp01 DATAFILE '/data1/oradata/DB19/temp01.dbf' SIZE 500 M
TEMPORARY ONLINE ;
4) Creation of rollback segments
(See section on RBS for examples)
CREATE TABLESPACE ts01 DATAFILE '/data/disk9/filexx01.dbf' SIZE 1000M MINIMUM EXTENT 500K DEFAULT STORAGE ( INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 ) PERMANENT ONLINE ; CREATE TABLESPACE sort99 DATAFILE '/data/disk11/sortxx99.dbf' SIZE 100M MINIMUM EXTENT 1M DEFAULT STORAGE ( INITIAL 2M NEXT 2M MAXEXTENTS 40 PCTINCREASE 0) TEMPORARY ONLINE ; CREATE TABLESPACE ts01 DATAFILE '/data/disk9/filexx01.dbf' SIZE 1000M MINIMUM EXTENT 500K DEFAULT STORAGE ( INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 ) PERMANENT ONLINE ; /* auto extend */ CREATE TABLESPACE prod_idx_01 DATAFILE '/u3/oradata/prod/prod_idx_01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M MINIMUM EXTENT 320K DEFAULT STORAGE ( INITIAL 320K NEXT 320K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ) PERMANENT ONLINE ;
1) Add a data file ALTER TABLESPACE ts03 ADD DATAFILE '/data1/oradata/ts03-b.dbf' SIZE 200M ; ALTER TABLESPACE ts01 ADD DATAFILE '/data/disk12/dataxx44.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M; 2) or, resize current data file alter database datafile '/u01/oradata/orcl/users01.dbf' resize 50M; 3) or, change the storage / extent configuration ALTER TABLESPACE ts01 DEFAULT STORAGE ( INITIAL 200M NEXT 200M MAXEXTENTS 10 ) ; /* Altering the default storage for a tablespace */ ALTER TABLESPACE TEMP01 DEFAULT STORAGE (PCTINCREASE 0) ALTER TABLESPACE TEMP01 DEFAULT STORAGE ( initial 1M next 1M )
Two rollback segments are created per database. Below are a few handy examples for creating and changing rollback segments.
Note that for some Oracle upgrades, you may need to extend the system rollback segment ( or increase its next extent size ).
/* example 1 */ CREATE ROLLBACK SEGMENT rbs01 TABLESPACE ts22 STORAGE ( INITIAL 100K NEXT 100K OPTIMAL 4M MINEXTENTS 20 MAXEXTENTS 100) ; ALTER ROLLBACK SEGMENT rbs01 ONLINE ; /* example 2 */ CREATE ROLLBACK SEGMENT R05 TABLESPACE rb5 STORAGE ( INITIAL 100K NEXT 100K MAXEXTENTS 300 ) ; ALTER ROLLBACK SEGMENT R05 ONLINE ; Note: Add this line to the initXX.ora file, to ensure the rollback segments are brought online after each server restart: rollback_segments=(r01,r02,r03,r04) Altering a Rollback Segment ALTER ROLLBACK SEGMENT r03 STORAGE ( MAXEXTENTS 250 ); ALTER ROLLBACK SEGMENT r05 SHRINK TO 10M ; /* example3, includes tablespace creates */ connect internal/oracle CREATE TABLESPACE rbs01 DATAFILE '/data1/oradata/DB19/rbs01.dbf' SIZE 100M MINIMUM EXTENT 10K DEFAULT STORAGE ( INITIAL 20K NEXT 500K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ) PERMANENT ONLINE ; CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs01 STORAGE ( INITIAL 100K NEXT 100K OPTIMAL 10M MINEXTENTS 20 MAXEXTENTS 999) ; ALTER ROLLBACK SEGMENT rbs01 ONLINE ; CREATE TABLESPACE rbs02 DATAFILE '/data1/oradata/DB19/rbs02.dbf' SIZE 100M MINIMUM EXTENT 10K DEFAULT STORAGE ( INITIAL 20K NEXT 500K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ) PERMANENT ONLINE ; CREATE ROLLBACK SEGMENT rbs02 TABLESPACE rbs02 STORAGE ( INITIAL 100K NEXT 100K OPTIMAL 10M MINEXTENTS 20 MAXEXTENTS 999) ; ALTER ROLLBACK SEGMENT rbs02 ONLINE ; CREATE TABLESPACE rbs03 DATAFILE '/data1/oradata/DB19/rbs03.dbf' SIZE 100M MINIMUM EXTENT 10K DEFAULT STORAGE ( INITIAL 20K NEXT 500K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ) PERMANENT ONLINE ; CREATE ROLLBACK SEGMENT rbs03 TABLESPACE rbs03 STORAGE ( INITIAL 100K NEXT 100K OPTIMAL 10M MINEXTENTS 20 MAXEXTENTS 999) ; ALTER ROLLBACK SEGMENT rbs03 ONLINE ;
ALTER DATABASE
ADD LOGFILE
('/data/disk77/log3a.rdo', '/data/disk78/log3b.rdo')
SIZE 500K;
ALTER DATABASE ADD LOGFILE MEMBER
'/data/disk77/log3a.rdo' TO GROUP 1,
'/data/disk78/log3b.rdo' TO GROUP 2;
ALTER DATABASE
ADD LOGFILE
'/data2/oradata/MARSH/redo14.log'
SIZE 10M;
select group#,thread#,archived,status from v$log ;
select * from v$logfile ;
ALTER SYSTEM SWITCH LOGFILE ;
ALTER DATABASE DROP LOGFILE '/data/OraHome1/oradata/TEST/redo01.log'
ALTER DATABASE DROP LOGFILE '/data/OraHome1/oradata/TEST/redo02.log'
CREATE TABLE
INVOICE (
INV_ID NUMBER(4),
INV_DATE DATE)
PCTFREE 20
PCTUSED 50
STORAGE (
INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE ts01
LOGGING ;
CREATE TABLE documents
( doc_id INTEGER
doc_text BLOB)
STORAGE (INITIAL 256 NEXT 256)
LOB (doc_text) STORE AS
(TABLESPACE ts04
STORAGE (INITIAL 6144 NEXT 6144)
CHUNK 4
NOCACHE NOLOGGING
INDEX (TABLESPACE ts05
STORAGE (INITIAL 256 NEXT 256)
)
);
/* index-organized table, similar to Sybase's clustered index construct */
CREATE TABLE postal_customer
( zip char(5),
addr numeric(5),
dir char(1),
street varchar2(20),
last_name varchar2(20),
first_name varchar2(20),
item_ct numeric(3)
)
CONSTRAINT pk_postal PRIMARY KEY (zip,addr,dir,street)
ORGANIZATION INDEX
TABLESPACE ts05
PCTTHRESHOLD 25
OVERFLOW TABLESPACE ts06 ;
/* cloning a table without data */
create table city2 as select * from city where 1=2 ;
create table brand2 as select * from brand where 1=2 ;
alter table employee move
STORAGE (
INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
TABLESPACE PROD_DATA_04 ;
# Alter table: modifying a column
ALTER TABLE employee MODIFY (last_name varchar2(35));
ALTER TABLESPACE ts1 BEGIN BACKUP;
<< backup files, within O/S >>
ALTER TABLESPACE ts1 END BACKUP;
/* control file backup */
ALTER DATABASE BACKUP CONTROLFILE TO 'filename' REUSE;
/* prepare for offline tablespace backup */
ALTER SYSTEM ARCHIVE LOG CURRENT;
/* take tablespace offline */
ALTER TABLESPACE users OFFLINE NORMAL;
/* run backup for datafiles */
/* bring tablespace online */
ALTER TABLESPACE users ONLINE;
/* create profile */
CREATE PROFILE pr01
SESSIONS_PER_USER 1
IDLE_TIME 30
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_VERIFY_FUNCTION SYSUTILS.VERIF_PWD ;
PROFILE = name of the profile
SESSIONS_PER_USER = limits a user to integer concurrent sessions
CPU_PER_SESSION = limits the CPU time for a session, in hundredth of seconds
CPU_PER_CALL = limits the CPU time for a call
CONNECT_TIME = limits the total elapsed time of a session, in minutes
IDLE_TIME = limits periods of continuous inactive time during a session, in minutes
LOGICAL_READS_PER_SESSION = number of data blocks read in a session
LOGICAL_READS_PER_CALL = number of data blocks read for a call to process a SQL stmt
FAILED_LOGIN_ATTEMPTS = number of failed attempts to log in, before locking acct
PASSWORD_LIFE_TIME = limits the number of days the same password can be used
PASSWORD_REUSE_TIME = number of days before which a password cannot be reused
PASSWORD_REUSE_MAX = number of password changes required
PASSWORD_LOCK_TIME = number of days an account will be locked
PASSWORD_VERIFY_FUNCTION = a PL/SQL password complexity verification script
DEFAULT = omits a limit for this resource in this profile
COMPOSITE_LIMIT = specifies the total resources cost for a session, in service units
UNLIMITED = a user assigned this profile can use an unlimited amount of this resource
/* create user */
CREATE USER scott
IDENTIFIED BY tiger
DEFAULT TABLESPACE ts01
QUOTA 500M ON ts02
PASSWORD EXPIRE
ACCOUNT UNLOCK
PROFILE pr01 ;
/* user pwd change */
ALTER USER scott
IDENTIFIED BY lion ;
/* add tablespace */
ALTER USER scott
QUOTA 100M ON ts29 ;
/* user drop */
DROP user scott ;
The import and export utilities are used to move data from one instance to another, or one schema to another.
The utility basically re-reruns all the DDL, in order, to recreate and fill the tables.
/* Export sample call */
exp username/password feedback=100 file=filename.dat log=export.log
exp help=y
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
ROWS export data rows (Y) PARFILE parameter filename
CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
DIRECT direct path (N) TRIGGERS export triggers (Y)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
QUERY select clause used to export a subset of a table
VOLSIZE number of bytes to write to each tape volume
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport
/* Import sample calls */
imp username/password feedback=100 file=filename.dat log=import.log
/* import a single table, table exists already */
imp admin23/portal feedback=100 file=company3.dat log=company.log tables='(company)' ignore=Y
imp help=y
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE output file (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output
DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
CHARSET character set of export file (NLS_LANG)
POINT_IN_TIME_RECOVER Tablespace Point-in-time Recovery (N)
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
ANALYZE execute ANALYZE statements in dump file (Y)
FEEDBACK display progress every x rows(0)
Bonus Script
#====================================================================
#!/usr/bin/ksh
#
# exp2imp: export Oracle table for import to secondary schema
#
# Parms: user1 table user2
#
#
#====================================================================
dbauser=oradba/dba@prod
tname=$2
user1=$1
user2=$3
tparm2="tables='$tname'"
tparm1="tables='$user1.$tname'"
echo "====================="
echo "Exporting $tparm1 ..."
exp $dbauser \
feedback=100 \
file=$tname.dat \
$tparm1 \
direct=Y \
log=/tmp/export.log
echo "====================="
sleep 5
echo "Importing $tparm2 ..."
imp $dbauser \
fromuser=$user1 \
touser=$user2 \
feedback=100 \
file=$tname.dat \
log=/tmp/import.log \
$tparm2 \
indexes=N \
ignore=Y
echo "========================================================="
echo `date` Done.
echo "========================================================="
Use this high performance utility for table-based imports. Drop the table's indexes before the load, and rebuild them afterwards. Drawback with this utility: you'll need to build a control file for each table, not a trivial process.
# command sample: uses "direct path" which bypasses redo
sqlldr userid=scott/tiger data=/apps/temp/customer.dat control=customer.ctl direct=true
# list parameters
sqlldr help=Y
userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name
data Data file name
discard Discard file name
discardmax Number of discards to allow
skip Number of logical records to skip
load Number of logical records to load
errors Number of errors to allow
rows Number of rows in conventional path bind array or between direct path data saves
bindsize Size of conventional path bind array in bytes
silent Suppress messages during run (header,feedback,errors,discards,partitions)
direct use direct path
_synchro internal testing
parfile parameter file: name of file that contains parameter specifications
parallel do parallel load
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions
skip_index_maintenance do not maintain indexes, mark affected indexes as unusable
commit_discontinued commit loaded rows when load is discontinued
_display_exitcode Display exit code for SQL*Loader execution
readsize Size of Read buffer
Using SQL Loader
1) create a control file:
LOAD DATA
INFILE 'places.txt'
INTO TABLE places
INSERT
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
(
loc_id,
name,
city,
phone
)
2) make sure the destination table is empty
3) run the command, as the oracle user:
sqlldr userid=user99/tiger control=places.ctl direct=true skip_index_maintenance=true readsize=131070
This command will run SQL loader, using 'direct path', and skip index block updates -
the indexes will need to be rebuilt later, along with the analyze command.
Direct path - skips constraints, along with redo - writes are direct, no commit.
Sample control, importing Sybase dates
LOAD DATA
INFILE 'company.dat'
INTO TABLE company
INSERT
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
(
COMPANY_ID ,
COUNTRY_ID ,
INDUSTRY_ID ,
INSERT_DT date "mon dd yyyy hh:miam" terminated by X'09',
MOD_DT date "mon dd yyyy hh:miam" terminated by X'09',
ENDING_EFF_DT date "mon dd yyyy hh:miam" terminated by X'09',
TRADE_COUNTRY_ID ,
COMPANY_NAME ,
DATA_ID )
Exporting to a delimited file
Note: blob/long raw/long field types are not exportable using this technique !
Option 1: Select as file format
set feedback off
set pages 0
spool file2.dat
select col_a||','||col_b||','||to_char(col_c) from tablename ;
spool off
Option 2: Use CTL file maker and SQL Loader
Install the three scripts detailed below, then run as follows:
(sample calls)
# export all tables, to ctl files
exp2ctl scott/tiger /export/home/dba/temp
cd /export/home/dba/temp
# import using sql loader, to different schema ( or instance! )
ctl2ora steve/lion
'sqlldr_exp' script from Oracle site (author: T.Kyte).
This script is called from the following two scripts.
Note: left justify all commands if using paste function !
#!/usr/bin/ksh
# Script: sqlldr_exp
# export single oracle table to a ctl file,
# for use with sqlldr
if [ "$1" = "" ]
then
cat << EOF
usage:sqlldr_exp un/pw [tables|views]
example: sqlldr_exp scott/tiger mytable
description: Select over standard out all rows of table or view with
columns delimited by pipes.
EOF
exit
fi
PW=$1
tname=$2
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $X
prompt prompt TRUNCATE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('$X')
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF > $tname.ctl
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done
Bonus Script I: Create control files for each table
in a schema.
#!/usr/bin/ksh
#==========================================
# Script: exp2ctl
# Export all tables to control files,
# for sqlldr import
#
# Parms: userid/pwd
# output directory
#
# ** Check location of sqlldr_exp script, modify call as needed (expcmd)
#==========================================
orauser=$1
dir1=$2
if ! test -d $dir1 ; then
echo "Directory not found: " $dir1
echo " "
echo " "
echo " "
exit
fi
shfile=$dir1/ctlmake.sh
awkfile=$dir1/ctlmake.awk
tfile=$dir1/tnames.dat
expcmd=sqlldr_exp
echo "Using schema ................" $orauser
echo "Using output directory ......" $dir1
sleep 2
echo "Creating table list ..."
sqlplus -s $orauser @user_tables.sql | egrep "[A-Z]" > $tfile
sleep 2
echo "Creating script ..."
echo "cd $dir1" > $shfile
echo "set -x" >> $shfile
echo "{print \"$expcmd\" \" $orauser \" \$1 }" > $awkfile
cat $dir1/tnames.dat | awk -f $dir1/ctlmake.awk >> $shfile
chmod 700 $shfile
sleep 2
cat $shfile
sleep 3
echo "Running ctl make ..."
$shfile
Bonus Script II: Run in all ctl files, in the current directory
( file must end in .ctl )
#!/bin/ksh
#-----------------------------
# Script: ctl2ora
# Control File Processor
# for SQL Loader
# Parms: userid/pwd
#-----------------------------
if test $# -lt 1 ; then
echo " "
echo "usage: "
echo $0 ' userid/pwd'
echo " "
exit
fi
for fname in *.ctl ; do
echo "sqlldr: " $fname
if test -f $fname ; then
sqlldr userid=$1 control=$fname direct=true readsize=131070
## Faster version!
## sqlldr userid=$1 control=$fname direct=true skip_index_maintenance=true readsize=131070
fi
done
Using PL-SQL to Export Data to a File
--
-- This procedure exports 2 columns from a table
-- to a double pipe delimited file.
--
-- Note: long raw / long data fields are not exportable using this technique
create or replace procedure proc_exp_cad (rcount in integer) is
count1 integer default 0 ;
file1 utl_file.file_type;
company_id1 integer;
industry_id1 integer;
line1 varchar2(1000);
cursor cur1 is
select company_id, industry_id
from company_annual_data2 ;
begin
file1 := utl_file.fopen('/apps/temp','cad.out','w',32000);
open cur1;
loop
count1 := count1 + 1 ;
fetch cur1 into company_id1, industry_id1 ;
exit when cur1%NOTFOUND ;
exit when count1 > rcount ;
line1 := to_char(company_id1) || '||' ||
to_char(industry_id1) ;
utl_file.put_line(file1,line1);
end loop ;
utl_file.fclose(file1);
end ;
/
call proc_exp_cad(30)
/
show errors
/
The DB Verify utiltity is the equivalent of Sybase's DBCC utility. It basically goes through a given data file and checks the consistency, verifying the pointers are all proper.
This should be run on a regular basis, to find potential disk problems. If it is not, the end users will likely be the first to notice any problems which may materialize.
# run example cd /apps/oracle/bin ./dbv /data2/ts23.dbf
/* startup in restricted mode */ STARTUP RESTRICT /* restrict an instance that is already running */ ALTER SYSTEM ENABLE RESTRICTED SESSION
Clearing the redo log files is usually the first remedy used in repairing a damaged instance ;
it's the equivalent of dropping and re-adding each file.
SELECT * FROM v$log ; ALTER DATABASE CLEAR LOGFILE 'filename' ; /* if archive log mode is true, may need to force the clear */ ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'filename' ; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 ;
1) take the tablespace offline 2) move the file, using mv command 3) ALTER TABLESPACE ts01 RENAME DATAFILE '/data/ts01.dbf' TO '/data5/ts01.dbf' ; 4) bring the tablespace online
ALTER TABLESPACE ts01 OFFLINE IMMEDIATE ; DROP TABLESPACE ts01 INCLUDING CONTENTS ; /* then, delete data files using O/S commands */
/* unique index, use a low PCTFREE value for ID columns */
CREATE UNIQUE INDEX schema2.idx_invoice
ON schema2.invoice ( inv_id )
PCTFREE 10
STORAGE ( INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
TABLESPACE idx01 ;
/* non-unique index ;
note the NOLOGGING parm, which is recommended for large indexes
also note the NOSORT option, which is good for large tables where the
data has been loaded in sorted order.
*/
CREATE UNIQUE INDEX schema2.idx_customer
ON schema2.customer ( stat_cd, name )
PCTFREE 40
STORAGE ( INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
NOLOGGING
NOSORT
TABLESPACE idx01 ;
/* index extent increase */
ALTER INDEX schema2.idx_customer
STORAGE ( NEXT 300K ) ;
/* indexes should be rebuilt, when there are many deleted entries */
ALTER INDEX schema2.idx_customer
REBUILD
TABLESPACE idx02 ;
alter index PK_EMPLOYEE rebuild compute statistics nologging ;
/* update internal stats on the index, for the optimizer ...
also check for corruption */
ANALYZE INDEX schema2.idx_customer VALIDATE STRUCTURE ;
/* disable a constraint */
ALTER TABLE EMPLOYEE DISABLE CONSTRAINT FK_DEPT ;
/* add a constraint */
alter table REPORT_LIST
add constraint PK_REPORT_LIST primary key(REPORT_ID)
using index tablespace prod_idx_01;
create tablespace ts99 datafile '/data4/ts99.dat'
size 200m default storage ( initial 100k next 100k pctincrease 0) ;
create user user99 identified by pwd99
default tablespace ts99
temporary tablespace temp
quota unlimited on ts99 ;
grant create table,resource,connect to r99 ;
grant dba to r99 ;
In the init.ora file, add:
LOG_ARCHIVE_DEST = /data5/archive
LOG_ARCHIVE_FORMAT = arch%s.arc
LOG_ARCHIVE_MAX_PROCESSES=2
Shut down the Oracle instance.
In server manager:
> connect
> startup mount
> alter database archivelog
> alter database open
> shutdown immediate
Run a complete cold backup - this sets the starting point
if a restore is needed.
Oracle may be a hard concept for some to follow, but if you return to nature and discover the beauty of the trees and forests then you will experience a spiritual tempo that will far exceed PL/SQL.
When we talk of a database in general, we talk about the entire set of processes (SMON, PMON, DBWR, LGWR), memory space (SGA) and files associated with the database. Another commonly used term for an Oracle database is an Oracle 'instance'. The files associated with an Oracle database are the initialization files, control files, online redo log files and data files.