oracle学习总结
来源：互联网 发布：微信麻将源码 编辑：程序博客网 时间：2024/05/16 06:47
1. 查看oracle上的安装用户，可以使用模糊查询【all_users】
SQL> select username from all_users where username like '%hk';
no rows selected
SQL> select username from all_users where username like '%HK%';
USERNAME
LBIDW_HK
LBIMK_HK
2. 查看某个用户拥有的表【dba_all_tables】
SQL> select TABLE_NAME from dba_all_tables where OWNER ='CBPDBYD';
TABLE_NAME
OCS_RECHARGELOG_TMP
OCS_ACCTMGRLOG_TMP
OCS_SUBFNLIST_TMP
OCS_SUBFNLIST
ACCOUNT
3. 查看某张表对应存放的目录
SQL> select default_tablespace from dba_users where username ='CBPDBYD';
DEFAULT_TABLESPACE
CBP_WORKDBS
SQL> select file_name from dba_data_files where TABLESPACE_NAME = 'CBP_WORKDBS';
FILE_NAME
/tellin/oracle/app/oradata/oracle/CBP_WORKDBS.dbf
4. 查看某个表空间占用的大小
表空间 (tablespace)是数据库的逻辑划分，每个数据库至少有一个表空间 (称作SYSTEM表空间)。一个表空间只能属于一个数据库。
SQL> select sum(BYTES)/1024/1024 from dba_segments where TABLESPACE_NAME = 'CBP_WORKDBS';
SUM(BYTES)/1024/1024
--------------------
472.5
5. 导出表结构
exp cbpdbyd/cbpdbyd file = ACCOUNT.dmp ROWS=N compress=N tables ="ACCOUNT" log ="exp.log" ;
6. 导出表数据
imp cbpdbyd/cbpdbyd fromuser=sysdb touser=cbpdbyd rows=y commit=y buffer=65536 feedback=100000 ignore=y volsize=0 file=cbpdbyd.dmp log=imp.log;
7. Oracle建立属于自己的数据库
创建表空间【一个逻辑的概念，是属于所有用户共享的】
create tablespace longs datafile ' /tellin/oracle/test.dbf' size 100M autoextend on;
创建用户【为用户指定默认的表空间】
create user abc identified by abc default tablespace longs;
8. 查看oracle上的安装用户，可以使用模糊查询【all_users】
SQL> select username from all_users where username like '%hk';
no rows selected
SQL> select username from all_users where username like '%HK%';
USERNAME
LBIDW_HK
LBIMK_HK
9. 查看某个用户拥有的表【dba_all_tables】
SQL> select TABLE_NAME from dba_all_tables where OWNER ='CBPDBYD';
TABLE_NAME
OCS_RECHARGELOG_TMP
OCS_ACCTMGRLOG_TMP
OCS_SUBFNLIST_TMP
OCS_SUBFNLIST
ACCOUNT
10. 查看某张表对应存放的目录
SQL> select default_tablespace from dba_users where username ='CBPDBYD';
DEFAULT_TABLESPACE
CBP_WORKDBS
SQL> select file_name from dba_data_files where TABLESPACE_NAME = 'CBP_WORKDBS';
FILE_NAME
/tellin/oracle/app/oradata/oracle/CBP_WORKDBS.dbf
11. 查看某个表空间占用的大小
SQL> select sum(BYTES)/1024/1024 from dba_segments where TABLESPACE_NAME = 'CBP_WORKDBS';
SUM(BYTES)/1024/1024
--------------------
472.5
12. 导出表结构
exp cbpdbyd/cbpdbyd file = ACCOUNT.dmp ROWS=N compress=N tables ="ACCOUNT" log ="exp.log" ;
13. 导出表数据
imp cbpdbyd/cbpdbyd fromuser=sysdb touser=cbpdbyd rows=y commit=y buffer=65536 feedback=100000 ignore=y volsize=0 file=cbpdbyd.dmp log=imp.log;
14. Oracle建立属于自己的数据库
创建表空间【一个逻辑的概念，是属于所有用户共享的】
create tablespace longs datafile ' /tellin/oracle/test.dbf' size 100M autoextend on;
创建用户【为用户指定默认的表空间】
create user abc identified by abc default tablespace longs;
dba_free_spaces
可查看数据库表空间的使用情况，如查看各数据库表空间的可用空间：
select tablespace_name,sum(bytes) from dba_free_spaces group by talbespace_name;
11.oracle常见四种状态
ORACLE数据库任何时候可以处于四种状态之一：
SHUTDOWN：数据库关闭
NOMOUNT：例程启动
MOUNT：例程启动，控制文件打开
OPEN：例程启动，所有数据文件打开
数据库可以在几种状态之间转变：
NOMOUNT到MOUNT
ALTER DATABASE MOUNT
MOUNT到OPEN
ALTER DATABASE OPEN
ALTER SYSTEM ENABLE RESTRICTED SESSION
产生随机数：
SELECT round(dbms_random.value(1,99)) AS rand FROM dual a;
12.distinct去除相同的列
13.找出连接到得session
这样就可以删掉相应用户的会话进场
SQL> select sid,serial# from v$session where username = 'ABC';
SID SERIAL#
---------- ----------
440 18996
SQL> alter system kill session '440,18996';
14.从其他表中建表
SQL> create table emp as select * from student;
Table created.
SQL> select table_name from dba_all_tables where owner ='ABC';
TABLE_NAME
------------------------------
STUDENT
EMP
SQL> select * from EMP;
ID NAME AGE
-------------------- -------------------- ----------
78 45 4
48 6 5
48 6 5
15.SID介绍
环境变量 ORACLE_SID， 初始化文件initSID.ora，其实 SID 就是 Oracle 实例的标识，不同的 SID 对应不同的内存缓冲(SGA)和不同的后台进程。这样一来我们就可以得当在一台物理的服务器上可以有多个 SID 的数据库实例。 一个运行着的 ORACLE 数据库就可以看成是一个ORACLE SERVER，该 SERVER 由数据库(Database)和实例(Instance)组成，在一般的情况下一个ORACLE SERVER 包含一个实例和一个与之对应的数据库，但是在特殊情况下，如 8i 的 OPS，9i 的 RAC，一个 SERVER 中一个数据库可以对应多个实例。
一系列物理文件（数据文件，控制文件，联机日志等）的集合或与之对应的逻辑结构（表空间，段等）被称为数据库，简单的说，就是一系列与磁盘有关系的物理文件的组成。ORACLE 内存结构和后台进程被成为数据库的实例，一个实例最多只能安装(Mount)和打开(Open)在一个数据库上，负责数据库的相应操作并与用户交互。
16.Oracle常见变量类型
VARCHAR2 变长字符串，最长为2000字符
NUMBER,INT 数值型
LONG 变长字符数据，最长为2G字节
DATE 日期型
RAW 二进制数据，最长为255字节
LONG RAW 变长二进制数据，最长为2G字节
CHAR 定长字符数据，最长为255
17.如何为一张表增加一列
现有表如下：
当然增加后的字段的值全部为空了
18.视图操作
视图是一个表或者多个表中数据的简化，用户可以将视图看成一个存储查询或一个虚拟的表，查询仅仅存储在oracle数据字典中，实际的数据没有存放在任何其他地方，所以建立视图不需要消耗其他空间
1.建立视图
按照上述student表为例：
我们可以建立一个视图来查询出成绩大于99的学生的名字：
SQL> select * from friend;
ID NAME AGE SCORE
-------------------- -------------------- ---------- ----------
78 45 4 90
48 6 5 90
48 6 5 90
SQL> create view s_name as select name from friend where score > 90;
View created.
SQL> select * from s_name;
no rows selected
19.查看锁表信息
SQL> select a.owner,a.object_name,
2 b.xidusn,b.XIDSQN,b.SESSION_ID,b.ORACLE_USERNAME,b.PROCESS,b.LOCKED_MODE,
3 c.MACHINE,c.STATUS,c.SERVER,c.SID,c.SERIAL#,c.PROGRAM
4 from ALL_OBJECTS a,V$LOCKED_OBJECT b,V$SESSION C
5 WHERE (a.OBJECT_ID = b.OBJECT_ID) and (b.PROCESS = c.PROCESS);
然后解锁
ALTER system KILL session'832,990'
20.触发器
1.建立student表
SQL> create table student(
2 sid int primary key,
3 sname varchar(30),
4 sex varchar(20),
5 phone varchar(20),
6 classId varchar(20)
7 );
Table created.
2.插入一些数据：
insert into student(sid,sname,sex,phone,classId) values(001,'Tom','male','13901234567',1);
insert into student(sid,sname,sex,phone,classId) values(002,'Mike','male','13901234561',1);
insert into student(sid,sname,sex,phone,classId) values(003,'John','male','13901234562',1);
insert into student(sid,sname,sex,phone,classId) values(004,'Rose','fmale','13901234563',2);
insert into student(sid,sname,sex,phone,classId) values(005,'Mary','fmale','13901234564',2);
3.建立班级情况
SQL> create table class (
2 classId varchar(20) primary key, className varchar(30)
3 );
Table created.
insert into class (classId,className) values (1,'AAA');
insert into class (classId,className) values (2,'BBB');
建立教师表
SQL> create table teachers(
2 teacherId int primary key,
3 tName varchar(30),
4 tSex varchar(10),
5 tPhone varchar(20),
6 classId varchar(20)
7 );
insert into teachers (teacherId,tName,tSex,tPhone,classId) values (104,'ligang','Male','13600004444',1);
insert into teachers (teacherId,tName,tSex,tPhone,classId) values (103,'limu','Male','13700003333',1);
insert into teachers (teacherId,tName,tSex,tPhone,classId) values (102,'likui','Male','13800002222',1);
insert into teachers (teacherId,tName,tSex,tPhone,classId) values (101,'libai','Fmale','13300001111',1);
建立借书记录表
SQL> Create table BorrowRecord(
2 borrowId int primary key ,
3 sid int,
4 bookID varchar(20),
5 borrowDate date,
6 returnDate date
7 );
Table created.
ORACLE体系结构：
关于对上诉ORACLE的理解：
1.用户程序
2.系统程序
Database【数据文件】存储的文件有：
1.数据文件
2.控制文件
3.在线重做文件
还包括：
4.参数文件
5.备份文件
6.归档重做文件
7.警告和追踪文件
实例有：
1.内存结构
2.后台进程
ORACLE 使用到的几类文件：
1.参数文件:
(spfile(server parameter file)
对应文件 ORCALE_HOME/dbs/ spfileoracle.ora
1.查看ORACLE是以pfile还是以spfile启动的
Pfile和spfile文件区别：
Pfile是一种静态文件，修改配置文件后不能马上生效，可以使用工具打开进行修改，但需要重启ORACLE生效，而spfile是一种动态参数文件，是二进制文件，不能使用工具进行查看直接进行修改，不需要重启ORACLE也能生效
2.如何查看当前的启动方式是pfile还是以spfile
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- - -----------------------------
spfile string /tellin/oracle/app/product/11. 1.0/db_1/dbs/spfileoracle.ora
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /tellin/oracle/app/product/11. 1.0/db_1/dbs/spfileoracle.ora
Pfile和spfile文件是可以相互进行创建的所以可以使用如下：
Create pfile from spfile;
oracle.__db_cache_size=1174405120
oracle.__java_pool_size=33554432
oracle.__large_pool_size=16777216
oracle.__oracle_base='/tellin/oracle/app'#ORACLE_BASE set from environment
oracle.__pga_aggregate_target=1493172224
oracle.__sga_target=2801795072
oracle.__shared_io_pool_size=100663296
oracle.__shared_pool_size=1409286144
oracle.__streams_pool_size=0
*._disable_image_check=TRUE
*.archive_lag_target=7200
*.audit_file_dest='/tellin/oracle/app/admin/oracle/adump'
*.audit_trail='NONE'
compatible='11.1.0.0.0'
*.control_files='/tellin/oracle/app/oradata/oracle/control01.ctl','/tellin/oracle/app/oradata/oracle/control02.ctl','/tellin/oracle/app/oradata/oracle/control03.ctl'
*.db_16k_cache_size=33554432
*.db_block_size=8192
*.db_domain=''
*.db_name='oracle'
*.db_recovery_file_dest=''
*.db_securefile='ALWAYS'
*.diagnostic_dest='/tellin/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracleXDB)'
*.global_names=FALSE
*.memory_max_target=4294967296
*.memory_target=4294
*.nls_date_format='MM/DD/YYYY HH24:MI:SS'
*.open_cursors=300
*.pga_aggregate_target=1073741824#changed 13-12-11 as per recommendation of lg
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=TRUE
*.service_names='oracle'
*.sessions=568
*.sga_target=1610612736
*.trace_enabled=FALSE
*.undo_tablespace='UNDOTBS1'
主要设置oracle 初始参数
修改这个文件可以使用altersystem parameter set ..语句可以修改
遗留参数文件init.ora也在相对应的目录
控制文件
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl' /*ORACLE控制文件*/
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' /*服务器进程相关的日志路径*/
*.db_block_size=8192 /*内存块的大小*/
*.db_create_file_dest='/u01/app/oracle/oradata/orcl' /*tablespace ,datafile路径*/
*.db_create_online_log_dest_1='/u01/app/oracle/disk1' /*联机日志文件路径*/
*.db_create_online_log_dest_2='/u01/app/oracle/disk2' /*联机日志文件路径*/
*.db_domain='' /域名/
*.db_file_multiblock_read_count=16 /*一次可读取块的大小：*8k = 128kb*/
*.db_name='orcl' /数据库标识符/
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' /*闪回路径*/
*.db_recovery_file_dest_size=2147483648 /*闪回区可使用的大小GB*/
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' /*共享服务器的配置参数*/
*.job_queue_processes=10 /*当前作业进程数*/
*.open_cursors=600 /*可打开的游标数*/
*.pga_aggregate_target=135266304 /*服务器进程大小*/
*.processes=250 /*描述当前可以运行的进程数*/
*.remote_login_passwordfile='EXCLUSIVE' /*远程登陆是否要用密码文件*/
*.sga_target=406847488 /*定义了sga的大小*/
*.undo_management='AUTO' /*Undo表空间的管理方式为自动管理*/
*.undo_tablespace='UNDOTBS1' /*指定使用哪一个undo表空间*/
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump' /**/
跟踪文件
查看对应的路径可以通过系统控制参数进行查找
SQL> show parameterbackground_dump_dest;
------------------------------------ ----------- ------------------------------
background_dump_dest string /tellin/oracle/app/diag/rdbms/oracle/oracle/trace
这个控制可以查看到警告日志存放的位置
Alter警告日志作用：
BACKGROUND_DUMP_DEST参数中指定，该文件记录每天数据库操作信息：
1：数据库启动和停止日志。
2：所有非缺省的初始化参数列表。
3：启动的后台进程记录日志。
4：实例使用的线程日志。
5：LGWR进程写日志的日志序列号。
6：日志切换的信息日志。
7：tablespace和undosegments 建立的日志。
8：alter申明的结果日志。
9：错误信息日志如：ORA-600和扩展错误信息日志。
又称警告日志，ORACLE的日记
Redo 日志存储的不是ASCILL值，无法使用vi命令查看
Control文件也无法帮忙定位oracle故障问题
Trace文件：
/tellin/oracle/app/diag/rdbms/oracle/oracle/trace
查看oralce中
数据库：磁盘上存储的数据集合，也就是上一节中所说的各种文件组成。
一系列物理文件的集合
包括控制文件、数据文件、联机日志文件、参数文件、密码文件等
即：Oracle Database = Controlfile + datafile + logfiel + spfile +..
1.控制文件(controlfile)
数据库的名字，检查点信息，数据库创建的时间戳，所有的数据文件，联机日志文件，归档日志文件信息备份信息等
2.数据文件(datafile)
包含了用户和应用程序的所有数据 --查看数据文件信息
3.联机日志文件
记录了用户对数据库的所有操作，一个数据库中至少要有两个日志组文件，每个日志组中至少有一个日志成员，日志组中的多个日志成员是互为镜相关系
4.归档日志文件
Oracle可以运行在两种模式之中，归档模式和非归档模式。在归档模式中，为了保存用户的所有修改，在联机日志文件切换后和被覆盖之间系统将他们另外保存成一组连续的文件系列，该文件系列就是归档日志文件。用户恢复意外情况出现的数据丢失、异常等。
5.参数文件(pfile和spfile)
初始化文件记载了许多数据库的启动参数，如内存，控制文件，进程数等，在数据库启动的时候加载(Nomount时加载)
实例：一组后台进程和共享内存。
Oracle实例
包括了内存结构(SGA)和一系列后台进程(Background Process),两者合起来称为一个Oracle实例
即：Oracle Instance = SGA + Background Process
Oracle内存结构
包含系统全局区(SGA)和程序全局区(PGA)
即Oracle Memory Structures = SGA + PGA
SGA由服务器和后台进程共享
PGA包含单个服务器进程或单个后台进程的数据和控制信息,与几个进程共享的SGA 正相反，PGA是只被一个进程使用的区域，PGA 在创建进程时分配在终止进程时回收。即由服务器进程产生
实例“一生”只能装载并打开一个数据库。一个数据库可以由一个或多个实例（使用RAC）装载和打开。Oracle系统中每一个 实例都由一个SID（site identity descriptor）值唯一标识。两者的关系有点类似程序与进程之间的关系。下图描述了两者的关系。
打开数据库：
打开实例--------à加载数据库---------à打开数据库
关闭数据库：
关闭数据库-------à卸载数据库---------à关闭实例
具体的启动过程如下:
1.nomount阶段(即实例的启动)
实例的启动通常包含下列任务：
a.按以下顺序spfile<SID>.ora ---> spfile.ora ---> init<SID>.ora查找参数文件
b.分配SGA
c.启动后台进程
d.打开并修改告警<SID>.log文件及跟踪文件
可以在此阶段完成数据库重建，控制文件重建
此阶段不打开任何的控制文件及数据文件。
2.mount阶段
mount阶段完成的任务：
a.启动实例并打开控制文件，将数据库与实例关联起来
b.利用参数文件中的说明，打开并锁定控制文件
c.读取控制文件以获取数据文件和重做日志文件的名字和状态信息，但不检查数据日志文件是否存在
使用场景：
a.重命名数据文件，移动数据文件位置等(数据库打开，表空间脱机的情况下也可以重命名数据文件)
b.启用或关闭重做日志文件的归档及非归档模式
c.实现数据库的完全恢复
这一步会读控制文件，如果这一步有一个控制文件损坏就无法启动
3.open阶段
mount阶段完成的任务：
a.打开数据文件
b.打开联机日志文件
注意：
a.在此期间，Oracle服务器将校验所偶的数据文件和联机日志文件能否打开并对数据库作一致性检查
b.如果出现一致性错误，SMON进程将启动实例恢复
c.如果任一数据文件或联机日志文件丢失，Oracle服务器将报错
sqlplus user/password与sqlplus user/password@ connect_identifier登录机制是不同的。
后者需要通过TNS监听进程，但前者似乎不需要。下面是把监听进程关闭掉的测试结果：
linux /home/dsbscp> sqlplus'ops$dsbscp/dsbscp@dsbscp'
SQL*Plus:Release 10.1.0.3.0 - Production on 26 04:01:29 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-12541:TNS:no listener
linux /home/dsbscp> sqlplus'ops$dsbscp/dsbscp'
SQL*Plus: Release 10.1.0.3.0- Production on26 04:02:53 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise EditionRelease 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Miningoptions
ops$dsbscp@DSBSCP>
sqlplususer/password形式的登录明显绕过了TNS这一层。它实际上通过客户进程sqlplus自己创建服务器进程。这就限制了这样的登录模式必须是在本机上（不能通过网络），且服务器是专用服务器，因为共享服务器的进程存在调度器中间环节，调度器是无法知道客户端的服务进程的，必须采用TNS机制。
数据库常用导出导入：
导出表结构
exp orasmp/12345 file=mm_user.dmp ROWS=N compress=N tables="mm_user"log=exp.log
导入表结构
imp orasmp/12345 file=mm_user.dmp ROWS=N compress=Ntables="mm_user" log=exp.log
导出数据库
exp sysdb55/sysdb55@sysdb55 file= sysdb55_net.dmp owner=sysdb55 rows=ylog=expdb.log
导入数据库
imp sysdb55/sysdb55@sysdb55 fromuser=sysdb touser=sysdb55 rows=ycommit=y buffer=65536 feedback=100000 ignore=y volsize=0file=sysdb_20101022.dmp log=imp.log
oracle常用目录
ORACLE_BASE:
为Oracle目录结构的根，该环境变量为指定计算机上的所有Oracle产品目录 如：$ORACLE_BASE=/u01/app/oracle
ORACLE_HOME:
当前Oralce软件安装的主目录，通常该环境变量为由ORACLE_BASE指示的路径下的一个目录
如：$ORACLE_HOME=$ORACLE_BASE/10g 完整的路径即为：$ORACLE_HOME=/u01/app/oracle/10g
ORACLE_HOME包含与Oracle软件运行有关的子目录和网络文件以及选定的组件等
$ORACLE_HOME目录中的常用子目录如下：
/bin --主要包含用于数据库管理的各种命令等,如impdb,expdp,emctl,emca等
/css --与Oracle Cluster Synchronization服务有关的文件
/dbs --存放数据库服务器端的参数文件Spfile,如spfileorcl.ora,init.ora,密码文件orapworcl等
/demo --存放数据库实例模式的脚本等 如human_resources的相关脚本
/install --用于存储ORACLE安装后的端口号,iSQL*Plus以及Enterprise Manager Database Control启动并登录的方式等,如该路径下的portlist.ini
/network/admin --有关监听器listener.ora和sqlnet.ora以及tnsnames.ora等
/sysman/config --用于与Oracle Enterprise Management有关的端口管理等
admin目录
位于$ORACLE_BASE目录下，即$ORACLE_BASE/admin/$ORACLE_SID主要存储的为Oracle 后台进程产生的相关文件及数据库管理文件
以下是其下常用的子目录
/adump --存储审计文件的目录(需要首先设置audit_file_dest参数)
/bdump --后台进程跟踪文件(使用backgroud_dump_dest参数进行设置)
/cdump --存储核心堆积文件(使用core_dump_dest参数进行设置)
/create --存储用于创建数据库的脚本。创建新的数据库时,DBCA将创建数据库的脚本放置在这个目录
/exp --建议存储使用Export使用程序或Oracle Data Pump创建的数据库导出文件的目录
/pfile --初始化参数文件
/udump --存储用户进程跟踪文件,使用user_dump_dest参数设置
oradata目录
Oracle数据库文件缺省存储该目录下,即$ORACLE_BASE/$ORACLE_SID/oradata,主要包括数据库的控制文件、数据文件、重做日志文件。
dbf文件对应数据库中每个表空间
ctl文件为控制文件
log文件对应重做日志文件组及其成员
flash_recovery_area目录
flash_recovery_area目录存储并管理与备份和恢复有关的文件。它包含系统中每个数据库的子目录。
该目录可用于存储与恢复有关的文件,如控制文件、联机重做日志副本、归档日志、闪回日志以及Oracle数据库恢复管理器(RMAN)备份等。
Oracle逻辑结构：
表空间
表空间是数据库最基本的逻辑结构
表空间、段、区、块
一个数据库由一个或多个表空间组成，一个表空间只能属于一个数据库
一个表空间由一个或多个多个数据文件组成，一个数据文件只能属于一个表空间
一个数据文件由一个或多个操作系统块组成，每一个操作系统块只能数以一个数据文件
一个表空间可以包含一个或多个段，一个段只能属于一个表空间
一个段由一个或多个区组成，每一个区只能属于一个段
一个区由一个或多个Oracle 块组成，每一个Oracle块只能属于一个区
一个区只能属于一个数据文件，数据文件的空间可以分配到一个或多个区
一个Oracle 块由一个或多个操作系统块组成，一个操作系统块是一个Oracle块的一部分
不可或缺的几个表空间：
SYSTEM --->字典表空间，不能被损坏
UNDO--->dml,dql把数据快照到此，数据提交即消失（用于恢复）
SYSAUX ---> 高并发系统繁忙时，会造成system争用，将工具放到SYSAUX,减轻system的压力，SYSAUX不影响系统（影响性能）
TEMP--->临时数据相关的内容
USERS ---> 用户数据从system拨离出来
不可或缺的几个表空间：
SYSTEM --->字典表空间，不能被损坏
UNDO--->dml,dql把数据快照到此，数据提交即消失（用于恢复）
SYSAUX ---> 高并发系统繁忙时，会造成system争用，将工具放到SYSAUX,减轻system的压力，SYSAUX不影响系统（影响性能）
TEMP--->临时数据相关的内容
USERS ---> 用户数据从system拨离出来
二、Oracle的存储结构
1.Schema: 用户--->创建相关对象、表、视图、序列、函数、存储过程、包等
2.逻辑结构：database数据库--->tablespace表空间---> segment段--->extent区间----> block块,逻辑结构是Oracle内部管理数据库中对象的方式
3.物理结构：OS block --->datafile 物理结构通常是一系列数据文件
4.举例描述scott用户创建对象的组织方式 */
--查看scott用户的默认表空间及数据文件
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
2 from dba_users
3 where username = 'SCOTT';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMP
SQL> col name format a50
SQL> select t1.name,t2.name --查看表空间及数据文件
2 from v$tablespace t1,v$datafile t2
3 where t1.ts# = t2.ts#;
NAME NAME
-------------------------------------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1/u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
EXAMPLE/u01/app/oracle/oradata/orcl/example01.dbf
/*
scott-->emp(table)-->数据定义(位于)-->system表空间数据(位于) -->user表空间(逻辑存储)-->表段-->区间-->内存块-->索引段等-->区间-->内存块
user表空间(物理存储)-->user01.dbf-->采用本地管理，包含头部信息，可用、已用等位图信息
当databuffer缓冲区满则调用dbwr进程将数据写入到物理文件当中 */
三、创建表空间
--简要语法：
CREATE [BIGFILE | SMALLFILE] [TEMPORARY] TABLESPACE tablespace name
DATAFILE datafile spec | TEMPFILE tempfile spec
[MINIMUM EXTENT minimum extent size]
[BLOCKSIZE blocksize]
[[COMPRESS|NOCOMPRESS] DEFAULT STORAGE (default storage clause)]
[LOGGING|NOLOGGING]
[FORCE LOGGING]
[ONLINE|OFFLINE]
[EXTENT MANAGEMENT DICTIONARY |
LOCAL [AUTOALLOCATE|UNIFORM SIZE size]]
[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]
[FLASHBACK ON|OFF]
--创建表空间的条件
1.具有create tablespace的权限，DBA角色具有该权限，sysdba,sysoper
2.创建的是bigfiel ，还是smallifle 超过T 级别应考虑bigfile
3.新建的表空间的I/O，是否会导致磁盘I/O不够用
4.datafile后跟的路径应该具备写的权限 */
--查看缺省是BIGFILE 还是SMALLFILE,当缺省为SMALLFILE,创建表空间不指定表空间类型则为SMALLFILE
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TBS%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TBS_TYPE SMALLFILE
--修改创建表空间为大或小表空间的默认值
SQL> alter database set default bigfile tablespace;
Database altered.
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TBS%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TBS_TYPE BIGFILE
--改回为缺省值
SQL> alter database set default smallfile tablespace;
Database altered.
--建议创建表空间时最好带上[BIGFILE | SMALLFILE]参数，以免参数设置导致创建了不合理的表空间
/*
注意：
大表文件（bigfile)最大可以存放个T的容量。头文件的大小达到了G－－＞block,普通的头文件大小为M---->block。
create bigfile tablespace <> datafile <>........;
好处：减少了数据文件的个数，管理方便，大的对象的存放得到了优化。减少了control文件的信息，控制文件定义了datafile的个数。
bigfile只能存在一个数据文件，所以要保证分配的的磁盘具有足够的空间。 */
--演示创建表空间
SQL> create tablespace TBS1 datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' size 100m;
Tablespace created.
SQL> col name format a50
SQL> select t1.name,t2.name
2 from v$tablespace t1,v$datafile t2
3 where t1.ts# = t2.ts#;
NAME NAME
-------------------------------------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
EXAMPLE/u01/app/oracle/oradata/orcl/example01.dbf
TBS1 /u01/app/oracle/oradata/orcl/tbs1.dbf
/*
创建临时表空间：
创建临时表空间，不能使用非标准数据块，另临时表空间不能存放永久对象。*/
CREATE TMEPORARY TABLESPACE tablespace_name
TEMPFILE '...' SIZE n
CREATE TEMPORARY TABLESPACE T2 TEMPFILE '/opt/oracle/oradata/orcl/t2.dbf'
size 100m;
/*
临时表空间具有以下特征：
临时数据文件不能置为只读
临时数据文件不能重命名
监时数据文件的日志方式总是NOLOGGING
临时表空间扩容：*/
--(1)重置临时文件大小
alter database tempfile '/opt/oracle/oradata/orcl/t2.dbf' resize 150m;
--(2)让临时文件能自动扩展
alter database tempfile '/opt/oracle/oradata/orcl/t2.dbf'
autoextend on next 10m maxsize 50m;
--(3)增加临时文件
alter tablespace t2 add tempfile '/disk8/oracle/t2b.dbf' size 50m;
--查看临时文件
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE(MB)"
from dba_temp_files;
--默认临时表空间：
select * from database_properties;
--修改默认临时表空间：
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE T2;
/*
默认临时表空间最好要指定一下，如果没有指定默认的临时表空间，那么将使用system表空间作为排
序区
默认临时表空间有一定的限制：
默认临时表空间不能删除
默认临时表空间不能脱机
获得创建表空间的语句：*/
SELECT dbms_metadata.get_ddl('TABLESPACE','SYSTEM') FROM dual;
/*
临时表空间组：
10G引入的，是一组临时表空间，只能由临时表空间组成。组名不能与临时表空间同名
临时表空间不能显示的创建和删除，当把第一个临时表空间分配给某个临时表空间组的时候，自动创建
临时表空间组，将最后一个临时表空间，删除时，组也将自动删除。
创建临时表空间组：
1、创建临时表空间的时候 */
CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE '...' SIZE n
TABLESPACE GROUP group_name;
ALTER TABLESPACE tablespace_name TABLESPACE GROUP group_name;
ALTER TABLESPACE T3 TABLESPACE GROUP TEMP_GRP;
--查看临时表空间组的信息:
dba_tablespace_groups
SELECT * FROM DBA_TABLESPACE_GROUPS;
--将临时表空间组成员移除：
ALTER TABLESPACE tablespace_name TABLESPACE GROUP '';
--可将系统默认的临时表空间设为临时表空间组：
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;
--删除临时表空间：
--将所有成员全移出去，便自动删除
--创建UNDO表空间（还原表空间）：
/*
创建语法：*/
CREATE UNDO TABLESPACE tablespace_name DATAFILE '...' SIZE n
SQL> CREATE UNDO TABLESPACE undo2
DATAFILE '/opt/oracle/oradata/orcl/undo2_01.dbf' SIZE 200M,
'/opt/oracle/oradata/orcl/undo2_02.dbf' size 100m;
--修改当前系统的UNDO表空间:
ALTER SYSTEM SET UNDO_TABLESPACE=tablespace_name
/*
UNDO表空间扩容：
（）重置DATAFILE大小
（）让DATAFILE能AUTOEXTEND
（）添加数据文件
删除UNDO表空间 */
DROP TABLESPACE tablespace_name
/*
注：当前的UNDO表空间不能删除
系统只能使用一个UNDO表空间 */
--创建表空间的详细语法：http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7003.htm#SQLRF01403
/*
四、表空间的管理
--表空间改名：
--必须在open状态下，system , sysaux 不能改名
ALTER TABLESPACE XXX RENAME TO YYY
--表空间的扩容：
/*
表空间的大小由组成它的所有数据文件的大小之和来决定
扩容的方法：*/
--(1)重置数据文件大小
ALTER DATABASE DATAFILE '...' | FileNo RESIZE XX
--(2)设置数据文件能自动增长
ALTER DATABASE DATAFILE '... '| FileNo AUTOEXTEND ON | OFF NEXT 20M MAXSIZE 300M;
--查看哪些表空间为自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
/* (3)添加数据文件
增加到表空间中的数据文件不能直接从表空间中删除，除非删掉整个表空间
增加数据文件将有助于均衡I/O
一个表空间文件最多为个，文件越多，执行一次检查点的代价越高 */
ALTER TABLESPACE tablespace_name ADD DATAFILE '...' SIZE XX;
ALTER TABLESPACE tbs2 ADD DATAFILE '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 200m autoextend on ;
--表空间的四种状态：
online
offline
read only
read write
/*
offline：
一个表空间的正常状态是联机(ONLINE)，有时需要将某一个表空间进行脱机，以进行数据库维护
如：
在数据库打开的状态下移动数据文件
在数据库打开的情况下恢复一个表空间或数据文件
执行表空间的脱机备份（虽然可以进行进行联机备份）
使数据库某一部分不可访问，让其它部分能正常访问。
表空间脱机的时候，将产生检查点事件，该表空间上的数据是不能访问的
命令：*/
ALTER TABLESPACE tablespace_name OFFLINE;
--联机命令：
ALTER TABLESPACE tablespace_name ONLINE;
--在脱机后，有可能无法再联机，这时要做数据的介质恢复
ALTER DATABASE RECOVER TABLESPACE tablespace_name
ALTER TABLESPACE tablespace_name ONLINE;
/*
read only:
不能执行DML语句，可以使用的为DDL，DQL语句 */
ALTER TABLESPACE tablespace_name READ ONLY;
read write：
ALTER TABLESPACE tablespace_name READ WRITE;
--一些表空间的特殊状态
tablespacenameonlieread only
system 必须online 必须read write
sysaux 可以offline 不能read only
undo不能offline 不能read only
--查看表空间的状态
SQL> select tablespace_name,file#,v.status,v.enabled from dba_data_files d,
2 v$datafile v
3 where d.file_id = v.file#;
TABLESPACE_NAME FILE# STATUS ENABLED
------------------------------ ---------- ------- ----------
SYSTEM 1 SYSTEM READ WRITE
UNDOTBS12 ONLINE READ WRITE
SYSAUX 3 ONLINE READ WRITE
USERS 4 ONLINE READ WRITE
EXAMPLE 5 ONLINE READ WRITE
TBS16 ONLINE READ WRITE
--几种状态的相互转换的演示
--将表空间置为只读
SQL> alter tablespace users read only;
Tablespace altered.
SQL> update scott.emp set sal = sal + 50 where ename = 'SCOTT'; --置为只读后不可更新
update scott.emp set sal = sal + 50 where ename = 'SCOTT'
*
ERROR at line 1:
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
SQL> select tablespace_name,file#,v.status,v.enabled from dba_data_files d,
2 v$datafile v
3 where d.file_id = v.file# and tablespace_name = 'USERS';
TABLESPACE_NAME FILE# STATUS ENABLED
------------------------------ ---------- ------- ----------
USERS 4 ONLINE READ ONLY
--将表空间置为脱机
SQL> alter tablespace users offline;
Tablespace altered.
SQL> update scott.emp set sal = sal + 100 where ename = 'SCOTT';/*置为脱机后不可更新*/
update scott.emp set sal = sal + 100 where ename = 'SCOTT'
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
--将表空间置为联机
SQL> alter tablespace users online;
Tablespace altered.
SQL> select tablespace_name,file#,v.status,v.enabled from dba_data_files d,
2 v$datafile v
3 where d.file_id = v.file# and tablespace_name = 'USERS';
TABLESPACE_NAME FILE# STATUS ENABLED
------------------------------ ---------- ------- ----------
USERS 4 ONLINE READ ONLY
--注意，readonly 状态下的表空间可以删除数据，仅仅是不能插入或更新数据，如下
SQL> delete from scott.emp where ename = 'SCOTT';
SQL> rollback;
Rollback complete.
--将表空间置为读写状态
SQL> alter tablespace users read write;
Tablespace altered
--表空间数据文件的重命名或重定位
--方法一，在open状态下且处于archive模式:
--1、查出数据文件位置和所属的表空间
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE(MB)"
from dba_data_files;
--2、将数据文件所在的表空间离线
alter tablespace uu offline;
--3、在操作系统下将文件移动到另外一个位置，或重命名(建议使用cp)
host mv /opt/oracle/oradata/orcl/tt.dbf /disk9/uu1.dbf
--4、修改控制文件，将数据文件的指针重新指向到另一个位置(执行rename)
alter tablespace uu rename datafile '/opt/oracle/oradata/orcl/tt.dbf'
to '/disk9/uu1.dbf';
--5、表空间在线
alter tablespace uu online
--方法二，不能脱机的表空间
--1、关闭数据库，再启动到mount状态
startup mount
--2、在操作系统下，移动或重命名文件
host mv /opt/oracle/oradata/orcl/tt.dbf /disk9/uu1.dbf
--3、修改控制文件
alter database rename file '/disk8/uu.dbf' to '/disk8/uu2.dbf';
--4、打开数据库
alter database open;
/*
删除表空间：
意味着表空间和数据文件的信息从数据库中删除，同时也应该将相应的操作系统文件删除
不能删除的表空间
系统表空间
有活动回滚段的表空间
默认临时/永久表空间
命令： */
DROP TABLESPACE tablespace_name [INCLUDING CONTENTS [AND DATAFILES]]
INCLUDING CONTENTS-- 删除表空间及所有段
INCLUDING CONTENTS [AND DATAFILES] --删除表空间、所有段、数据文件、同时删除系统中的物理文件
--限制某个用户对表空间的使用：
ALTER USER user_name QUOTA n ON tablespace_name;
ALTER USER scott QUOTA 10m on UU;
--取消限制
ALTER USER user_name QUOTA UNLIMITED ON tablespace_name;
GRANT UNLIMITED TABLESPACE TO user_name;
--单个数据文件离线：
--1、查看有哪些数据文件
SELECT name FROM v$datafile;
--2、离线
ALTER DATABASE DATAFILE '...' OFFLINE;
--3、在线
ALTER DATABASE DATAFILE '... ' ONLINE
-- 有可能提示要进行介质恢复：
RECOVER DATAFILE '...' ；
ALTER DATABASE RECOVER TABLESPACE tablespace_name;
-- 之后再将文件再线
--重命名数据文件（同前面移动数据文件一样）
--获取表空间和数据文件信息
--表空间信息
dba_tablespaces
v$tablespace
--数据文件信息
dba_data_files
v$datafile
--临时数据文件信息
dba_temp_files
v$tempfile
--查看系统默认的表空间及临时表空间：
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties
2 where PROPERTY_NAME like 'DEFAULT%';
--修改改系统的默认表空间：
SQL>ALTER DATABASE DEFAULT TABLESPACE tablespace_name
--修改系统的默认临时表空间：
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name
--查看用户默认表空间：
SQL>select USERNAME,USER_ID,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;
--修改用户默认表空间：
SQL>ALTER USER user_name DEFAULT TABLESPACE tablespace_name
--查看表空间的使用情况：
dba_data_files;
dba_free_space;
--例：查每个表空间的剩余空间
SQL> select tablespace_name,sum(bytes/1024/1024)
2 from dba_free_space group by tablespace_name;
/*
五、数据文件
数据文件是数据库中最重要的一个要求，是所有的数据库内容存放的地方
datafile是按照表空间为组织单位，表空间的构成是按照段区块为层次－－－数据文件的逻辑结构
datafile就是表空间的物理文件。
datafile中的内容是通过dbwr把data buffer cache中的dirty buffer data 写入的
dbwr触发：
ckpt触发，dirty buffer 到一定量的时候，data buffer caceh 的内存空间快用完了
查询空闲内存空间超时，表空间offline/read only,表的drop, truncate 开始执备份表空间的时候 */
--查看数据文件
SQL> select file# ,name,status,enabled,bytes/1024/1024 M from v$datafile;
FILE# NAME STATUS ENABLED M
---------- -------------------------------------------------- ------- ---------- ----------
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM READ WRITE490
2 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE READ WRITE 30
3 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE READ WRITE250
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE READ WRITE 5
5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE READ WRITE100
6 /u01/app/oracle/oradata/orcl/tbs1_1.dbfONLINE READ WRITE100
7 /u01/app/oracle/oradata/orcl/tbs1_2.dbfONLINE READ WRITE 60
SQL> select tablespace_name,file_name,user_bytes/1024/1024 M from dba_data_files;
TABLESPACE_NAMEFILE_NAMEM
------------------------------ ------------------------------------------------------- ----------
USERS01/u01/app/oracle/oradata/orcl/users01.dbf4.9375
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 249.9375
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 29.9375
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 489.9375
EXAMPLE/u01/app/oracle/oradata/orcl/example01.dbf 99.9375
TBS1 /u01/app/oracle/oradata/orcl/tbs1_1.dbf99.9375
TBS1 /u01/app/oracle/oradata/orcl/tbs1_2.dbf59.9375
--数据文件的增加与删除
--为TBS1增加一个数据文件
SQL> alter tablespace tbs1 add datafile '/u01/app/oracle/oradata/orcl/tbs1_3.dbf' size 10m autoextend on;
--还有一种创建表空间时不指定大小，而是重用一个存在的dbf要用resue参数，reuse 是把原有的结构拿来用，里面的数据是用不了的，因为早都没有了。
--删表空间中的数据文件，11g以后的版本才用以下功能，在之前版本的就只能把表空间给干掉。
SQL> alter tablespace tbs1 drop datafile '/u01/app/oracle/oradata/orcl/tbs1_3.dbf';
--有数据的数据文件不能删。第一个不能删，如果要删就把表空间干掉，数据文件中有数据写入的文件也不能删除，如下
SQL> alter tablespace tbs1 drop datafile 7; --7是v$datafile中的file#.
alter tablespace tbs1 drop datafile 7
*
ERROR at line 1:
ORA-03262: the file is non-empty
/*
数据文件的日志记录
只有temp表空间是nologing 别的都是logging的
日志记录的几种模式
nologin 并不是不记录日志而是记录的很少
logging 正常记录日志信息
force logging记录的日志比logging还详细记录信息非常多 用于DG data guard 容灾的情况 */
SQL> select tablespace_name ,logging,force_logging from dba_tablespaces;
TABLESPACE_NAMELOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS01LOGGING NO
EXAMPLENOLOGGING NO
TBS1 LOGGING NO
--修改表空间中日志文件记录的方式
SQL> alter tablespace tablespace_name nologging | logging | force logging;
SQL> alter tablespace example logging;
Tablespace altered.
SQL> alter tablespace tbs1 force logging;
Tablespace altered.
SQL> select tablespace_name ,logging,force_logging from dba_tablespaces;
TABLESPACE_NAMELOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS01LOGGING NO
EXAMPLELOGGING NO
TBS1 LOGGING YES
- oracle学习总结----oracle常用命令
- oracle学习总结(二)
- ORACLE 8023学习总结
- oracle学习总结(二)
- oracle学习总结(二)
- oracle学习总结(一)
- oracle学习总结(二)
- Oracle 学习总结
- oracle学习总结(二)
- 【oracle学习】简单总结
- Oracle数据库学习总结
- 宝宝Oracle学习总结
- oracle index学习总结
- oracle的学习总结
- oracle学习总结
- Oracle数据库学习总结
- oracle学习总结
- Oracle学习总结
- C++ FAQ学习笔记 13 章 运算符重载
- C++ FAQ学习笔记 14 章 友元
- Oracle截取字符串和查找字符串
- POJ 1450 最短距离
- Android 4.2.2 LockScreen&Keyguard 锁屏分析
- oracle学习总结
- C++ FAQ学习笔记 15 章 通过 <iostream> 和 <cstdio>输入／输出
- javacript设置CSS样式的新方法
- 1.4
- <web>get post区别
- C++ FAQ学习笔记 16章 自由存储管理
- Java基础12：线程；等待唤醒机制；锁；线程优先级；
- [Boost基础]并发编程——asio网络库——定时器deadline_timer
- 文件函数
