oracle学习笔记（转）
命令行操作:
打开服务: services.msc
启动Oracle: net start OracleOraHome92TNSListener
net start OracleService实例名
停止Oracle: net stop OracleOraHome92TNSListener
net stop OracleService实例名
打开Enterprise Manager Console: oemapp.bat console即会出现enterprise manager console界面
打开sqlplus
sqlplusw /nolog
或者sqlplus /nolog
以下操作在打开sqlplus以sys用户登录后进行------------------------
SQL> conn sys/sys@gist as sysdba;
或者SQL> conn sys/sys as sysdba;
--------------------------------------更改语言
alter session set nls_language=american;
--------------------------------------清除当前窗口
clea scre
执行某SQL文件
SQL> @D:\oracle\ora92\RDBMS\admin\catalog.sql
SQL> @C:\a.sql;
show parameter shared;
查看三种数据库文件
select * from v$logfile;-------日志文件
select * from v$datafile;------数据文件
select * from v$controlfile;---控制文件
select * from v$log;-----------当前重做日志文件的序列号，日志组
select * from v$backup;--------备份文件
select * from v$tempfile;------临时文件
设置进入备份状态
SQL> alter tablespace users begin backup;-----设置进入备份状态
取消联机备份
SQL> alter tablespace users end backup;-------取消联机备份
查看归档日志
SQL> select * from v$archived_log;------------查看归档日志
SQL> show parameter spfile--------------------查看当前系统启动的SPFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%
**********************************************************************************************************
--------------------------------------------------进入ISQLPLUS : http://WWW-976307DA5D4:7778/isqlplusdba
查D:\oracle\ora92\Apache\Apache\ports.ini文件中的s_apachePort = 7778
命令行完成以下设置：【添加登录用户admin及密码】
C:\Documents and Settings\Administrator>d:
D:\>cd D:\oracle\ora92\Apache\Apache\bin
D:\oracle\ora92\Apache\Apache\bin>htpasswd D:/oracle/ora92/sqlplus/admin/iplusdb
a.pw admin 【解释:设置用户名：admin】
Automatically using MD5 format on Windows.
New password: ***** 【解释：设置密码：admin】
Re-type new password: ***** 【解释：重复密码：admin】
Adding password for user admin
查D:\oracle\ora92\sqlplus\admin\iplusdba.pw文件中生成参数信息admin:$apr1$MJ5.....$WiF.iQOtbDozEvhYfl8tt0
启动OracleOraHome92HTTPServer服务
IE访问地址：
http://WWW-976307DA5D4:7778/isqlplusdba
http://127.0.0.1:7778/isqlplusdba也可访问
*************************************************************************************************************
查看进程
select * from v$bgprocess where paddr<>'00';
查看日志buffer
show parameter log_buffer;
查看归档日志
archive log list
打开Database Configuration Assistant
C:\Documents and Settings\Administrator>dbca ------------------即打开数据库配置帮手图形界面
sys初始口令: change_on_install 为数据库数据字典表的所有者[Owner of the database data dictionary]
system初始口令: manager 为管理工具所要求的类表及视图的所有者[Owner of additional internal tables and views used by Oracle tools]
scott初始口令: tiger
创建用户ID
create os user id
create os group ora_dba, ora_gist_dba
查看有多少用户授予了dba权限
select * from v$pwfile_users
授予某用户为dba权限
grant sysdba to username
取消某用户的dba权限
revoke sysdba from username
sql*plus环境设置命令
set autocommit on
set linesize 1000
关闭数据库 shutdown immediate
启动数据库 startup
set wrap off
set linesize 100
查看参数文件
select * from v$system_parameter where name like 'time%'
/
run
利用参数文件的顺序sid为数据库名GIST
-->spfilesid.ora-->spfile.ora-->initsid.ora
修改D:\oracle\admin\gist\pfile\init.ora文件参数
select name, value from v$system_parameter where name like 'time%';
alter system set timed_statistics=false scope=spfile;
startup force
desc v$system_parameter
名称 是否为空? 类型
----------------------------------------------------- -------- -------------------
NUM NUMBER
NAME VARCHAR2(64)
TYPE NUMBER
VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISMODIFIED VARCHAR2(8)
ISADJUSTED VARCHAR2(5)
DESCRIPTION VARCHAR2(64)
UPDATE_COMMENT VARCHAR2(255)
select name, isses_modifiable from v$system_parameter where isses_modifiable='TRUE';
修改SQL----------------false改为'FALSE'
SQL> select name, issys_modifiable from v$system_parameter where issys_modifiable<>false;
select name, issys_modifiable from v$system_parameter where issys_modifiable<>false
*
ERROR 位于第 1 行:
ORA-00904: "FALSE": 无效的标识符
SQL> c /false/'FALSE';-----------------------------修改SQL
1* select name, issys_modifiable from v$system_parameter where issys_modifiable<>'FALSE'
SQL> run
1* select name, issys_modifiable from v$system_parameter where issys_modifiable<>'FALSE'
NAME ISSYS_MOD
---------------------------------------------------------------- ---------
timed_statistics IMMEDIATE
timed_os_statistics IMMEDIATE
resource_limit IMMEDIATE
license_max_sessions IMMEDIATE
license_sessions_warning IMMEDIATE
shared_pool_size IMMEDIATE
large_pool_size IMMEDIATE
trace_enabled IMMEDIATE
nls_length_semantics IMMEDIATE
nls_nchar_conv_excp IMMEDIATE
备份init.ora文件
create pfile='d:\initbck.ora' from spfile;
创建表空间：
SQL> create tablespace test1
2 datafile 'd:\test1.dbf' size 2m;
表空间已创建。
SQL> drop tablespace test1;
test1.dbf依然存在
删除表空间：
SQL> create tablespace test1
2 datafile 'd:\test1.dbf' reuse;
表空间已创建。
SQL> drop tablespace test1
2 including contents and datafiles;
表空间已丢弃。
test1.dbf已经删除
指定默认创建表空间的位置及日志文件的默认位置
指定前，不能创建
SQL> create tablespace test1;
create tablespace test1
*
ERROR 位于第 1 行:
ORA-02199: 丢失 DATAFILE/TEMPFILE 子句
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
指定后
SQL> alter system set db_create_file_dest=
2 'd:\oracle\oradb';
系统已更改。
SQL> create tablespace test1;
表空间已创建。
SQL> show parameter db_create;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string d:\oracle\oradb
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
增加日志文件缺省位置d:\oracle\oradb
alter database add logfile group 6
删除表空间,在修改oracle控制文件的信息的同时也删除物理文件
drop tablespace test1;
删除日志文件，在修改oracle控制文件的信息的同时也删除物理文件
alter database drop logfile group 6;
访问实例
select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VE
--------------- ---------------- ---------------------------------------------------------------- --
1 gist WWW-976307DA5D4 9.
访问后台进程
select * from v$bgprocess;
访问SGA
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
访问DATABASE，如果不能执行，先执行alter database mount打开控制文件
select * from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_ PRIOR_RESETLOGS_CHANGE# PRIOR_RESE LOG_
---------- --------- ---------- ----------------- ---------- ----------------------- ---------- ----
1091306975 GIST 28-6月 -10 190578 28-6月 -10 1 12-5月 -02 NOAR
查询DATABASE结构下的所有表空间
select * from v$tablespace;
TS# NAME INC
------ ------------------------------ ---
12 TABLESPACE1 YES
13 TSM YES
14 S2AQ2 YES
15 S2AQTEMP YES
由哪些数据文件组成
select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_T TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# C
---------- ---------------- ---------- ---------- ---------- ------- ---------- ------------------ -
1 11 12-5月 -02 0 1 SYSTEM READ WRITE 1785202 1
2 187697 12-5月 -02 1 2 ONLINE READ WRITE 1785202 1
3 6283 12-5月 -02 3 3 ONLINE READ WRITE 1785202 1
4 6302 12-5月 -02 4 4 ONLINE READ WRITE 1785202 1
5 6324 12-5月 -02 5 5 ONLINE READ WRITE 1785202 1
6 6343 12-5月 -02 6 6 ONLINE READ WRITE 1785202 1
7 6363 12-5月 -02 7 7 ONLINE READ WRITE 1785202 1
8 6382 12-5月 -02 8 8 ONLINE READ WRITE 1785202 1
9 6401 12-5月 -02 9 9 ONLINE READ WRITE 1785202 1
10 6420 12-5月 -02 10 10 ONLINE READ WRITE 1785202 1
11 209205 28-6月 -10 12 11 ONLINE READ WRITE 1785202 1
FILE# CREATION_CHANGE# CREATION_T TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# C
---------- ---------------- ---------- ---------- ---------- ------- ---------- ------------------ -
12 226097 28-6月 -10 13 12 ONLINE READ WRITE 1785202 1
13 866142 03-7月 -10 14 13 ONLINE READ WRITE 1785202 1
有哪些日志文件组成
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 32 104857600 1 NO CURRENT 1785201 11-10月-10
2 1 30 104857600 1 NO INACTIVE 1725945 10-10月-10
3 1 31 104857600 1 NO INACTIVE 1752348 10-10月-10
打开DATABASE
alter database open;
Oracle启动过程
start [nomount|mount|open]
nomount--startup instance
used to create db,create controlfile
mount----open controlfile
used to change log mode,rename file, db recovery
修改SESSION语言
alter session set nls_language=american
Session altered
无法启动已在运行的 ORACLE --- 请首先关闭
cannot start already-runing ORACLE - shut it down first
强制重启
SQL> startup
ORA-01081: 无法启动已在运行的 ORACLE --- 请首先关闭
SQL> startup force ----------强制重启
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
使用强制启动的参数文件[init.ora]，指定启动到哪个状态[mount]
startup force pfile=d:\oracle\admin\gist\pfile\init.ora mount
启用restricted[受限制]的session权限
alter system enable restricted session;
授予某用户restricted权限，否则该用户不能登录
grant restricted session to username;
启动数据库为只读状态
shutdown immediate
startup mount
alter database open read only;----------只能查看，不能修改
关闭数据库的四种方式
1. shutdown immediate ----干净关闭
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
2. shutdown abort -----脏关闭
SQL> shutdown abort
ORACLE 例程已经关闭
3. shutdown normal
4. shutdown transactional
查看所有用户
select * from dba_users;
查看所有对象
select * from dba_objects;
查看用户表
select * from user_tables;
查看我拥有的可以访问的表
select * from all_tables;
***************************************************************************
Oracle启动三种状态下的访问结果
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;-----------------------------------1. nomount状态
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> select * from v$instance
2 /
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- -------------------------------- -------------------
1 gist WWW-976307DA5D4
SQL> select * from v$sga;
NAME VALUE
---------------------------------------- ----------
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
SQL> select * from v$tablespace;
select * from v$tablespace
*
ERROR 位于第 1 行:
ORA-01507: ??????
SQL> alter session set nls_language=american;-----------设置语言
Session altered.
SQL> select * from v$tablespace;
select * from v$tablespace
*
ERROR at line 1:
ORA-01507: database not mounted -------------提示没有mount，不能访问tablespace;
SQL> alter database mount;--------------------------------2. mount状态，可以访问database;
Database altered.
SQL> select * from v$tablespace;--------------------------查看表空间
TS# NAME INC
---------- ------------------------------ ---
12 TABLESPACE1 YES
13 TSM YES
14 S2AQ2 YES
15 S2AQTEMP YES
15 rows selected
select * from v$database;--------------------可以访问database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MOD
---------- --------- --------- ----------------- --------- ----------------------- --------- -------
1091306975 GIST 28-JUN-10 190578 28-JUN-10 1 12-MAY-02 NOARCHI
SQL> select * from dba_users;
select * from dba_users
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only -----------不能访问数据字典，数据库没有打开，只能访问固定表和视图
SQL> alter database open;--------------------------------3. database open 状态，可以查看数据字典
Database altered.
SQL> desc dictionary
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> desc v$fixed_table------------------------------查看动态性能表结构
Name Null? Type
----------------------------------------------------- -------- ----------------
NAME VARCHAR2(30)
OBJECT_ID NUMBER
TYPE VARCHAR2(5)
TABLE_NUM NUMBER
select * from v$fixed_table;----------------------------查看动态性能表的所有信息
**************************************************************************
执行存储过程
SQL> set serveroutput on
SQL> execute dbms_output.put_line('test string1')
test string1
PL/SQL procedure successfully completed.
重复执行上一条SQL的两种方法
SQL> select * from v$controlfile;-------------------------重复执行上一条SQL的两种方法
STATUS NAME
------- --------------------------------------
D:\ORACLE\ORADATA\GIST\CONTROL01.CTL
SQL> 1
1* select * from v$controlfile
SQL> /
STATUS NAME
------- --------------------------------------
D:\ORACLE\ORADATA\GIST\CONTROL01.CTL
SQL> 1
1* select * from v$controlfile
SQL> run
1* select * from v$controlfile
STATUS NAME
------- --------------------------------------
D:\ORACLE\ORADATA\GIST\CONTROL01.CTL
*************************************************
---------------------------------------------SQL*Plus执行SQL时修改前一条执行语句
SQL> select * from v$tablespace; ------最初执行的SQL
TS# NAME INC
---------- ------------------------------ ---
12 TABLESPACE1 YES
13 TSM YES
SQL> 1 ------重复上一条SQL，按1
1* select * from v$tablespace
SQL> 2 where name='TSM'; --------------增加条件语句，按2，加查询条件[where name='TSM']
SQL> run-------------------------------执行，输入run
1 select * from v$tablespace
2* where name='TSM'
TS# NAME INC
---------- ------------------------------ ---
13 TSM YES
SQL> c /name/inc/----------------------修改查询条件中的name，改为inc
2* where inc='TSM'
SQL> run
1 select * from v$tablespace
2* where inc='TSM'
where inc='TSM'
*
ERROR 位于第 2 行:
ORA-00904: "INC": 无效的标识符
SQL> c /inc/name------------------------修改查询条件中的inc为name
2* where name='TSM'
SQL> run
1 select * from v$tablespace
2* where name='TSM'
TS# NAME INC
---------- ------------------------------ ---
13 TSM YES
************************************************************************************
获得控制文件的信息
SQL> show parameter control-------------获得控制文件的信息
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string D:\oracle\oradata\gist\CONTROL
*******************************************************************************
添加控制文件 --------添加控制文件
六步骤：
1 -----先查询添加前的控制文件信息
SQL> select * from v$controlfile
2 /
STATUS NAME
------- ---------------------------------------
D:\ORACLE\ORADATA\GIST\CONTROL01.CTL
D:\ORACLE\ORADATA\GIST\CONTROL02.CTL
D:\ORACLE\ORADATA\GIST\CONTROL03.CTL
2 ----执行添加控制文件语句
SQL> alter system set control_files=
2 'D:\ORACLE\ORADATA\GIST\CONTROL01.CTL',
3 'D:\ORACLE\ORADATA\GIST\CONTROL02.CTL',
4 'D:\ORACLE\ORADATA\GIST\CONTROL03.CTL',
5 'D:\ORACLE\ORADATA\GIST\CONTROL04.CTL'
6 SCOPE=SPFILE;
系统已更改。
3 ----关闭数据库
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
4 ----添加物理控制文件
把D:\ORACLE\ORADATA\GIST\目录下的控制文件[如CONTROL01.CTL]复制一份到当前目录，
改名为CONTROLO4.CTL
5 ----启动数据库
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
6 ----重新查询，此时的结果集中就多了一个控制文件CONTROL04.CTL
SQL> select * from v$controlfile;
STATUS NAME
------- -----------------------------------------
D:\ORACLE\ORADATA\GIST\CONTROL01.CTL
D:\ORACLE\ORADATA\GIST\CONTROL02.CTL
D:\ORACLE\ORADATA\GIST\CONTROL03.CTL
D:\ORACLE\ORADATA\GIST\CONTROL04.CTL
7 ----修改D:\oracle\admin\gist\pfile目录中的init.ora文件
修改control_files中添加D:\oracle\oradata\gist\CONTROL04.CTL
# File Configuration
###########################################
control_files=("D:\oracle\oradata\gist\CONTROL04.CTL","D:\oracle\oradata\gist\CONTROL01.CTL", "D:\oracle\oradata\gist\CONTROL02.CTL", "D:\oracle\oradata\gist\CONTROL03.CTL")
8 ----关闭数据库
shutdown immediate
9 ----sartup pfile="d:\oracle\ora92/database\init.ora";
******************************************************************
------------------------------------------------------------备份控制文件的两种方式
1. ----在Oracle运行中动态备份二进制的控制文件
SQL> alter database backup controlfile to 'c:\controlfile_bak.bak
2. ----翻译创建控制文件的脚本信息
SQL> alter database backup controlfile to trace;
执行后会在D:\oracle\admin\gist\udump目录下生成gist_ora_3416.trc文件，是创建控制文件的脚本信息，用写字板可以直接打开
控制文件生成脚本文件存放目录
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
user_dump_dest string D:\oracle\admin\gist\udump
*********************************************************************
-------------------------------------------------查看存档日志归档模式
SQL> archive log list
数据库日志模式 非存档模式
自动存档 禁用
存档终点 D:\oracle\ora92\RDBMS
最早的概要日志序列 35
当前日志序列 37
自动存档设为启用
SQL> alter system archive log start;
转换日志记录组
SQL> alter system switch logfile;
强制同步
SQL> alter system checkpoint;
添加日志文件
SQL> alter database add logfile group 6
2 'd:\oracle\oradata\gist\redo06.ora' size 10m;
查看日志文件
select * from v$log;-----------------查看日志组
添加日志成员log member
SQL> alter database add logfile member
SQL> 1
1* alter database add logfile member
SQL> 2 'd:\oracle\oradata\gist\redo06a.ora'
SQL> run
1 alter database add logfile member
2* 'd:\oracle\oradata\gist\redo06a.ora'
'd:\oracle\oradata\gist\redo06a.ora'
*
ERROR 位于第 2 行:
ORA-00946: 缺少 TO 关键字
SQL> a to group 6-----------------------------------a 的意思代表在上条SQL后面追加语句如加to group 6
2* 'd:\oracle\oradata\gist\redo06a.ora'to group 6
SQL> run
1 alter database add logfile member
2* 'd:\oracle\oradata\gist\redo06a.ora'to group 6
数据库已更改。
SQL> set wrap off
SQL> set linesize 100
SQL> select * from v$logfile;-----查看所有日志组的日志成员
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -------------------------------------
3 ONLINE D:\ORACLE\ORADATA\GIST\REDO03.LOG
2 STALE ONLINE D:\ORACLE\ORADATA\GIST\REDO02.LOG
1 STALE ONLINE D:\ORACLE\ORADATA\GIST\REDO01.LOG
6 ONLINE D:\ORACLE\ORADATA\GIST\REDO06.ORA
6 INVALID ONLINE D:\ORACLE\ORADATA\GIST\REDO06A.ORA--------添加的日志member
删除日志文件组
alter database drop logfile group 6;
注意：只能删除控制文件的信息，不能删除物理文件
D:\oracle\oradata\gist目录中的REDO06A.ORA及REDO06.ORA依然存在
约束条件:
current log group当前日志组不能删
active log group 活动日志组不能删
not archived log group 未归档日志组不能删
删除日志组成员
限制条件：当前日志组的成员不能删除，日志组最少要有一个成员
SQL> alter database drop logfile member 'D:\ORACLE\ORADATA\GIST\REDO06A.ORA';
清空日志文件-----也称重新初始化日志文件
alter database clear logfile group 2;
alter database clear unarchived logfile group 5;----------------清空未归档日志组
日志文件更名
先将要更名的日志文件的物理文件重命名，再用命令修改------当前日志组文件不能重命名
alter database rename file 'D:\ORACLE\ORADATA\GIST\REDO06.ORA'
to 'D:\ORACLE\ORADATA\GIST\REDO06a.ORA'
SQL> show parameter db_create---------------查看在线日志文件能做多少个多功
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
db_create_file_dest string d:\oracle\oradb
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_online_log_dest_1='d:\oracle\oralog';
系统已更改。
SQL> alter database add logfile group 6;---------在新目录中添加日志文件
alter database add logfile group 6
*
ERROR 位于第 1 行:
ORA-01184: 日志文件组6已经存在
SQL> 1
1* alter database add logfile group 6
SQL> c /6/5
1* alter database add logfile group 5
SQL> run
1* alter database add logfile group 5
数据库已更改。
会在d:\oracle\oralog目录里自动添加O1_MF_5_6C976N00_.LOG日志文件，初始大小为100M
删除日志文件组同时删除物理文件
alter database drop logfile group 5;
53：54
用来数据恢复
非归档日志模式：只能做整体备份，而且要在离线状态下才能备份offline 冷备份
归档日志模式：可以做局部的备份，而且可在线状态下备份。online 热备份
数据库缺省为非归档模式
------------------------查看日志模式
SQL> archive log list
数据库日志模式 非存档模式
自动存档 禁用
存档终点 D:\oracle\ora92\RDBMS
最早的概要日志序列 37
当前日志序列 40
归档日志模式转换到非归档日志模式步骤：
1. shutdown immediate
2. startup mount
3. alter database noarchivelog;---------------归档转到非归档
或者是alter database archivelog;--------------非归档转到归档
SQL> alter system archive log start;----------"自动存档"设为"启用"
SQL> alter system archive log stop;-----------"自动存档"设为"禁用"
4. alter database open;
查看有多少归档日志文件
select * from v$archived_log;
查看归档日志参数
show parameter log_archive;
使用日志文件进行数据恢复挖掘：
步骤：
1. ------------设定文件目录。修改初始参数文件d:\oracle\admin\gist\pfile\init.ora
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
#2010.10.13 新增，作用：对日志文件进行分析
utl_file_dir=d:\oracle\admin\gist\cdump
增加utl_file_dir=d:\oracle\admin\gist\cdump
2. -------------重启数据库
SQL> shutdown immediate
SQL> startup;
3. -------------建立目录文件
SQL> desc dbms_logmnr;
SQL> desc dbms_logmnr_d;
如果以上两条指令执行无效，则执行下面的构建脚本[创建标准数据包]：
SQL> start d:\oracle\ora92\rdbms\admin\catproc.sql;----------------运行脚本
SQL> desc dbms_output;
更改部分数据作为参考
SQL> update scott.emp
2 sal=801
3 where empno=7369;
sal=801
*
ERROR 位于第 2 行:
ORA-00971: 缺少 SET 关键字
SQL> c /sal/set sal/
2* set sal=801
SQL> run
1 update scott.emp
2 set sal=801
3* where empno=7369
已更新 1 行。
SQL> commit;
查看当前日志组
select * from v$log;
当前日志组文件目录为D:\oracle\oradata\gist\REDO02.LOG
4. -------------使用build存储过程来构建数据字典文件
execute dbms_logmnr_d.build('gistdict.ora','d:\oracle\admin\gist\cdump');
5. -------------加入分析日志文件
SQL> execute dbms_logmnr.add_logfile('d:\oracle\oradata\gist\redo02.log',dbms_logmnr.new);
SQL> desc dbms_logmnr;
6. ------------开始分析
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\admin\gist\cdump\gistdict.ora');
************************************************************************************
ORACLE9i启动（无论是手动启动或自动启动）都采用服务器参数文件（即spfilesid.ora文件）来启动数据库实例。Spfilesid.ora文件是一个二进制的文件。是在数据库创建期间由系统自定创建的
查看参数文件的位置
SQL> show parameter spfile;
or show parameter file;
查看参数文件中的值D:\oracle\ora92\database\SPFILEGIST.ORA
SQL> show parameter db_block_size;
通过SPFILE初始化参数文件SPFILEGIST.ORA创建PFILE初始化参数文件initSID.ora文件
SQL> create pfile='D:\oracle\ora92\database\initGIST.ora'
2 from spfile='D:\oracle\ora92\database\SPFILEGIST.ora';
initSID.ora可以用编辑器打开修改
指定启动的初始化参数文件
SQL> startup pfile=D:\oracle\ora92\database\initGIST.ora
**********************************************************
表空间
分为系统表空间与非系统表空间
tablespace----------------logical area逻辑区域，一个段只能放在一个表空间里，由一个或多个物理数据文件组成
segment段-----------------specific logical structure occupied space collection某个特定的逻辑结构所占用的空间集合
分为四类段：table表段/index索引段/temp临时段/rollback回滚段，段可跨数据文件，段在申请空间分配时是通过分区得到的
extent分区----------------allocate unit是一个分配单位，分区的单位一定是块的整数倍，区不能跨数据文件
block块-------------------use&store unit是Oracle中的使用和存储单位，一个分区由多个物理块组成
select * from v$tablespace;
查看数据文件
select * from v$datafile;
--------------------------------------------------------------------------------查看表空间与数据文件及用户的对应关系
select t1.name, t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;
SQL> set wrap off
SQL> set linesize 1000
SQL> 1
1* select t1.name,t2.name
SQL> 2 from v$tablespace t1, v$datafile t2
SQL> 3 where t1.ts#=t2.ts#;
SQL> run
1 select t1.name,t2.name
2 from v$tablespace t1, v$datafile t2
3* where t1.ts#=t2.ts#
NAME NAME
------------------------------ --------------------------------------------
SYSTEM D:\ORACLE\ORADATA\GIST\SYSTEM01.DBF
UNDOTBS1 D:\ORACLE\ORADATA\GIST\UNDOTBS01.DBF
CWMLITE D:\ORACLE\ORADATA\GIST\CWMLITE01.DBF
DRSYS D:\ORACLE\ORADATA\GIST\DRSYS01.DBF
EXAMPLE D:\ORACLE\ORADATA\GIST\EXAMPLE01.DBF
INDX D:\ORACLE\ORADATA\GIST\INDX01.DBF
ODM D:\ORACLE\ORADATA\GIST\ODM01.DBF
TOOLS D:\ORACLE\ORADATA\GIST\TOOLS01.DBF
USERS D:\ORACLE\ORADATA\GIST\USERS01.DBF
XDB D:\ORACLE\ORADATA\GIST\XDB01.DBF
TABLESPACE1 D:\ZHOUXU\AA.DBF
NAME NAME
------------------------------ --------------------------------------------
TSM D:\ZHOUXU\BB.DBF
S2AQ2 D:\ORACLE\ORADATA\GIST\S2AQ2.ORA
已选择13行。
为表空间添加数据文件
SQL> alter tablespace tsm
2 add datafile 'd:\zhouxu\tsm.dbf' size 10m;
查看系统回滚段
SQL> select * from dba_rollback_segs;
22:00
*******************************************************************
表空间及数据文件
创建表空间
一 创建字典管理的表空间
SQL> create tablespace dbtel
2 datafile 'd:\oracle\oradata\gist\dbtel.dbf' size 5m
3 extent management dictionary
4 default storage(
5 initial 100k
6 next 100k,
7 pctincrease 10)
8 offline;
next 100k,
*
ERROR 位于第 6 行:
ORA-02143: 无效的 STORAGE 选项
SQL> 6 -----------------------转到第六行SQL，进行修改
6* next 100k,
SQL> c /,/--------------------修改第六行SQL，去掉逗号[,]
6* next 100k
SQL> run
1 create tablespace dbtel
2 datafile 'd:\oracle\oradata\gist\dbtel.dbf' size 5m
3 extent management dictionary
4 default storage(
5 initial 100k
6 next 100k
7 pctincrease 10)
8* offline
create tablespace dbtel
*
ERROR 位于第 1 行:
ORA-12913: 无法创建字典管理的表空间
出错原因：
9i的第一个版本默认system表空间是字典管理的[dmt]，9.2.0.1就默认是本地管理了[lmt]。要改只能安装的时候选择
查看ORACLE版本
SQL> select * from v$version;
二 创建本地管理的表空间
SQL> CREATE TABLESPACE "DBTEL"
2 LOGGING
3 DATAFILE 'D:\ORACLE\ORADATA\GIST\DBTEL.ora' SIZE 5M EXTENT
4 MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
5 /
表空间已创建。
SQL> create tablespace dbtel2
2 datafile 'd:\oracle\oradata\gist\dbtel2.dbf' size 5m
3 extent manager local
4 default storage(initial 100k)
5 /
extent manager local
*
ERROR 位于第 3 行:
ORA-25141: 无效的 EXTENT MANAGEMENT 子句
SQL> 3 --------------------------第三句有误，定位到第三行SQL
3* extent manager local
SQL> c /manager/management/--------------------把manager改为management
3* extent management local
SQL> run
1 create tablespace dbtel2
2 datafile 'd:\oracle\oradata\gist\dbtel2.dbf' size 5m
3 extent management local
4* default storage(initial 100k)
create tablespace dbtel2
*
ERROR 位于第 1 行:
ORA-25143: 默认存储子句与分配策略不兼容--------------------本地管理表空间不能指定缺省的存储参数
SQL> del 4-----------------------------------------删除第四行SQL
SQL> input uniform size 1m-----------------------统一大小为1m，添加SQL语句在前句的末尾
SQL> /
表空间已创建。
SQL> create tablespace dbtel2
2 datafile 'd:\oracle\oradata\gist\dbetl2.dbf' size 5m
3 extent management local autoallocate;-----自动分配
回滚
delete scott.emp;
rollback;
------------------------------------------------------------查看回滚参数
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ----------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
-------------------------------------------------------------------------------------创建撤消表空间undo tablespace
SQL> create undo tablespace dbtel3
2 datafile 'd:\oracle\oradata\gist\dbtel3.dbf' size 5m
3 extent management local;
不能在撤消表空间中创建段
-------------------------------------------------------------------------------------删除表空间
drop tablespace dbtel3
-------------------------------------------------------------------------------------创建临时表空间
作用:进行排序，存放中间数据，使用本地管理,不能创建永久的数据对象，
SQL> create temporary tablespace dbtel4
2 tempfile 'd:\oracle\oradata\gist\dbtel4.dbf' size 5m
3 extent management local;
如果没有指定临时表空间，那么排序时会使用系统表空间
-------------------------------------------------------------------------------------指定某临时表空间为缺省临时表空间
alter database default temporary tablespace dbtel4;
不能设置缺省临时表空间为离线状态，也不能设为只读，不能删除
alter tablespace dbtel4 offline;
alter tablespace dbtel4 read only;
drop tablespace dbtel4;
-------------------------------------------------------------------------------------离线表空间，可部分关闭数据库
alter tablespace tsm offline;
-------------------------------------------------------------------------------------查看某表空间内有些什么表
select * from dba_tables where tablespace_name='TSM'
-------------------------------------------------------------------------------------表空间设为离线时，不能再访问该表空间所包含的数据文件
SQL> alter tablespace tsm offline;
SQL> select * from sys.tsm_employee;
select * from sys.tsm_employee
*
ERROR 位于第 1 行:
ORA-00376: 此时无法读取文件 12 -----------------表空间设为离线时，不能再访问该表空间所包含的数据文件
ORA-01110: 数据文件 12: 'D:\ZHOUXU\BB.DBF'
SQL> alter tablespace tsm online;
-------------------------------------------------------------------------------------删除表空间同时删除表空间的所有内容以及数据文件
SQL> drop tablespace dbtel3
2 including contents and datafiles;
-------------------------------------------------------------------------------------修改表空间的数据文件的SIZE， 临时文件不能修改
alter database datafile 'd:\oracle\oradata\gist\dbtel.ora'
resize 10m
-------------------------------------------------------------------------------------表空间增添数据文件
SQL> alter tablespace dbtel
2 add datafile 'd:\oracle\oradata\gist\dbtel3.dbf'
3 size 10m;
-------------------------------------------------------------------------------------数据文件改名
先把d:\oracle\oradata\gist\目录下的原始文件名[dbtel3.dbf]文件名改为要修改的文件名[dbtel3_update.dbf]，再执行下面的command，
因为command只修改控制文件里的信息，不会修改物理文件名，所以物理文件名在手动修改
1 alter tablespace
2 dbtel
3 rename datafile
4 'd:\oracle\oradata\gist\dbtel3.dbf' -----------old filename
5 to
6* 'd:\oracle\oradata\gist\dbtel3_update.dbf'----------new filename
如果要修改或移动临时缺省表空间或系统表空间的数据文件，要先shutdown数据库，然后再启动在mounted状态下才能修改或移动数据文件
-------------------------------------------------------------------------------------获取表空间的信息的两种方法：
1. ---------使用数据字典视图查看
select * from v$tablespace;
select * from v$datafile;
select * from v$tempfile;
2. ---------使用动态性能表查看
select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_temp_files;
*****************************************************************************
-------------------------------------------------------------------------------------保存Oracle SQL*Plus的SQL语句到指定的文本文件中
spool d:\sp_test.txt
select * from table
spool off
*****************************************************************************
-------------------------------------------------------------------------------------SQL定位要修改的行
SQL> select * from
2 dba_datafiles;
dba_datafiles
*
ERROR 位于第 2 行:
ORA-00942: 表或视图不存在
SQL> l2------------------------------------------------------------------------------定位在第2行，前面是英文line的l字母，不是数字1
2* dba_datafiles
SQL> c /datafiles/data_files/
2* dba_data_files
SQL> run
1 select * from
2* dba_data_files
********************************************************************8
段可以跨数据文件，但不能跨表空间
为数据对象分配单位
申请空间的扩展，强制为段进行空间分配: 所指定的空间一定要来自于段原有的表空间底下的数据文件
----------------------------------------------------扩展表的空间
SQL> alter table zhouxu.test3
2 allocate extent(size 1m datafile 'd:\zhouxu\aa.dbf')
3 /
alter table zhouxu.test3
*
ERROR 位于第 1 行:
ORA-03284: 数据文件d:\zhouxu\aa.dbf不是表空间TSM的成员-------------用来扩展的数据文件不能跨表空间
SQL> alter tablespace tsm
2 add datafile 'd:\oracle\oradata\gist\tsm_add.dbf'-------------添加表空间的数据文件
3 size 5m;
表空间已更改。
SQL> alter table zhouxu.test3
2 allocate extent(size 1m datafile 'd:\oracle\oradata\gist\tsm_add.dbf')
3 /
表已更改。
分区在什么样的情况下会被删除掉
1. drop table table_name ---删除表
2. truncate table table_name ---清空表
-------------------------------------------------------------------------------------释放没有用到的分区
SQL> alter table zhouxu.test3
2 deallocate unused;
-----------------------block块
show parameter db;查看内存参数
alter system set db_cache_size=10m
alter system set db_8k_size=10m
-------------------------------------------------------------------------------------段空间自动管理设置
SQL> create tablespace dbtel3
2 datafile 'd:\oracle\oradata\gist\dbtel3.dbf' size 5m
3 extent management local uniform size 1m
4 segment space management auto;
-------------------------------------------------------------------------------------利用数据字典视图查看信息
select * from dba_tablespaces;
select * from dba_segments;
select * from dba_data_files;
select * from dba_free_space;
desc dba_extents;---------------------------------------------------------------------查看表列名
desc dba_tablespaces;
*************************************************************
undo data 回滚 --------------
1. SYSTEM 系统回滚段，放在system表空间内，用于系统表改变数据的回滚。如系统表、数据字典表。
2. Non-SYSTEM 非系统回滚段
3. Deferred 延期回滚段 Oracle自动派生，用完后自动消失，查不到，保持数据一致性。
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- --------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
--------------------------------------------------------------------------------------查看当前用户
show user;
模似非正常关闭
shutdown abort
一个实例只能操控一个DBTABASE ，一个DATABASE可被多个实例操控
-------------------------------------------------------------------------------------表空间的online与offline状态设定
SQL> alter tablespace users
2 offline normal;
表空间已更改。
SQL> alter tablespace users online;
表空间已更改。
SQL> alter tablespace users offline immediate;
表空间已更改。
SQL> alter tablespace users online;
alter tablespace users online
SQL> show parameter undo;
*
ERROR 位于第 1 行:
ORA-01113: 文件 9 需要介质恢复
ORA-01110: 数据文件 9: 'D:\ORACLE\ORADATA\GIST\USERS01.DBF'
SQL> recover datafile 'D:\ORACLE\ORADATA\GIST\USERS01.DBF';------------------------------恢复介质
完成介质恢复。
SQL> alter tablespace users online;
表空间已更改。
-----------------------------------------------------------------------------------创建undo tablespace
SQL> create undo tablespace undotbs2
2 datafile 'd:\oracle\oradata\gist\undotbs2.dbf' size 5m
3 /
-------------------------------------------------------------------------------------表空间已创建。
SQL> alter system set undo_tablespace=undotbs2;
系统已更改。
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2-----自动修改D:\oracle\ora92\database\SPFILEGIST.ORA中的undo_tablespace参数。
32：
-------------------------------------------------------------------------------------查看回滚段
select * from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME
------------------------------ ------ ------------------
SYSTEM SYS SYSTEM
_SYSSMU1$ PUBLIC UNDOTBS1
_SYSSMU2$ PUBLIC UNDOTBS1
_SYSSMU3$ PUBLIC UNDOTBS1
_SYSSMU4$ PUBLIC UNDOTBS1
_SYSSMU5$ PUBLIC UNDOTBS1
_SYSSMU6$ PUBLIC UNDOTBS1
_SYSSMU7$ PUBLIC UNDOTBS1
_SYSSMU8$ PUBLIC UNDOTBS1
_SYSSMU9$ PUBLIC UNDOTBS1
_SYSSMU10$ PUBLIC UNDOTBS1
SEGMENT_NAME OWNER TABLESPACE_NAME
------------------------------ ------ ------------------
_SYSSMU11$ PUBLIC UNDOTBS2
_SYSSMU12$ PUBLIC UNDOTBS2
_SYSSMU13$ PUBLIC UNDOTBS2
_SYSSMU14$ PUBLIC UNDOTBS2
_SYSSMU15$ PUBLIC UNDOTBS2
_SYSSMU16$ PUBLIC UNDOTBS2
_SYSSMU17$ PUBLIC UNDOTBS2
_SYSSMU18$ PUBLIC UNDOTBS2
_SYSSMU19$ PUBLIC UNDOTBS2
_SYSSMU20$ PUBLIC UNDOTBS2
-------------------------------------------------------------------------------------undo表空间添加数据文件
alter tablespace undotbs2
add datafile 'd:\oracle\oradata\gist\undotbs3.dbf' size 2m
-------------------------------------------------------------------------------------删除undo表空间，不能删除磁盘上的物理文件
SQL> drop tablespace undotbs2;
表空间已丢弃。
-------------------------------------------------------------------------------------删除undo表空间，同时删除磁盘上的物理文件
SQL> drop tablespace undotbs3
2 including contents and datafiles;
表空间已丢弃。
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR 位于第 1 行:
ORA-30013: 撤消表空间 'UNDOTBS1' 当前正在使用中--------------------------------------激活的表空间不能删除
-------------------------------------------------------------------------------------设置表空间的在线或离线
alter tablespace users online;
alter tablespace users offline;
-------------------------------------------------------------------------------------重新使用已存在的数据文件建立表空间
SQL> create undo tablespace undotbs3
2 datafile 'd:\oracle\oradata\gist\undotbs3.dbf' size 2m;
create undo tablespace undotbs3
*
ERROR 位于第 1 行:
ORA-01119: 创建数据库文件 'd:\oracle\oradata\gist\undotbs3.dbf' 时出错----------------提示该数据文件已经存在
ORA-27038: skgfrcre: 文件存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在
SQL> del 2--------------删除第二行SQL
SQL> input datafile 'd:\oracle\oradata\gist\undotbs3.dbf' reuse;
SQL> run
1 create undo tablespace undotbs3
2* datafile 'd:\oracle\oradata\gist\undotbs3.dbf' reuse------------------------------使用reuse重新使用数据文件
表空间已创建。
---------------------------------------------------------------------------------------Oracle用户以normal身份登录
SQL> conn zhouxu;
请输入口令: ******
或者
SQL> conn zhouxu/zhouxu
Oracle9i新特征-------------------------------------------------------------------回闪: 执行提交后，仍能查看到旧数据
SQL> alter session set nls_language=american;
SQL> execute dbms_flashback.enable_at_time('17-OCT-10:12:10:00 am'); ---退回到执行前的时间段
PL/SQL procedure successfully completed.
示例：
SQL> conn zhouxu/zhouxu
已连接。
SQL> select * from test2; --------删除前的表数据
ID
----------
55
SQL> delete test2 where id = 55;---删除一行数据
已删除 1 行。
SQL> commit;-----------------------提交
提交完成。
SQL> select * from test2;----------查询提交后的表数据
未选定行
SQL> alter session set nls_date_language=american;-----更改当前语言
会话已更改。
SQL> execute dbms_flashback.enable_at_time('17-OCT-10:12:51:00 am');----退回到执行删除前的时间段，执行回闪
PL/SQL 过程已成功完成。
SQL> select * from test2;----------再查询，可查看删除前的数据，但这些数据已不在当前表内，只是查看作用
ID
----------
55
SQL> execute dbms_flashback.disable;--------取消回闪操作
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800-------------------回闪时间10800秒
undo_suppress_errors boolean TRUE
undo_tablespace string UNDOTBS1
undo 数据统计
SQL> select end_time, begin_time,undoblks
2 from v$undostat;
END_TIME BEGIN_TIME UNDOBLKS
---------- ---------- ----------
17-10月-10 17-10月-10 8
17-10月-10 17-10月-10 18
17-10月-10 17-10月-10 18
17-10月-10 17-10月-10 17
17-10月-10 17-10月-10 19
17-10月-10 17-10月-10 16
17-10月-10 17-10月-10 18
17-10月-10 17-10月-10 18
17-10月-10 16-10月-10 16
16-10月-10 16-10月-10 18
16-10月-10 16-10月-10 23
查看UNDO信息
1. 所有信息
select * from dba_rollback_segs;
2. 回滚段的名称
select * from v$rollname;
3. 统计信息
select * from v$rollstat;
4. 会话信息
select * from v$session;
5. 事物信息
select * from v$transaction;
手工管理回滚段-------------不利用表空间
SQL> show parameter transactions;
NAME TYPE VALUE
------------------------------------ ----------- ----------
transactions integer 187
transactions_per_rollback_segment integer 5
SQL> show parameter rollback;
NAME TYPE VALUE
------------------------------------ ----------- -----------
fast_start_parallel_rollback string LOW
max_rollback_segments integer 37
rollback_segments string
transactions_per_rollback_segment integer 5
*****************************************************************************
管理表
常规表regular table;
分区表partitioned table;
索引表index-organized table;
群集表cluster table; 多个栏位占用一个段
数据类型
Data type
user-defiled built-in
scalar collection relationship
CHAR(N) max length 2000字节, NCHAR(N)
VARCHAR2(N) max length 40000字节,
NVARCHAR2(N)
NUMBER(P,S)
DATE 固定七字节长度的格式来进行保存----c\y\m\d\h\m\s
TIMESTAMP
RAW(N)
BLOB, CLOB,NCLOB, BFILE
LONG, LONG RAW
ROWID, UROWID
查行ID---rowid
select rowid form tablename;
行ID分为扩展行ID及限制行ID;
Extended ROWID Format 扩展行ID： 64进制18位的字符串
Restricted ROWID Format限制行ID：
查看ROWID 相关信息
行ID函数 function --------------
SQL> desc dbms_rowid;
FUNCTION ROWID_BLOCK_NUMBER RETURNS NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_CREATE RETURNS ROWID
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN
RELATIVE_FNO NUMBER IN
BLOCK_NUMBER NUMBER IN
ROW_NUMBER NUMBER IN
PROCEDURE ROWID_INFO
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
FUNCTION ROWID_OBJECT RETURNS NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_RELATIVE_FNO RETURNS NUMBER
参数名称 类型 输入/输出默认值？
desc dbms_rowid;
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_ROW_NUMBER RETURNS NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_TO_ABSOLUTE_FNO RETURNS NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
FUNCTION ROWID_TO_EXTENDED RETURNS ROWID
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TO_RESTRICTED RETURNS ROWID
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TYPE RETURNS NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_VERIFY RETURNS NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
1 select rowid, dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid)
2* from tsm_employee
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
------------------ ------------------------------------ ----------------------------------
AAAHZlAAMAAAAASAAA 18 0
AAAHZlAAMAAAAASAAB 18 1
AAAHZlAAMAAAAASAAC 18 2
AAAHZlAAMAAAAASAAD 18 3
AAAHZlAAMAAAAASAAE 18 4
AAAHZlAAMAAAAASAAF 18 5
AAAHZlAAMAAAAASAAG 18 6
AAAHZlAAMAAAAASAAH 18 7
AAAHZlAAMAAAAASAAI 18 8
AAAHZlAAMAAAAASAAJ 18 9
AAAHZlAAMAAAAASAAK 18 10
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
------------------ ------------------------------------ ----------------------------------
AAAHZlAAMAAAAASAAL 18 11
AAAHZlAAMAAAAASAAM 18 12
AAAHZlAAMAAAAASAAN 18 13
AAAHZlAAMAAAAASAAO 18 14
AAAHZlAAMAAAAASAAP 18 15
AAAHZlAAMAAAAASAAQ 18 16
AAAHZlAAMAAAAASAAR 18 17
AAAHZlAAMAAAAASAAS 18 18
AAAHZlAAMAAAAASAAT 18 19
AAAHZlAAMAAAAASAAU 18 20
行结构:
Row header 行头
Column length 行长度
Column value 行值
创建表
create table test2
(id int,
iname varchar2(20) not null,
fname varchar2(20) constraint ck_1 check(fname like 'k%'),
empdate date default sysdate)
tablespace tsm;
临时表的创建
创建Oracle临时表，可以有两种类型的临时表：
会话级的临时表
事务级的临时表。
1） 会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断（truncate table，即数据清空）了。会话级的临时表创建方法：
-----创建一个与tsm_employee表相同结构的临时表，临时表中不要tsm_employee表中数据
SQL> create global temporary table tsm_employee_temp
2 on commit delete rows
3 as
4 select * from tsm_employee;
------------------------------------------------------------
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Preserve Rows；
举例：
create global temporary table Student
(Stu_id Number(5),
Class_id Number(5),
Stu_Name Varchar2(8),
Stu_Memo varchar2(200)) on Commit Preserve Rows ;
2） 事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：
-----创建一个与tsm_employee表相同结构的临时表，临时表中保留tsm_employee表中数据
SQL> create global temporary table tsm_employee_temp
2 on commit preserve rows
3 as
4 select * from tsm_employee;
-----------------------------------------------------------
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Delete Rows；
举例：
create global temporary table Classes
(Class_id Number(5),
Class_Name Varchar2(8),
Class_Memo varchar2(200)) on Commit delete Rows ;
3） 两中类型临时表的区别
会话级临时表采用on commit preserve rows；而事务级则采用on commit delete rows；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是commit、rollback或者是会话结束，临时表中的数据都将被截断
4）什么时候使用临时表
a、当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中
b、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
修改表创建参数
1 create table test3
2 (id int)
3 tablespace tsm
4 storage(initial 100k
5 next 100k
6 pctincrease 0
7 minextents 1
8 maxextents 10)
9 pctfree 10
10* pctused 40
QL> alter table test3
2 pctfree 20
3 pctused 50
为表指定分配空间
SQL> alter table zhouxu.test2
2 allocate extent(size 1m datafile 'd:\zhouxu/tsm.dbf');--------------------.dbf文件必须来自于这个表[test3]所在的表空间[TSM]
所分配的分区不会影响到oracle原先分配分区的顺序
手动释放表空间---------high water mark高水印
SQL> alter table zhouxu.test2
2 deallocate unused; -----------------------释放所有未使用表空间
表已更改。
SQL> alter table zhouxu.test2
2 deallocate unused keep 0;-----------------在高水印上保留多少空间
表已更改。
SQL> alter table zhouxu.test2
2 deallocate unused keep 2m;
alter table zhouxu.test2
*
ERROR 位于第 1 行:
ORA-03230: 段只包含 6 块在高水印之上的未使用空间-----提示只有6块空间，没有2M
SQL> alter table zhouxu.test2
2 deallocate unused keep 8k; ----------------修改为8k
----------------------------------------------------------把表从一个表空间移到另一个表空间，可以重新组织分区
SQL> alter table tsm_employee move tablespace dbtel2;---------把tsm_employee从system表空间移到dbtel2表空间
表已更改。
SQL> create index empindex on tsm_employee(name) tablespace dbtel2;--在表的name字段上创建索引
索引已创建。
SQL> select * from tsm_employee
2 where name like '周%';
EMP_NO NAME POSITION MGR SALARY CREATE_DA
---------- ------------------------- -------------------- ---------- ---------- ---------
2007080713 周海其 项目经理 15000 28-6月 -1
alter table tsm_employee move tablespace system---把表移回system表空间
表已更改。
SQL> select * from tsm_employee
2 where name like '周%';
select * from tsm_employee
*
ERROR 位于第 1 行:
ORA-01502: 索引'SYS.EMPINDEX'或这类索引的分区处于不可用状态-----索引不能使用，要重构
SQL> alter index empindex rebuild;----重构索引
索引已更改。
SQL> select * from tsm_employee
2 where name like '周%';
EMP_NO NAME POSITION MGR SALARY CREATE_DA
---------- ------------------------- -------------------- ---------- ---------- ---------
2007080713 周海其 项目经理 15000 28-6月 -1
清空表
truncate tabel tablename;----为数据定义语言
释放表空间，同时清除索引。
删除表，同时删除该表的主外键约束关联
SQL> drop table zhouxu.test2
2 cascade constraint;
从表中删除栏位[column]
SQL> alter table test2
2 add(lname varchar2(20) default 'dbtel');------------给表添加列
方法一：即刻删除，如果数据量在会占用大量时间
SQL> alter table test2
2 drop column lname;---------------------删除列
方法二：先设为该列失效，空闲时再删除
SQL> alter table test2
2 set unused column lname; ----------先设lname字段失效
SQL> alter table test2
2 drop unused columns;---------------再删除未使用的列
方法三：防止系统出错的删除栏位语句
SQL> alter table test3 drop unused columns checkpoint 1000;
SQL> alter table test2 drop columns continue checkpoint 1000;
获得表信息
desc dba_tables;
select * from dba_tables where owner = 'ZHOUXU'
desc dba_objects;
select * from dba_objects where owner = 'ZHOUXU'
索引*********************************************************
数据库中只有表和索引能申请空间分配
索引与表的关系是书与目录的关系，记录着表的行号信息，是独立于表的指针
如果系统是面向写的，索引是越少越好，
如果系统是面向读的，索引是越多越好。
逻辑索引：
物理索引：
b-tree索引组成：用于取值惟一性比较高的列，适合用于在线交易 root-branch-leaf
leaf:1.index entry header
2.key column length
3.key column value
4.ROWID
SQL> create index empindex on tsm_employee(name) tablespace dbtel2;--没有任何声明的是b-tree索引
bitmap索引组成：用于取值惟一性比较低的列，适合用于数据仓库
1.key
2.start ROWID
3.end ROWID
4.bitmap
SQL> create bitmap index emp_index
2 on tsm_employee(position)
3 tablespace system;---------------------创建位图索引
SQL> create index empindex
2 on tsm_employee(position)
3 tablespace tsm
4 pctfree 20
5 storage(
6 initial 100k
7 next 100k
8 )nologging;-----------------------------设置不要日志
SQL> show parameter create_bit;
NAME TYPE VALUE
------------------------------------ ----------- -----------
create_bitmap_area_size integer 8388608
SQL> create index emp_index
2 on tsm_employee(emp_no)
3 reverse;----------------------反转索引，适用在顺序的序列号上的索引
改变索引的存储参数
SQL> alter index emp_index
2 allocate extent(size 100k
3 datafile 'd:\oracle\oradata\gist\dbtel3.dbf');----------手动分配表空间
alter index emp_index
*
ERROR 位于第 1 行:
ORA-03284: 数据文件d:\oracle\oradata\gist\dbtel3.dbf不是表空间SYSTEM的成员
SQL> alter index emp_index
2 allocate extent(size 100k
3 datafile 'd:\oracle\oradata\gist\system01.dbf');
索引已更改。
SQL> alter index emp_index
2 decallocate unused;
decallocate unused-------------------------释放没有使用的表空间
*
索引重建
两种方式：1.online
2.rebuild
SQL> alter index emp_index
2 rebuild tablespace tsm;-------------移动索引至tsm表空间
b-tree<------------>bitmap两种索引不能相互转换
只能反转与非反转之间转换
SQL> alter index emp_index
2 rebuild
3 reverse;
索引已更改。
SQL> alter index emp_index
2 rebuile
3 bitmap;
rebuile
*
ERROR 位于第 2 行:
ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效-------------------b-tree不能转为bitmap
在线重构索引
QL> alter index emp_index rebuild online;
索引碎片整理
SQL> alter index emp_index coalesce;
检查索引的有效性
SQL> analyze index emp_index validate structure
索引已分析
SQL> desc index_stats;
名称 是否为空? 类型
----------------------------------------------------- -------- ----------------
HEIGHT NUMBER
BLOCKS NUMBER
NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
LF_ROWS NUMBER
LF_BLKS NUMBER
LF_ROWS_LEN NUMBER
LF_BLK_LEN NUMBER
BR_ROWS NUMBER
BR_BLKS NUMBER
BR_ROWS_LEN NUMBER
BR_BLK_LEN NUMBER
DEL_LF_ROWS NUMBER
DEL_LF_ROWS_LEN NUMBER
DISTINCT_KEYS NUMBER
MOST_REPEATED_KEY NUMBER
BTREE_SPACE NUMBER
USED_SPACE NUMBER
PCT_USED NUMBER
ROWS_PER_KEY NUMBER
BLKS_GETS_PER_ACCESS NUMBER
PRE_ROWS NUMBER
PRE_ROWS_LEN NUMBER
OPT_CMPR_COUNT NUMBER
OPT_CMPR_PCTSAVE NUMBER
查看当前用户在某张表上的索引
SQL> select * from user_indexes
2 where table_name = 'TSM_EMPLOYEE';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ----------
EMPINDEX NORMAL SYS TSM_EMPLOY
EMP_INDEX NORMAL/REV --反转索引 SYS TSM_EMPLOY
增加对索引的监视
SQL> alter index empindex
2 monitoring usage;
取消对索引的监视
SQL> alter index empindex
2 nomonitoring usage;
----------------------------------------------------------------------------------从数据字典查看索引信息
SQL> select * from dba_indexes;
SQL> select * from dba_ind_columns;
SQL> select * from v$object_usage;
SQL> select * from dba_ind_expressions;----函数索引
查看某表上的索引
SQL> select * from dba_ind_columns
2 where table_name = 'TSM_EMPLOYEE';
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_N
------------------------------ ------------------------------ ------------------------------ -------
SYS EMPINDEX SYS TSM_EMP
SYS EMP_INDEX SYS TSM_EMP
查询可以访问的索引
select * from all_indexes;
函数索引举例
SQL> create index test2_index2
2 on test2(salary-bonus);------------列salary减去bonus
SQL> select * from test2 where (salary-bonus)>1000;---------使用
SQL> select * from dba_ind_expressions where table_name = 'TEST2';----查询函数索引
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_N
------------------------------ ------------------------------ ------------------------------ -------
ZHOUXU TEST2_INDEX2 ZHOUXU TEST2
1、查找表的所有索引（包括索引名，类型，构成列）：
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表
2、查找表的主键（包括名称，构成列）：
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表
3、查找表的唯一性约束（包括名称，构成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表
4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表
查询外键约束的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
5、查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表
*****************************************************************************************
维护数据完整性
约束种类：
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIEG KEY
5. CHECK
SQL> alter table test2
2 add constraint pk_1 primary key(id);--------添加主键约束, pk_1是约束名，如没指定，系统会自动生成一个约束名
SQL> alter table test2
2 add constraint uk_1 unique(id, lname);------添加复合惟一约束
SQL> alter table test2
2 drop constraint uk_1;-----------------------删除约束
SQL> alter table test3
2 add constraint fk_test2 foreign key(test2_id)
references test2(id);-------添加外键，test3的test2_id列是test2表id列的外键
SQL> alter table test3 modify city not null;---------------非空约束
SQL> alter table test3 modify city null;-------------------取消非空约束
查看当前用户约束列表
desc user_constraints;
SQL> select owner, constraint_name, constraint_type, table_name from user_constraints;
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------------------
ZHOUXU PK_1 P TEST2
ZHOUXU PK_TEST3 P TEST3
ZHOUXU FK_TEST2 R TEST3
ZHOUXU SYS_C003867 P TEST4 ------默认主键名
对已有数据的表添加检查约束
SQL> alter table test3
2 add constraint ck_test3_city check(city like 'b%');
add constraint ck_test3_city check(city like 'b%')
*
ERROR 位于第 2 行:
ORA-02293: 无法验证 (ZHOUXU.CK_TEST3_CITY) - 违反检查约束条件--------------------------city列已有不在约束范围内的数据
SQL> input enable novalidate;--------------------------取消添加约束时对已有数据的验证，新添加的数据启动检查约束
SQL> run
1 alter table test3
2 add constraint ck_test3_city check(city like 'b%')
3* enable novalidate
表已更改。
使用disable novalidate添加检查约束
SQL> alter table test3
2 add constraint ck_test3_city check(city like 'z%');
add constraint ck_test3_city check(city like 'z%')
*
ERROR 位于第 2 行:
ORA-02293: 无法验证 (ZHOUXU.CK_TEST3_CITY) - 违反检查约束条件----表中已有不适合数据
SQL> a disable novalidate; --------------------------a 代表在当前的SQL上追加信息
2* add constraint ck_test3_city check(city like 'z%')disable novalidate
SQL> run;
1 alter table test3
2* add constraint ck_test3_city check(city like 'z%')disable novalidate-----已有数据不检查，新进来的数据检查
表已更改。
