oracle9i 学习笔记及心得

n
1z0_031_01.avi Oracle Architectural Components
1z0_031_02.avi:管理工具
1z0_031_03.avi:Managing an oracle instance
1z0_031_04.avi:Create a database
1z0_031_05.avi:Data Dictionary Contents and Usage
lz0_031_06:Maintaining the Control File

lz0_031_07:Maintaining Redo log files
lz0_031_08:Managing Tablespaces and Data files
lz0_031_09:Storage Structure and Relationship
lz0_031_10:管理重做数据Manage Undo Data

lz0_031_11:管理表Manage Table
lz0_031_12:管理表Managing Indexes
lz0_031_13:维护数据的完整性
lz0_031_14:口令安全管理
lz0_031_15:Manaing Users
lz0_031_16:Manaing Privileges管理权限
lz0_031_17:Manaing Roles管理角色
lz0_031_18:Using Globalization Support

------*************************************--------------
1z0_031_01.avi:Oracle Architectural Components
db:
Instance:share pool,database buffer cache,redo buffercache (SGA),background processor
database:Datafile,control file,log file,etc

v$bgprocess
select * from v$bgprocess where paddr<>'00' --后台必要进程
show parameter sga
show parameter db

alter system set db_cache_size=64M
alter session set nls_language=american
alter system set shared_pool_size=64M;

share pool:
 libarary cache--Shared SQL area,Shared PL/SQL area
 data dictionary cache =row cache 
 
database buffer cache
  DB_block_size
 db_cache_advice --依据系统进行内存建议
alter system set db_cache_advice=on

show parameter log_buffer
alter system set large_pool_size =64M(用户会话信息等，共享服务器)

Java Pool 组件池


Program Global Area(PGA)
Process Structure :user process,Service process,Background process

archive log list

Database Writer(DBWn):将数据写入DB
Log Write(LGWR):将redo log buffer写入Redo log file
System Monitor(SMON):系统监视
Process Monitor(PMON)
Checkpoint(CKPT):同步
Archiver(ARCn):归档，自动备份日志

Logical Structure
tablespace,segments,extents分区,and blocks



1z0_031_02.avi:管理工具
--------------------------------
Installer: Linux:  runInstaller
DBCA:

sys:
 change_on_install
Owner of the database data dictionary

system:manager Owner of additional inernal tables and views used by oracle tools.

创建OS用户并增入DB
create os userid
create os group-ora_dba,ora_fox_dba
add os userid to ora_dba group
edit sqlnet
 修改:sqlnet.authentication_servies=(NTS)

口令审核--例：将sysdba授权给网络中的用户
orapwd file=$oracle/ora90/database/pwdfox.ora password=admin11 entries=10（可增用户数）
set remote_login_passwordfile to execlusive (init.ora)
grant sysdba to hh;
select * from v$pwfile_users;查询口令文件中的用户

sqlplus:set autocommit on
sysman:oem_temp


1z0_031_03.avi:Managing an oracle instance
--------------------------------------------
有效方向spfilesid.ora->spfile.ora->initsid.ora
select * from v$system_parameter
alter system set timed_statistics=false scope=memory(pfile,both);

Creating an Spfile
create spfile from pfile
create spfile='d:/../spfile.ora' from pfile;
create pfile='d:/.../pfile.ora' from spfile;--对文件的复制,一般不用文本编辑器改动

Oracle Manage Files
show parameter db_create
alter system set db_create_file_dest='d:/oracle/oradb';
create tablespace test1;(先要用上句来指定文件路径，才可这样来做)
drop tablespace test1 including contents and datafiles;

alter session set nls_language=american
startup pfile='../initSID.ora'

startup restrict --特权使用
altere system enable/disable restricted session;
grant restricted session to user1;

alter session set optimizer_mode=first_rows;
show parameter sql_trace;



1z0_031_04.avi:Create a database
------------------------------------------
set oracle_sid=orafox
setenv oracle_home=


1。指定INSTANCE和DBNAME
2。指定字符集
3。设定环境变量
4。建立初始文件
5。启动实例
6。执行命令create database
7。执行相关脚本

set oracle_sid=foxconn
oradim -new -sid foxconn -intpwd admin -pfile d:/oracle/ora90/database/initfoxconn.ora
将ora90/database/admin/sample 拷入admin，更改一些选项（目录名，文件名，文件内容参数）
控制文件在init.ora中指定文件完整路径，它会create database时自动建立

sqlplusw /nolog
conn sys/admin as sysdba
编辑initfoxconn路路径
create spfile from pfile;
startup nomount
create database foxconn
maxlogfiles 10
maxdatafiles 1024
maxinstances 2
datafile 'd:/oracle/oradata/foxconn/system01.dbf' size 100m
logfile
 group 1 'd:/oracle/oradata/foxconn/redo01.ora' size 10m,
 group 2 'd:/oracle/oradata/foxconn/redo02.ora'  size 10m

/

完成一些脚本
--ora92/rdbms/admin

1z0_031_05.avi：Data Dictionary Contents and Usage
---------------------------------------------------
select * from dba_users;
select * from dba_objects;

DBA:what is in all the schemas
ALL:what the user can access
USER:what is in the user's schema

alter session set nls_language=america

desc dictionary　数据字典视图
desc v$fixed_table 动态性能表

建立SQL帮助:运行catproc.sql,/ora92/sqlplus/admin/pupbld.sql,ora92/sqlplus/admin/help/hlpbld.sql(参数helpus.sql)






lz0_031_06:Maintaining the Control File
------------------------------------------------
数据库的结构信息

Control File Contents
.Database name and identifier
.Time stamp of database creation
.tablespace names
.Names and locations of data files and redo log files
.Current redo log file sequence number
.Checkpoint information
.Begin and end of undo segments
.Redo log archive information
.Backup information

select * from v$database;
select * from v$tablespace;
select * from v$logfile;
select * from v$log;
select * from v$backup;
sekect * from v$achived_log;
alter tablespace users begin backup;--end backup

对控制文件作多工

获取控制文件的相关信息
show parameter spfile
show parameter control
show parameters control_files
show parameter  user_udump
select * from v$parameter where...
select * from v$controlfile;
alter system set control_files='d:/...X.ctl','..' scope=spfile;
关闭数据库 shutdown immediate
准备Control文件 拷贝文件并重命名
增加语句
重启数据库

select * from v$paramter where name like 'control%'
show parameter control

select * from v$controlfile_record_section
备份：
alter database backup controlfile to 'c:/....bak'
alter database backup controlfile to trace;创建所需的文本脚本
show parameter user_dump



lz0_031_07:Maintaining Redo log files
-----------------------------------------
Archive log list

Using Redo Log Files
.Redo log files are organized into groups
.An Oracle database requires at least two groups

alter system archive log start
alter system log switch //强制switch
alter system switch logfile;
alter system checkpoint //系统同步，写控制文件和数据文件

MTTR－－mean time to recovery
show parameter fast
show parameter Log_checkpoint
日志文件的不同组的大小可以不一样，但同一个组中的文件大小是相同的
删除文件的说明：当前日志组，活动的日志组，非归档日志组不能删
一个日志组中至少有一个日志成员文件，若有多个文件，最后一个文件不能删除

Clear logfile --重新初始化日志文件
alter database clear logfile group2
alter database clear unarchived logfile group5 --要做一次full db backup

alter database rename file 'd:/...ora' to '...ora'

在线日志文件配置

show parameter db_create
alter syste set db_create_online_log_dest_1='d:/oracle/or..'
alter database add logfile group 6;(与上条语句结合使用)
alter database add logfile member 'd:/..' to group 6;
alter database drop logfile member 'd:/..'

v$log:log group information status
v$logfile :status --blank in use


Archived Redo log files
.recovery;
.backup


archivelog <-change->noarchivelog
.shutdown immediate
.startup mount
.alter database noarchivelog(archivelog)
.alter database open;
.full dbbackup
alter system archive log all
select * from v$archived_log;
show parameter log_archived;

数据挖掘logmnr---
1)set directory
2)retartup
3)create directory file
  dbms_logmnr_d.build
4)add/remove log file
  dbms_logmnr.add_logfile(removefile)

5)start logmnr
  dbms_logmnr.start_logmnr
6)v$logmnr_content ---sqlredo/sqlundo


desc dbms_logmnr_d

desc dbms_output

start c:/oracle/ora92/rdbms/admin/catproc.sql

execute dbms_logmnr_d.build('foxdict.ora','c:/.fox/cdump')

select * from v$log;找到当前使用的日志文件
execute dbms_logmnr.add_logfile('d:/oracle/oradata/fox/redo02.txt','dbms_logmnr.new);

execute dbms_logmnr.start_logmnr(dictfilename=>'d:/../fox/foxdict.ora');



lz0_031_08:Managing Tablespaces and Data files
---------------------------------------------------
管理表空间和数据文件

database
^
tablespace --logical area
^
segment --specific logical structure occupied space collection
只能放在一个表空间中，但可以有多个数据文件space collection table/index/temp/rollback
^
extent ---分区allocate  unit
^
oracle block --use&store unit


select * from v$tablespace;
select * from v$datafile


在任何一个数据库中一定有一个表空间是系统表空间
select * from dba_rollback_segs;

系统表空间:
数据库 created with the database
数据字典 containts the data dictionary
系统重做内容 Contains the SYSTEM undo segment

alter user xx quot 10m on yy
限制用户xx在表空间yy上使用10M

非系统表空间
Separate segments
ease space administration
control amount of space allocated to a user

[datafile 'xxx'] OMF
[extent ]

Space Management in Tablespaces
.Locally managed tablespaces
.Dictionary-managed tablespaces

create tablespace xx
datafile 'd:...'
extent management dictionary(local autoallocate)
default storage(initial 1M next 1M pctincrease 10
mininum extent 10
maxextents 999
);

Undo Tablespace
.Used to store undo segments
.Cannot contain any other objects
.Extents are locally managed
.Can only use the Datafile and Extent Management

Undo Tablespace:不能存放其他对象
create undo tablespace undo1 datafile '...' size xxm;
Temporary Tablespace:不能改为永久表空间，不能删除默认的表空间，
show parameter undo
v$dba_tablespace;
v$datafile;

v$tempfile
临时表空间用于排序(当内存不够)建议使用本地管理,不能创建永久数据对象
不能脱机，read only
create temporary tablespace kong4
 tempfile 'd:/..' size 5m
 extent management local;

alter database default temporary tablespace k4;
alter tablespace users read only;
drop tablespace kk including contents and datafiles;
alter tablespace datafile 'd:/..'
autoextend on /resize xxm

alter tablespace userd
 rename datafile 'kk' to 'd:/..'先要建目标文件

moving data file
startup  mount
alter database rename file 'dd' to 'dd2' 先要建目标文件



study lz0_031_09:Storage Structure and Relationship
---------------------------------------------------


表空间：不指定存储参数，使用默认值
优先级：初始分区为块大小的5倍(5*8k)
段-->表空间－＞系统默认值

alter table x allocate extent(size 1m datafile 'd:/.../kong');Kong为表空间
alter table kong.authors
deallocate unused;

create table kongw
datafile 'd:/..'
blocksize 8k;

create tablespace k
datafile 'd:/..' size 10m
extent management local uniform size 1m
segment space management auto;段空间自动管理

PCTfree:剩余块空间预留将来数据使用，若达到n%时，通知ORCALCE另row migration行移植处理
PCTused:如果使用率低于N%，通知ORACLE可以增加数据
show parameter db
alter system set db_8k_cache_size=10m

获取信息:
used extents
dba_extents
dba_segments

select sum(bytes) from dba_extents where owner='dd' and segment_name='x'

free extents
dba_free_space
dba_data_files

lz0_031_10:管理重做数据Manage Undo Data
-----------------------------------------
v$rollname;
v$rollstat;
v$undostat;
v$session;
v$transaction;
dba_rollback_segs
show parameter undo

alter system set undo_management=manual
alter system set undo_tablespace=ud1;
create undo tablespace undo1 datafile '..d' size 1m
autoextend on;
alter system set undo_suppress_errors=true;
create rollback segment rbs1 tablespace undotbs;(error)

提交后回闪(恢复)
execute dbms_flashback.enable_at_time('26-jan-04:12:17:00pm');
execute dbm_flashback.disable;(undo_retention)

recover datafile 'd:/...dbf'

v$undostat
Undospace=[UR:UNDO_RETENTION *(UPS *DBS:db_block_size)]+(DBS*@$)

手工管理回滚段
show parameter rollback
create public rollback segment rbs1
tablespace undo undotbs;
alter rollback segment rbs1 online;(手动联机)
--in initfox.ora edited add
undo_management=manual
rollback_segments=('rbs1','rbs2')
transactions=100
transactions_per_rollback_segment=10
select * from dba_rollback_segs;

lz0_031_11:管理表Manage Table
dba_tables;
dba_Objects;

数据类型：用户定义和系统定义
系统定义:
Scalar标量类型 nchar(N)nvarchar(N)用于支持国际字符集
varchar(N)varchar2(N),number(p,s),date,timestamp,raw(N),BLOB,CLOB,NCLOB,BFILE,LONG,LONG RAW,ROWID,UROWID
ROWID:扩展ROWID：18个64位的string　数据对象。相对文件号。块号。行号
Collection集合数组 Varray:record Table
Relationship参照类型REF参照游标）

desc dbms_rowid

create global temporary table XXX on [commit  delete rows | commit preserve rows ]
as select * from ath;

create table t3
(id int)
tablespace users
storage(initial 100k
next 100k
pcincrease 0
minextents 1
maxextents 10)
pcfree 10
pcused 40

alter table hr
allocate extent(size 500k datafile 'd:/dd..')

alter table kon
deallocate unused keep 2m;当删除数据时，收缩表,并保持高水印（High Water Mark)

alter table kon
move tablespace data1;会保持原有的索引（索引不可用，要重建），约束等;重新组织表
alter index kon_index rebuild;

Truncate table h (DDL 会释放空间，而DELETE不会,要手工收缩，如上）
Drop table kk cascade constraints

alter table h
drop column con
cascade constraints checkpoint 1000;

{alter table h
set unused column c1 cascade constraints; --先设列无效

alter table h
drop unused columns checkpoint 1000; 　--再删除

alter table h
drop columns  continue checkpoint 1000; --若在删除时掉电，再删除
}



lz0_031_12:管理表Managing Indexes
--------------------------------------
.Logical
  Single column or concatenated
  Unique or nonunique
  Function-based
  Domain
.Physical
  Partitioned or nonpartitioned
  B-tree:唯一性高的情况下,在OR条件下不使用索引,OLTP
  Bitmap:唯一值很少的情况下,在OR条件下使用索引,OLAP（用0,1表示）

Create bitmap index te
on s(sex)
tablespace k;在存储参数中不允许使用PCTUSED
nologging --不作日志

建立反转索引
create index index1 on order(id) reverse;

alter index t1 deallocate unused;

整理碎片
alter index idx rebuild tablespace in2;

show parameter create_bit --加速位图索引查询
show user
alter table tablex deallocate unused;
alter index indexx allocate extent(size 100k datafile 'd:/.../x.dbf')

alter index testindex3 rebuild tablespace ndx;

alter index text rebuild reverse;
最小化锁定,在旧有索引的情况下重建
alter indext tx rebuild online;

alter index oind coalesce;整理碎片
--检查索引的有效性
analyze index ind2 validate structure;
--监示索引的使用情况
alter index ind01 [monitoring|nomonitoring] usage;
select * from v$object_usage

create index k on bb(col1-col2)

dba_indexes
user_indexes
...
dba_ind_columns
dba_ind_expression

lz0_031_13:维护数据的完整性Maintaining Data Integrity
-------------------------------------------------------

disable novalidate 对原来的数据不作验证
disable validate 对新进入的数据可不满足条件，对原数据验证

alter table ss
add constraint ck_dd check(s_name like '#%')
enable novalidate --不校验已存在的数据

altere table kk
enable validate constraint pk_0;
exceptions into exceptions --将不符约束的记录存入表中
--start ora90/admin/utlexcpt.sql 运行这个脚本
select * from exceptions

alter table sales
add constraint ck_1 check(qty>0)
initially deferred/immediate;--允许改，但在提交时才应用约束

alter session set constraints=deferred;

alter table sales disable constraint fk_1
truncate table stores

dba_constraints
dba_cons_columns

lz0_031_14:口令安全管理
---------------------------------------
ProFile概要文件

alter user
account lock;(unlock)
password expire;

create porfile pro1 limit
failed_login_attempts 3 --尝试三次后就锁定
password_lock_time 1/1440;以天为单位(锁定１分钟),1/24一个小时

Password_life_time 2 生命周期２天
password_grace_time 3;  第一次成功登录后多少天后可修改口令
password_reuse_time 口令可以重复使用次数
password_reuse_max  最大次数
password_verify_funtion(user,新口令，旧口令)-- run utlpwdmg.sql

alter profile profile1 limit
password_verify_function verify_function;

drop profile de1;
alter system set resource_limit=true

cpu_per_session 10000
connect_time 60
idle_time 3;
session_per_user 2
connect_time 480

DBA_USERS
DBA_PROFILE

lz0_031_15:Manaing Users管理用户
---------------------------------------
authentication mechanism审核模式（DB、OS、全局）
account lock
default tablespace
temporary tablespace支持排序
tablespace quotas(分配)
resource limits
direct privileges
role priviliges

create user u
identified by s
default tablespace user;
quota 1m on users;
password expire;

alter user tt
default tablespace users
default tablespace temp
quota 10m on users;(0m时，不允许使用表空间)

show parameter default
alter database default temporary tablespace temp;

create user ops$dd
identified Exenally (OS认证)
default tablespace temp;

conn / as sysdba
show parameter os
create user ops$kk --OS审核，加ops$
identified externally

临时、回滚表空间不能指定用户限额
drop user test1 cascade

desc dba_ts_quotas 表空间限额


lz0_031_16:Manaing Privileges管理权限
-------------------------------------------
系统权限：动作
对象权限：操作对象

audit select on k.tab by access;

creat table   --不可传递，不能把owner给别人(如：create table kong.dd (id int)就不可)
create any table  --可以传递权限,可做任何owner

create any index 没有create index权限项
没有truncate权限项，存在于drop any table项

show parameter o7
alter system set o7_xxx=true scope=spfile --设为true可读取系统表

alter user kk quota 10m on users;

create role r1;
grant create session to r1
with admin option;--

SYSDBS and SYSOPER Privileges
SYSOPER:startup shutdown
  alter database open |mount
 alter database backup controlfile to
recover database
alter database archive log
缺省情况下，只有owner才可以授权，但除了指定with grant option
sysdba：all
select * from v$pwfile_users

dba_sys_privs;系统权限
session__privs;会话中有的权限
dba_tab_privs;哪些对象授给哪些用户
dba_col_privs;

审计
show parameter audit_trail　开启审记(db,os,none)

audit select on kon.dd by session(相同只记一次)/access whenever successful/not successful;

noaudit select on kon.dd

desc dbms_fga

all_def_audit_opts
dba_stmt_audit_opts
dba_obj_audit_opts
dba_priv_audit_opts

获取审计记录^|

dba_audit_object
dba_audit_session
dba_audit_exists/statement/trail

audit select on kong.authors

lz0_031_17:Manaing Roles管理角色

create role t1 (identified by kkk);
动态管理
set role r1 identified by kkk ;激活角色
set role r2

connect,resource,dba
exp_full_database
imp_full_database
delete_catalog_role
execute_catalog_role --run标准的包
select_catalog_role

alter user xx
default role r1,r2...
all /all except r1...

default role none;--Cancel role

set role all /all except r1..

set role none

revoke r1 from role1

dba_roles
dba_role_privs
role_role_privs
dba_sys_privs
role_sys_privs
role_tab_privs

session_roles

select * from role_tab_privs
select * from role_sys_privs



lz0_031_18:Using Globalization Support
----------------------------------------
Language  support
Territory support
Character set support
Linguistic sorting
message support
Date and time formats
Numeric formats
Numeric formats
Monetary formats

select parameter,ualue from nls_database_parameters
AL16UTF16 fixed 2 bytes
UTF8 variable width 1-3 bytes

alter session set nls_language='simplified chinese'
设置环境变量(dos cmd)
set nls_lang=AMERICAN_AMERICA.US7ASCII

select parameter,value fro nls_instance_parameters
select * from nls_session_parameters

alter session set nls_currency='$'

execute dbms_session.set_nls('NLS_DATE_FORMAT','''yyyy.mm.dd''');
alter session set NLS_DATE_FORMAT('yyyy.mm.dd')


v$nls_parameters


-------007---------------

create view force as select * from xx;--强制创建


select tz_offset('US/Eastern') from dual;

超级聚合rollup:
group by rollup(id,sname) 从左至右再聚合

group by cube(id,sname) 从右往左再聚合

select id,sname,grouping(id) ...是否以ID为聚合（0或1)

group by grouping sets((id),(sname)) 分别求分组，再UNION

with
  d1 as (select ..)
  d2 as (select ..)
select * from d1 where id >(select id from d2)

select level id,name from t
where id>10
start with id=1 connection by prior id=pid

多表数据插入
1.无条件插入
insert all
  into sname(sid) values(id)
  into kkk(sname)   values(name)
 select id,name from employees where id>3
2.条件插入
insert all
   when id>100 and id like '1%'  then
     into sname(sid) values(id)
   when id<90 then
     into kkk(sname)   values(name)
select id,name from employees where id>3
3.First insert语句
Insert First
   when id>100 and id like '1%'  then
     into sname(sid) values(id)
   when id<90 then
     into kkk(sname)   values(name)
    else
     into bb(id,sname) values(id,name)
select id,name from employees where id>3
4.旋转插入 Pivoting insert
 Insert all
   into sales values(emp_id,week_id,sales_mon)
   into sales values(emp_id,week_id,sales_tue)
select emp_id,week_id,sales_mon,sales_tue from sales_data

外表 External Table
create directory emp_dir as '/s.txt'
create table oldemp(
empno number,empname char(20) )
Organiztion External(
Type oracle_loader default directory emp_dir
Access parameters(records delimited by newline
badfile 'bad_emp'
logfile 'log_emp'
fields terminated by ','
(empno char,empname char) )
Location ('s.txt') )
parallel 5
reject limit 200;
创建表时进立索引
create table tbl(id int not null primary key using index(
index t_index on tbl(id)),
lname varchar2(20) );