--1、查询数据库临时表空间信息
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_VALUE='TEMP';
--2、表空间信息
SELECT TP.tablespace_name AS 表空间名称,
TP.block_size / 1024 || 'M' AS 空间块大小,
TP.contents AS 空间类型,
TP.extent_management AS 表空间管理方式
FROM DBA_TABLESPACES TP;
--查询数据字典管理模式下，哪些区被占用，权限属于sys，如果是LOCAL管理方式，则数据行为空
SELECT * FROM UET$;
--查询数据字典管理模式下，哪些区是空闲状态（FET：FREE EXTENT TABLE）
SELECT * FROM FET$;
--创建普通表空间语法
CREATE TABLESPACE test DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' SIZE 5M EXTENT MANAGEMENT LOCAL
--创建UNDO表空间
CREATE UNDO TABLESPACE testundo DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TESTUNDO.DBF' SIZE 5M
--创建临时表空间
CREATE TEMPORARY TABLESPACE testtemp TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TESTTEMP.DBF' SIZE 5M
SELECT * FROM V$datafile;
数据库（Database）可以拥有多个表空间（Tablespace），一个表空间在同一时间只能属于同一个数据库，即两者是1：N的关系；
一个表空间（Tablespace）可以由多个段（Segment）组成，段可以跨越不同的数据文件（Data file）；
一个段（Segment）由由多个区（Extent）组成；
一个数据文件（Data file）由多个区组成，即存在1：N的关系，一个区只能属于一个数据文件；
一个区由多个连续的Oracle数据块（Block）组成；
在Oracle数据库中Block是最小的存储单位；
Oracle数据块与操作系统的数据块的关系是整数倍的关系，比如OS的block大小是4K，则Oracle数据块大小只能是4的整数倍，如4K、8K、16K等，整数倍的意义在于不必要的I/O；
附：
查询Windows操作系统Block大小的方法：命令行执行“fsutil fsinfo ntfsinfo c:”
查询Oracle Block大小的方法：sqlplus中执行 “show parameter db_block_size”
其他操作系统（如linux、HP-UNIX等）查询数据块大小方法具体请参考：http://blog.itpub.net/25116248/viewspace-1063047/
一个数据库中只有SYSTEM表空间是必须的，如果使用DBCA创建数据库时选择表空间管理方式为Local Managent，则日后无法再更改数据库表空间管理方式；
段（Segment）有两种管理方式
1、手动管理，用FREELIST，叫MSMM；
2、自动管理，叫ASSM
区（Extent）的两种管理方式
1、字典管理方式（Dictionary，Oracle8.15版本之前默认管理方式，现在已经被淘汰，字典管理方式容易产生递归，不能良好支持并发）
2、本地管理（Oracle9版本以后默认管理方式）
创建数据库时，会自动预置如下数据表空间
1、SYSAUX：这是SYSTEM 表空间的辅助表空间，Oracle Database 10g 或更高版本都必须拥有SYSAUX 表空间本地化自动段管理；
2、UNDOTBS1：这是数据库服务器用于存储还原信息的还原表空间
3、TEMP：如果执行的SQL 语句需要创建临时段（如大规模排序或创建索引），则可以使用临时表，创建数据库时如果用户没有明确指定默认临时表空间，则此表空间会被作为默认临时表空间。
4、USERS：此表空间用于存储永久用户对象和数据。
5、EXAMPLE：此表空间包含创建数据库时可以安装的例子数据
alter tablespace offline;
alter tablespace online;
说明：offline可以使表空间离线，但是不能对system、temp以及有活动事务的undo表空间进行离线操作；
--查询数据文件信息
select * from dba_data_files;
select * from v$datafile;
--修改数据文件自动扩展范围
alter database datafile 'E:\APP\NEUSOFT\ORADATA\ORCL\DATAFILE\O1_MF_DUSHUAI_BBQ2GWSK_.DBF' autoextend on next 50M maxsize unlimited;
--查询临时数据文件信息
select * from dba_temp_files;
select * from v$tempfile;
--空间分配信息
select * from dba_free_space;
select * from dba_extents;
--修改数据文件自动扩展范围
alter database datafile 'E:\APP\NEUSOFT\ORADATA\ORCL\DATAFILE\O1_MF_DUSHUAI_BBQ2GWSK_.DBF' autoextend on next 50M maxsize unlimited;
================================================
Oracle表空间和数据文件章节试验章节
================================================
SQL> --更改会话标识符为dushuai
SQL> set sqlprompt "dushuai>>"
dushuai>>--创建一个本地化管理的表空间，初始大小定义为20M，统一块大小256K
dushuai>>create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' size=20M extent management local uniform=256K;
create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' size=20M extent management local uniform=256K
*
第 1 行出现错误:
ORA-02237: 无效的文件大小
dushuai>>create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' size 20M extent management local uniform size 256K;
表空间已创建。
dushuai>>--上面的创建错误，依然说明对于创建表空间的语法不熟练，加强练习!
dushuai>>--创建一个test用户，默认表空间是test
dushuai>>create user test identified by test default tablespace test;
用户已创建。
dushuai>>--为新创建的用户test赋予基本权限
dushuai>>grant connect,resource to test;
授权成功。
dushuai>>--下面我们用新创建的用户登录sqlplus
dushuai>>conn test/test
已连接。
dushuai>>show user
USER 为 "TEST"
dushuai>>--创建一张表test,则test表默认的表空间就是test
dushuai>>create table test (id integer,name varchar2(100));
表已创建。
dushuai>>desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(100)
dushuai>>--向test表插入几行模拟数据
dushuai>>insert into test values(1,'dushuai');
已创建 1 行。
dushuai>>insert into test values(2,'tom');
已创建 1 行。
dushuai>>commit;
提交完成。
dushuai>>col name for a20;
dushuai>>select * from test;
ID NAME
---------- --------------------
1 dushuai
2 tom
dushuai>>--查看dba_tablespace表的字段信息
dushuai>>grant dba to test;
授权成功。
dushuai>>conn test/test;
已连接。
dushuai>>desc dba_tablespaces;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
dushuai>>--上面的错误说明一个普通用户无法查询dba_ 报表
dushuai>>SELECT TP.tablespace_name AS 表空间名称,
2 TP.block_size / 1024 || 'M' AS 空间块大小,
3 TP.contents AS 空间类型,
4 TP.extent_management AS 表空间管理方式
5 FROM DBA_TABLESPACES TP
6 WHERE TP.tablespace_name='TEST';
表空间名称 空间块大小 空间类型 表空间管理
-------------- ----------- ---------- ------------------
TEST 8M PERMANENT LOCAL
dushuai>>--我们将新创建的test表空间设置为只读
dushuai>>alter tablespace test read only;
表空间已更改。
dushuai>>--表空间设置为只读模式，会引起系统发生检查点操作，会促使DBWR进程将内存块中的脏数据写入磁盘数据文件中
dushuai>>--需要注意的是，检查点只会写脏数据，但是并不会完结事务，所以在设置只读模式前，必须保证没有尚未结束的事务，否则操作会hang在哪里；
dushuai>>--我们检测一下只读模式下，能否插入、删除、更新数据行
dushuai>>insert into test values(3,'wanwei');
insert into test values(3,'wanwei')
*
第 1 行出现错误:
ORA-00372: 此时无法修改文件 7
ORA-01110: 数据文件 7: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF'
dushuai>>delete from test where id=2;
delete from test where id=2
*
第 1 行出现错误:
ORA-00372: 此时无法修改文件 7
ORA-01110: 数据文件 7: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF'
dushuai>>update test set id=88 where id=2;
update test set id=88 where id=2
*
第 1 行出现错误:
ORA-00372: 此时无法修改文件 7
ORA-01110: 数据文件 7: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF'
dushuai>>--上面的事例告诉我们，表空间只读模式下是无法进行DML操作的；
dushuai>>--我们再试试是否可以truncate、drop表
dushuai>>truncate table test;
truncate table test
*
第 1 行出现错误:
ORA-00372: 此时无法修改文件 7
ORA-01110: 数据文件 7: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF'
dushuai>>drop table test;
表已删除。
dushuai>>--上面的事例告诉我们，只读模式下只能drop，不能truncate
dushuai>>--更改表空间为可读可写模式
dushuai>>alter tablespace test read write;
表空间已更改。
dushuai>>--如果一个表空间不需要更新、写入数据，可以设置为只读模式
dushuai>>--表空间离线操作：system、处于活动状态的undo表空间、默认的temp表空间不能离线，表空间后，用户无法访问；
dushuai>>alter tablespace test offline;
表空间已更改。
dushuai>>--查询表test
dushuai>>select * from test;
select * from test
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
dushuai>>--上述现象说明，表空间离线后，无法再访问表空间中原有的对象信息，如表；
dushuai>>--表空间在线
dushuai>>alter tablespace test online;
表空间已更改。
SQL> show user
USER 为 "SYS"
SQL> --变更test表空间为自动扩展模式，每次自动扩展大小为10M
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' autoextend on next 10M;
数据库已更改。
SQL> --注意使用的是alter database命令项
SQL> DESC DBA_FREE_SPACE
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> --在test表空间下再增加一个表空间
SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST02.DBF' size 20M autoextend on next 10M maxsize 500M;
表空间已更改。
SQL> --查询表空间使用率情况信息
SQL> col 表空间名称 for a10;
SQL> select a.tablespace_name "表空间名称",
2 a.bytes / 1024 / 1024 "空间总大小/MB",
3 (a.bytes - b.bytes) / 1024 / 1024 "空间已使用/MB",
4 b.bytes / 1024 / 1024 "空间剩余/MB",
5 round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "空间使用率"
6 from (select tablespace_name, sum(bytes) bytes
7 from dba_data_files
8 group by tablespace_name) a,
9 (select tablespace_name, sum(bytes) bytes, max(bytes) largest
10 from dba_free_space
11 group by tablespace_name) b
12 where a.tablespace_name = b.tablespace_name
13 order by ((a.bytes - b.bytes) / a.bytes) desc
14 /
表空间名称 空间总大小/MB 空间已使用/MB 空间剩余/MB 空间使用率
---------- ------------- ------------- ----------- ----------
SYSAUX 280 277.375 2.625 99.06
SYSTEM 490 484.75 5.25 98.93
EXAMPLE 100 77.6875 22.3125 77.69
USERS 5 3.375 1.625 67.5
UNDOTBS1 70 26.3125 43.6875 37.59
TEST 40 .5 39.5 1.25
DUSHUAI 100 .0625 99.9375 .06
已选择7行。
SQL> /*总结：增加表空间容量的方法主要有以下几种，1、在已存在的表空间中添加一个新的数据文件datafile；2、对已存在的数据文件进行扩容处理，使用alter database命令；**/
SQL> conn /as sysdba
已连接。
SQL> show user
USER 为 "SYS"
SQL> set sqlprompt "dushuai>>"
dushuai>>--更改dba_data_files的输出格式
dushuai>>col file_name for a40;
dushuai>>col tablespace_name for a10
dushuai>>col bytes for a10
dushuai>>select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE BYTES/1024/1024
---------------------------------------- ---------- ---------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TE TEST 20
ST.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TE TEST 20
ST02.DBF
dushuai>>--我们使用命令行增大指定表空间中数据文件的size大小
dushuai>>alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' resize 40M;
数据库已更改。
dushuai>>--检验一下更改后数据文件size是否生效
dushuai>>select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE BYTES/1024/1024
---------------------------------------- ---------- ---------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TE TEST 40
ST.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TE TEST 20
ST02.DBF
dushuai>>--下面我们进行表空间中数据文件移动路径的操作实验
dushuai>>--方法1：通过alter tablepace命令实现
dushuai>>--首先让数据文件所在的表空间离线，我们让test02这个数据文件移动到上级目录下
dushuai>>alter tablespace test offline;
表空间已更改。
dushuai>>--使用操作系统命令复制或移动数据文件路径，因为我们当前是windows环境，我们就使用图形化界面移动数据文件，移动完毕仅仅属于物理层面移动；
dushuai>>--我们还需要使用命令修改控制文件
dushuai>>alter tablespace test rename datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST02.DBF' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST02.DBF';
表空间已更改。
dushuai>>--我们让表空间在线online
dushuai>>alter tablespace test online;
表空间已更改。
dushuai>>col file_name for a40;
dushuai>>col tablespace_name for a15
dushuai>>col bytes for a10
dushuai>>select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
---------------------------------------- --------------- ---------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TE TEST 40
ST.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST02. TEST 20
DBF
dushuai>>--通过上面的查询语句我们看见操作已经成功
dushuai>>--下面我们使用方法2：alter database命令行将test.dbf移动到上级目录
dushuai>>--首先我们需要将数据库关闭
dushuai>>shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
dushuai>>--然后将数据库启动到mount状态
dushuai>>startup mount;
ORACLE 例程已经启动。
Total System Global Area 293601280 bytes
Fixed Size 1290208 bytes
Variable Size 222298144 bytes
Database Buffers 62914560 bytes
Redo Buffers 7098368 bytes
数据库装载完毕。
dushuai>>show user
USER 为 "SYS"
dushuai>>alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST.DBF';
数据库已更改。
dushuai>>col file_name for a40;
dushuai>>col tablespace_name for a15
dushuai>>col bytes for a15
dushuai>>select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST'
*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询
dushuai>>--上面的错误，是因为我们操作完毕后，没有将数据库状态变更为open状态
dushuai>>alter database open;
数据库已更改。
dushuai>>select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
---------------------------------------- --------------- ---------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST.DB TEST 40
F
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST02. TEST 20
DBF
dushuai>>--上面的查询结果让我们知道我们的操作已经成功
dushuai>>--总结：上面两种方法都是可以实现将指定数据文件修改文件路径的目的，但是推荐第一种方法，因为不需要全局关闭数据库，不影响其他表空间的用户使用，数据库的修改一定需要注意减小影响范围；
SQL> conn /as sysdba
已连接。
/*表空间语法汇总说明
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
**/
SQL> --使用including contents删除逻辑块
SQL> drop tablespace test including contents;
表空间已删除。
SQL> col file_name for a40
SQL> col tablespace_name for a15
SQL> select * from dba_data_files where tablespace_name='TEST';
未选定行
SQL> --但是此时已删除表空间中的原数据文件还是存放于物理磁盘上
SQL> --我们可以通过including contents and datafiles 进行彻底删除
SQL> drop tablespace test including contents and datafiles;
drop tablespace test including contents and datafiles
*
第 1 行出现错误:
ORA-00959: 表空间 'TEST' 不存在
SQL> drop tablespace dushuai including contents and datafiles;
表空间已删除。
SQL> spool off
转载于:https://blog.51cto.com/dushuai/1608292
2454
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
