oracle学习笔记

oracle学习笔记

SQL*PLUS
############################################
TNS_ADMIN : tnsname.ora path
LD_LIBRARY_PATH : libraries on Unix and Linux
SQLPATH : SQL scripts path


sqlplus [[options] [logon] [start]]
sqlplus -H
save 命令
install help : @?/sqlplus/admin/help/*.sql  (要使用system用户，非sysdba权限登录)
SQL> conn system
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql
desc table_name or plsql_producre_name
list
del
c
REMARK  #SQL PLUS注释 
--      #SQL注释
注释的限制
start @ @@  (echo  termout)
SQLPATH
sqlplus username/pass @script.sql
spool
help
替代变量 & &&
define
accept
prompt


############################################




SQL语句跟踪 : From Client To SQL
############################################
commit
rollback
savepoint
rollback to <savepoint>
set transaction


netstat -nap
netstat -nlatp
v$transaction
v$session
v$process
v$sql


v$session.saddr=v$transcation.ses_addr
v$session.paddr=v$porcess.addr
v$session.sql_address=v$sql.address (prev_sql_addr上次执行的sql)
v$process.spid = netstat
v$sql.sql_text= sql text
############################################




oracle 9i dba fundamentals I
############################################
--Architectural Components
-------------------------
ipcs  #查看ipc资源
granule : sga分配的最小单位 （sga<128M granule=4M, sga>128M granule=16M）
select component, granule_size from v$sga_dynamic_components;
SHARED_POOL_SIZE
DB_CACHE_SIZE
LOG_BUFFER
LARGE_POOL_SIZE
JAVA_POOL_SIZE
STREAMS_POOL_SIZE (11G)
ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
ALTER SYSTEM SET DB_CACHE_SIZE = 96M;
DB_BLOCK_SIZE 块尺寸
DB_CACHE_SIZE : DB_CACHE_ADVICE set to gather statistics for predicting different cache size behavior, statistics displayed by V$DB_CACHE_ADVICE
backgroup process : PMON SMON DBWn LGWR CKPT ARCn Others
-------------------------


--Getting Started with Oracle Server
-------------------------
Oracle Universal Installer
Database Configuration Assistant
Database Upgrade Assistant
Oracle Net Manager
Oracle Enterprise Manager
SQL*PLUS
Recovery Manager
Oracle Secure Backup
Data Pump
SQL*Loader
Non-Interactive Installation
./runInstaller -silent -responseFile responsefile.rsp
dbca 是sh脚本
-------------------------


--Managing an Oracle Instance
-------------------------
Initialization Parameter Files
v$parameter
show parameter XXX
SID: Site Identifier
pfile spfile
db_name=ORACLE_SID
db_block_size=8192
control_files = ('control01.ctl' , ' ' , ' ')
instance startup or not startup : create spfile from pfile;
strings spfileORCL.ora
alter system set parameter=value <comment='text'> <deferred> <scope=memory|spfile|both> <sid='sid|*'>
alter system reset undo_suppress_errors scope=both sid='*';
deferred : 针对不能立即生效的参数，下次启动生效  v$parameter.issys_modifiable = deferred
startup pfile = $ORACLE_HOME/dbs/initORCL.ora
initORCL.ora : spfile=$ORACLE_HOME/dbs/spfileORCL.ora
SHUTDOWN -> NOMOUNT (Instance started) -> MOUNT (Control file opened for this instance) -> OPEN (All files opened)


STARTUP [FORCE] [RESTRICT] [NOMOUNT] [MIGRATE] [QUIET]
[PFILE=<file_name>]
[MOUNT [EXCLUSIVE] <database_name>x ]
OPEN <READ {ONLY | WRITE [RECOVER]} | RECOVER>
<database_name>


retricted mode
STARTUP RESTRICT
ALTER SYSTEM ENABLE RESTRICTED SESSION;


ALTER SYSTEM KILL SESSION 'SID,SERIAL#';


Diagnostic Files
alertSID.log      #可以通过修改重新构造参数文件(字符串加单引号)
Background trace files : SID_processname_PID.trc
User trace files : SID_ora_PID.trc
BACKGROUND_DUMP_DEST
USER_DUMP_DEST \   MAX_DUMP_FILE_SIZE
show parameter dump


Enable/Disable User Tracing
Session level:
 ALTER SESSION SET SQL_TRACE = TRUE;
  or dbms_system.SET_SQL_TRACE_IN_SESSION
Instance level
 SQL_TRACE = TRUE
-------------------------


--Creating a Database
-------------------------
DBCA & manually(CREATE DATABASE)
Use Oracle Data Migration Assistant to migrate from an earlier version of the database
Authentication Methods : Use OS authentication  & Use a password file
orapwd file=$ORACLE_HOME/dbs/orapwORCL password=oracle entries=10
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
GRANT SYSDBA TO HR;
ORA_NLS33($ORACLE_HOME/ocommon/nls/admin/data) \ LD_LIBRARY_PATH


create database
Choose a unique instance and database name
Choose a database character set
Set operating system variables
Create the initialization parameter file
Create password file
Create directory
Create a Server Parameter File
Start the instance in NOMOUNT stage
Create and execute the CREATE DATABASE command
Run scripts to generate the data dictionary and accomplish post-creation steps
Create additional tablespaces as needed


create database bbk;
$vi crtdb.sql
----------------
spool dbcreate.log;


CREATE DATABASE "bbk"
  CONTROLFILE REUSE
  MAXDATAFILES 500
  MAXINSTANCES 8
  MAXLOGFILES 32
  CHARACTER SET "UTF8"
  NATIONAL CHARACTER SET AL16UTF16
  ARCHIVELOG
  DATAFILE
    '/u01/oradata/bbk/system01.dbf' size 300M
  EXTENT MANAGEMENT LOCAL
  DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/bbk/tempts01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL  --11gR2注释此行，oracle自动创建temptablespace
  UNDO TABLESPACE "UNDOTBS1"
  DATAFILE '/u01/oradata/bbk/undotbs01.dbf' SIZE 200M
  LOGFILE
    GROUP 1 ('/u01/oradata/bbk/redo01.log') SIZE 100M,
    GROUP 2 ('/u01/oradata/bbk/redo02.log') SIZE 100M,
    GROUP 3 ('/u01/oradata/bbk/redo03.log') SIZE 100M;


spool off
----------------


Build Data Dictionary Views
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql


catalog.sql : Creates the views of the data dictionary tables, the dynamic performance views,and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
catproc.sql : Run all scripts required for or used with PL/SQL.
pupbld.sql :  Required for SQL*PLUS. Enables SQL*PLUS to disable commands by user.


OMF
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n


-------------------------




--Using Data Dictionary and Dynamic Performance Views
-------------------------
Data dictionary
Performance tables
PL/SQL packages
Database event triggers


DD Views contains two parts:
Base tables : created with CREATE DATABASE  (sql.bsq)
Data dictionary view : created with the catalog.sql script


DBA_XXX  ALL_XXX USER_XXX


General overview : DICTIONARY , DICT_COLUMNS
Schema objects : DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS, DBA_CONSTRAINTS
Space allocation : DBA_SEGMENTS, DBA_EXTENTS
Database structure : DBA_TABLESPACES, DBA_DATA_FILES


Dynamic Performance Tables (Information is accessed from memory and controlfile)
V$XXX  (X$XXX)
V$FIXED_TABLE


V$CONTROLFILE  V$DATABASE  V$DATAFILE  V$INSTANCE  V$PARAMETER
V$SESSION  V$SGA  V$SPPARAMETER  V$TABLESPACE  V$THREAD  V$VERSION


Admin Script Scripts
cat*.sql    Catalog and data dictionary information
dbms*.sql   Database package specifications
prvt.plb    Wrapped database package code
utl*.sql    Views and tables for database utilities
-------------------------




--Maintaining the Control File
-------------------------
The main determinants of the size of a control file are the values set for the
MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY and MAXINSTANCES
parameters in the CREATE DATABASE statement that created the associated database.


A control file contains the following entries:
Database name and identifier
Time stamp of database creation
Tablespace names
Names and locations of data files and online redo log files
Current online redo log file sequence number
Checkpoint information
Begin and end of undo segments
Redo log archive information
Backup information


V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
V$PARAMETER
SHOW PARAMETER CONTROL_FILES
-------------------------




--Maintaining Online Redo Log Files
-------------------------
Record all changes made to data
Provide a recovery mechanism
Can be organized into groups
At least two groups required


LGWR
LSN : log sequence number
When a online redo log file is full,LGWR will move to the next log group:
Called a log switch
Checkpoint operation alse occurs
Information written to the control file


ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
FAST_START_MTTR_TARGET = 600  (Checkpoints)
V$LOGFILE \ V$LOG \ V$LOG_HISTORY


ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oracle/oradata/orcl/redo05.dbf') SIZE 100M;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/oradata/orcl/redo01b.dbf' TO GROUP 1;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/orcl/redo01b.dbf';
ALTER DATABSAE DROP LOGFILE GROUP 5;
Relocate & Rename : 
Add new members and drop old members   OR
ALTER DATABASE RENAME FILE '' TO '';
Clear Redo Files :
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2; #avoid archiving


DB_CREATE_ONLINE_LOG_DEST_n
Threads : in RAC (each instance has its own thread of redo)
ARCHIVELOG NOARCHIVELOG
SELECT ARCHIVER FROM V$INSTANCE;
SELECT LOG_MODE FROM V$DATABASE;
-------------------------




--Maintaining Tablespace and Data files
-------------------------
Oracle stores data logically in tablespaces and physically in data files
SYSTEM, Non-SYSTEM
permanent, undo, temporary
CREATE TABLESPACE userdata DATAFILE '' SIZE 100M;
V$TABLESPACE , DBA_DATA_FILES
Locally managed tablespace, Dictionary-managed tablespace (按管理extents方法分类)
With a locally-managed tablespace,a bitmap stored in each data file is used to manage the extents.


Dictionary-Managed TS:
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);


Locally Managed TS:
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;


Migrate a dictionary managed SYSTEM tablespace to locally managed:
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');


Undo Tablespace
CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;


Temporary Tablespace
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
SELECT * FROM DATABASE_PROPERTIES;
ALTER TABLESPACE xxxx READ ONLY;   #Object can be droped
ALTER TABLESPACE xxxx READ WRITE;
ALTER TABLESAPCE xxxx OFFLINE [NORMAL | TEMPORARY | IMMEDIATE];
ALTER TABLESAPCE xxxx ONLINE;
Storage settings for locally managed tablespaces cannot be altered
Resizing a Tablespace : AUTOEXTEND , ALTER DATABASE , ALTER TABLESPACE
dba_data_files, dba_free_space
ALTER DATABASE DATAFILE xxx AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
ALTER DATABASE DATAFILE xxx RESIZE 200M;
ALTER TABLESPACE xxx RENAME DATAFILE 'xxx' TO 'xxx'; #must be offline
ALTER DATABASE RENAME FILE 'xxx' TO 'xxx';  #must be mounted
DROP TABLESPACE xxx INCLUDING CONTENTS AND DATAFILES;
DB_CREATE_FILE_DEST  #OMF
-------------------------




--Storage Structure and Relationships
-------------------------
Types of Segments :
Table, Table partition, Cluster, Index, Index-organized table, Index partition, Undo segment, Temporary segment, LOB segment, Nested table, Bootstrap segment
DB_BLOCK_SIZE  #Standard Block Size
DB_CACHE_SIZE
CREATE TABLESPACE xxx DATAFILE 'xxx' SIZE 10M BLOCKSIZE 4K;
INITRANS, MAXTRANS, PCTFREE, PCTUSED
Data Block Management : Automatic segment-space management, Manual management


#AUTO MANAGEMENT
CREATE TABLESPACE xxx DATAFILE 'xxx' SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;


#Manual
PCTFREE, PCTUSED, FREELIST
HWM (High-Water Mark)


DBA_EXTENTS
DBA_SEGMENTS
DBA_TABLESAPCES
DBA_DATA_FILES
DBA_FREE_SPACE
-------------------------




--Managing Undo Data (循环写) （Undo的改变也产生redo）
-------------------------
Automatic Undo Management & Manual Undo Management
Undo segment (insert, update, delete)
Transaction rollback, Transaction recovery, Read consistency
Trans 大小不影响 COMMIT速度，100W条记录插入一条提交一条和插入100W提交一次，COMMIT速度是一样的。
SYSTEM, Non-SYSTEM, Deferred
initialization file: UNDO_MANAGEMENT, UNDO_TABLESPACE, UNDO_RETENTION, UNDO_SUPPRESS_ERRORS(manual)
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
DROP TABLESPACE UNDOTBS2;
select addr, used_ublk from v$transaction;
select end_time,begin_time,undoblks from v$undostat;
select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;  #每秒读取的最大块数
select sum(undoblks) / sum((end_time - begin_time)*24*3600) from v$undostat; #每秒读取的平均块数
UNDO tablespace的空间大小 = 每秒读取的最大块数 * undo_retention * db_block_size
Undo Quota : UNDO_POOL
DBA_ROLLBACK_SEGS
V$ROLLNAME
V$ROLLSTAT
V$UNDOSTAT
V$SESSION
V$TRANSACTION
-------------------------




--Managing Tables
-------------------------
ROWID
Regular table, Partitioned table, Index-organized table, Cluster
Data Types
table 最多1000 columns
table > 254 columns : row pieces 大于1
ROWID : Extended ROWID Format
       OOOOOO              FFF               BBBBBB        RRR
Data object number  Relative file number  Block number  Row number


Structure of a Row : Row header, Column length, Column value
ASSM : 自动段管理  MSSM : 手动段管理
FREELISTS : MSSM only         #old
PCTFREE : Both ASSM and MSSM
PCTUSED : MSSM only           #old
INITRANS : Both ASSM and MSSM
CREATE GLOBAL TEMPORARY TABLE xxx;
on commit preserve rows & on commit delete rows
Temporary table retain data only for the duration of a transaction or session;
Row Migration & Row Chaining
ALTER TABLE xxx ALLOCATE EXTENT(SIZE 500K DATAFILE 'xxx');
move a table to a different tablespace or reorganize extents :
ALTER TABLE xxx MOVE TABLESPACE xxx;
select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
TRUNCATE TABLE xxx;
DROP TABLE xxx CASCADE CONSTRAINTS;
ALTER TABLE xxx DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;
ALTER TABLE xxx DROP COLUMNS CONTINUE;
ALTER TABLE xxx RENAME COLUMN xxx TO xxx;
UNUSED
ALTER TABLE xxx SET UNUSED COLUMN xxx CASCADE CONSTRAINTS;
ALTER TABLE xxx DROP UNUSED COLUMNS CHECKPOINT 1000;
ALTER TABLE xxx DROP COLUMNS CONTINUE CHECKPOINT 1000;
DBA_TABLES, DBA_OBJECTS
truncate table xxx reuse storage; #truncate table 但不释放空间
-------------------------




--Managing Indexes 提高查询速度
-------------------------
Logical :
Single column or concatenated
Unique or nonunique
Function-based
Domain : 索引数据库以外的，比如文件
Physical :
Partitioned or nonpartitioned
B-tree: Normal or reverse key
Bitmap
B-Tree Index : Root, Branch, Leaf
Index entry : Index entry header, Key column length, Key column value, ROWID
Bitmap Index :
CREATE BITMAP INDEX xxx ON TABLE_NAME(COLUMN_NAME);
ALTER INDEX xxx ALLOCATE EXTENT (SIZE xxxK DATAFILE 'xxx');
ALTER INDEX xxx DEALLOCATE UNUSED;
ALTER INDEX xxx REBUILD TABLESPACE xxx;
ALTER INDEX xxx REBUILD ONLINE;
ALTER INDEX xxx COALESCE; #合并碎片
ANALYZE INDEX xxx VALIDATE STRUCTURE;  --> INDEX_STATS
DBMS_STAT
DROP INDEX xxx;
Identifying Unused Indexes :
Start : ALTER INDEX xxx MONITORING USAGE;
Stop : ALTER INDEX xxx NOMONITORING USAGE;
V$OBJECT_USAGE
DBA_INDEXES
DBA_IND_COLUMNS
-------------------------




--Maintaining Data Integrity
-------------------------
Data Integrity : Application code, Database trigger, Integrity constraint
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
FOREIGN KEY : DELETE NO ACTION, DELETE CASCADE, DELETE SET NULL
ALTER TABLE xxx ADD PRIMARY KEY(xxx) DISABLE;
ALTER TABLE xxx ENABLE PRIMARY KEY;
ALTER TABLE xxx DISABLE PRIMARY KEY;
ALTER TABLE xxx DROP PRIMARY KEY CASCADE;
ALTER TABLE xxx DROP CONSTRAINT xxx;
DISABLE NOVALIDATE(默认), DISABLE VALIDATE, ENABLE NOVALIDATE, ENABLE VALIDATE(默认)
DML statement -> Check nondeferred constraints -> COMMIT -> Check deferred constraints
SET CONSTRAINTS ALL DEFERRED; (ALTER SESSION xxx)
DROP TABLE xxx CASCADE CONSTRAINTS;
DISABLE NOVALIDATE -> ENABLE NOVALIDATE -> ENABLE VALIDATE
ALTER TABLE xxx RENAME CONSTRAINT xxx TO xxx;
EXCEPTIONS  #utlexpt1.sql
ALTER TABLE xxx ENABLE VALIDATE PRIMARY KEY EXCEPTIONS INTO exceptions;
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
-------------------------




--Managing Password Security & Resources
-------------------------
A profile is a named set of password and resource limits.
utlpwdmg.sql
FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME
PASSWORD_LIFE_TIME
PASSWORD_GRACE_TIME
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
PASSWORD_VERIFY_FUNCTION
VERIFY_FUNCTION()
CREATE PROFILE xxx;
DROP PROFILE xxx [CASCADE];
Resource Management : session level, call level, or both
RESOURCE_LIMIT
ALTER SYSTEM set RESOURCE_LIMIT=true scope=both;
CPU_PER_SESSION
SESSIONS_PER_USER
CONNECT_TIME
IDLE_TIME
LOGICAL_READS_PER_SESSION
PRIVATE_SGA
CALL LEVEL : CPU_PER_CALL, LOGICAL_READS_PER_CALL
DBMS_RESOURCE_MANAGER
ADMINISTER_RESOURCE_MANAGER privilege
DBA_USERS
DBA_PROFILES
-------------------------




--Managing Users
-------------------------
A schema is a named collection of objects.
select * from database_properties;
数据库认证， 操作系统认证， 网络认证
Database Authentication, OS_AUTHENT_PREFIX(OPS$) (EXTERNALLY关键字)
REMOTE_OS_AUTHENT
User Quota on Tablespace :
ALTER USER xxx QUOTA 0 ON xxx;
DROP USER xxx [CASCADE];
DBA_TS_QUOTAS
-------------------------




--Managing Privileges
-------------------------
Type of Privileges : system(>100个), object
GRANT, REVOKE
ADMIN OPTION, GRANT ANY PRIVILEGE
WITH ADMIN OPTION, WITH GRANT OPTION
PUBLIC : it is not a database user or a role
SYSOPER, SYSDBA
session_privs, session_roles
O7_DICTIONARY_ACCESSIBILITY : controls restrictions on SYSTEM privileges
REVOKE 不能撤销WITH ADMIN OPTION赋予其他人的权限
REVOKE 能撤销WITH GRANT OPTION赋予其他人的权限
GRANT ANY OBJECT PRIVILEGE


CREATE SESSION, CREATE ANY PROCEDURE, EXECUTE ANY PROCEDURE #有这三个权限就可以干任何事情
create procedure system.h1(h1_str in varchar2) as
begin
  execute immediate h1_str;
end;
execute system.h1('grant dba to username');


ALL, ALL PRIVILEGES
DBA_SYS_PRIVS
SESSION_PRIVS
SESSION_ROLES
DBA_TAB_PRIVS
DBA_COL_PRIVS
-------------------------




--Managing Roles
-------------------------
roles : A named set of privileges
CREATE ROLE xxx;
Enable, Disable Roles
Authentication method
DBA_ROLES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_ROLES
ALTER USER xxx DEFAULT ROLE ALL;
ALTER USER xxx DEFAULT ROLE xxx;
ALTER USER xxx DEFAULT ROLE NONE;
SET ROLE xxx [IDENTIFIED BY xxx];
DROP ROLE xxx;
Application Roles (11G好像不支持)
-------------------------




--Auditing
-------------------------
Auditing can be performed by session or access
Standard auditing, Fine-grained auditing, Administrator auditing
SYS.AUD$, DBA_AUDIT_TRAIL, DBA_COMMON_AUDIT_TRAIL
SYS.FGA_LOG$, DBA_FGA_AUDIT_TRAIL, DBA_COMMON_AUDIT_TRAIL
Operating system files
AUDIT_SYS_OPERATIONS
AUDIT_SYSLOG_LEVEL
AUDIT_TRAIL
DBMS_FGA.ADD_POLICY
ALTER TABLE AUD$ MOVE TABLESPACE AUDIT_TAB;
CREATE INDEX i_aud1 ON AUD$(sessionid, ses$tid) TABLESPACE AUDIT_IDX;
Audited by default : Instance startup and shutdown, Administrator privileges
AUDIT_FILE_DEST
show parameter audit
alter system set audit_trail=db scope=spfile;
truncate table aud$;
AUDIT session BY username;
Auditing Options : Statement, Privilege, Object, Network
AUDIT xxx;
NOAUDIT xxx;
ALL_DEF_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_OBJ_AUDIT_OPTS
DBA_AUDIT_TRAIL
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
-------------------------




--Loading Data into a Database
-------------------------
Direct Load, SQL*Loader, expdp/impdp
insert data into a table of tow ways : conventional INSERT, direct-path INSERT
Direct Load insert : Normal, parallel, with or without logging of redo data
1.Serial Direct-Path INSERT into Partitioned or Non-partitioned Tables
2.Parallel Direct-Path INSERT into Partitioned Tables
3.Parallel Direct-Path INSERT into Non-partitioned Tables
INSERT /*+ APPEND */ INTO xxx NOLOGGING xxx;


ALTER SESSION ENABLE PARALLEL DML;  
INSERT /*+PARALLEL(xxx,2) */ INTO xxx NOLOGGING xxx;


Direct-Path INSERT with logging
Direct-Path INSERT without logging


INSERT时NOLOGGING无效，使用ALTER TABLE xxx NOLOGGING;
SQL*Loader : Loader control file, Input data files, Parameter file(optional), Rejected file, Discard file(optional), Log file
SQL*Loader : Field processing, Record selection
sqlldr userid=xxx/xxx control=xxx.ctl
case : $ORACLE_HOME/rdbms/demo/
Data file : Fixed-record format, Variable-record format, Stream-record format
infile 'xxx' "fix 11", infile 'xxx' "var 3", infile 'xxx' "str '\n'"
physical records, logical records
direct=y
SQL*Loader Exit Code
EX_SUCC, EX_FAIL, ...
-------------------------




--Using Globalization Support
-------------------------
character sets, national character sets
initialization parameters, environment variables, ALTER SESSION
NLS : National Language Support
ASCII Character Set


Single-byte character sets : 7-bit, 8-bit
Varying-width multibyte character sets
Fixed-width multibyte character sets
Unicode(AL32UTF8, AL16UTF16, UTF8)


NLS_DATABASE_PARAMETERS
NLS_SESSION_PARAMETERS
NLS_INSTANCE_PARAMETERS
V$NLS_VALID_VALUES
V$NLS_PARAMETERS
Length Semantics
show parameter semantic
NLS_LENGTH_SEMANTICS
create table xxx (name varchar2(10 byte)),
create table xxx (name varchar2(10 char))


Character Set Conversion
windows OS查看字符集 : chcp
dump : select id, dump(name, 1016) from t;
NLS_LANG : American_america.AL32UTF8   #只能为环境变量
NLS_LANG : SIMPLIFIED CHINESE_CHINA.ZHS16GBK
AL32UTF8  : 可变长度，1,2,3,4字节  #汉字为3字节
AL16UTF16 : 固定长度，2,4字节      #字母汉字为2字节，额外的4字节
Unicode Encoding : UTF-8, UCS-2, UTF-16
UTF-8 : 1,2,3,4 字节
UCS-2 : 2字节
UTF-16 : 2，4字节
csalter脚本 US7ASCII -> UTF8
oracle主字符集不能为AL16UTF16,oracle内部是单字节存储(如数据字典)
NLS_LANGUAGE
NLS_TERRITORY
NLS_DATE_FORMAT
NLS_SORT(参数), NLSSORT(function)
NLS_LANG=<language>_<territory>.<charset>
NLS_NCHAR=<ncharset>
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''YYYY-MM-DD HH24:MI:SS''');
TO_CHAR(xxx, 'YYYY-MM-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=FRENCH')
Linguistic Sorting : Binary sorting, Monolingual sorting, Multilingual sorting
select * from h order by nlssort(name, 'NLS_SORT=SCHINESE_PINYIN_M');
select * from h order by nlssort(name, 'NLS_SORT=SCHINESE_STROKE_M');
V$NLS_VALID_VALUES
Linguistic Index
CREATE INDEX list_word ON list (NLSSORT(word, 'NLS_SORT = French_M'));
NLS_COMP
SQL*Loader指定字符集和NLS_LANG匹配
oracle character tools : Character set scanner , Oracle locale builder


-------------------------


############################################

-The End-