说明：该文章内容参考 oracle从入门到精通（第3版）
创建表空间
为了简化表空间的管理并提高系统性能，Oracle建议将不同类型的数据对象存放到不同的表空间中，因此，在创建数据库后，数据库管理员还应该根据具体应用的情况，建立不同类型的表空间。例如，建立专门用于存放表数据的表空间、建立专门用于存放索引或簇数据的表空间等，因此创建表空间的工作就显得十分重要，那么在创建表空间时必须要考虑以下几点。[插图] 是创建小文件表空间，还是大文件表空间（默认是小文件表空间）。[插图] 是使用局部盘区管理方式，还是使用传统的目录盘区管理方式（默认为局部盘区管理）。[插图] 是手工管理段空间，还是自动管理段空间（默认为自动）。[插图] 是否是用于临时段或撤销段的特殊表空间。
一个数据库包括多个表空间，每一个表空间又包含一个或多个数据文件。表空间可以看成是Oracle数据库的逻辑结构，而数据文件可以看成是Oracle数据库的物理结构。
1、创建表空间语法
创建表空间的语法如下：
CREATE [SMALLFILE/BIGFILE] TABLESPACE tablespace_name
DATAFILE '/path/filename'
SIZE num[k/m] REUSE
[,'/path/filename'] SIZE num[k/m] REUSE]
[,...]
[AUTOEXTEND [ON|OFF] NEXT num[k/m] ] MAXSIZE [UNLIITED | num[k/m]]]]
[MININUM EXTENT num[k/m]]
[DEFAULT STORAGE storage]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
[PERMANENT|TEMPORARY]
[EXTENT MANAGEMENT DICTIONARY | LOCAL[AUTOALLOCATE] | UNIFORM SIZE num[k/m]]]]
1.1语法中的关键字
SMALLFILE/BIGFILE：表示创建的是小文件表空间还是大文件表空间。
AUTOEXEND [ON | OFF] NEXT：表示数据文件为自动扩展（ON）或非自动扩展（OFF），如果是自动扩展，则需要设置NEXT的值。
MAXSIZE：表示当数据文件自动扩展时，允许数据文件扩展的最大字节数，如果指定UNLIMITED关键字，则不需要指定字节长度。
MINIMUN EXTENT：指定最小的长度，由操作系统和数据库的块决定。
ONLINE | OFFLINE：创建表空间时可以指定为在线或离线。
PERMANENT|TEMPORARY：指定创建的表空间是永久表空间或临时表空间，默认为永久性表空间。
LOGGING | NOLOGGING：指定该表空间内的表在加载数据时是否产生日志，默认为产生日志（LOGGING）。即使设置为NOLOGGING，但在进行INSERT、UPDATE和DELETE操作时，Oracle仍会将操作信息记录到Redo Log Buffer中。
EXTENT MANAGEMENT DICTIONARY | LOCAL：指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。Oracle不推荐使用数据字典管理表空间。
AUTOALLOCATE | UNIFORM SIZE：如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。若是按照等同大小进行，则默认每次扩展的大小为1MB。
1.2 语法中的参数
tablespace_name：该参数表示要创建的表空间的名称。
‘/path/filename’：该参数表示数据文件的路径与名字。
REUSE表示若该文件存在，则清除该文件再重新建立该文件，若该文件不存在，则创建该文件。
DEFAULT STORAGE storage：指定以后要创建的表、索引及簇的存储参数值，这些参数将影响以后表等对象的存储参数值。
2、通过本地化管理方式创建表空间
本地化表空间管理使用位图跟踪表空间所对应的数据文件的自由空间和块的使用状态，位图中的每个单元对应一个块或一组块。当分配或释放一个扩展时，Oracle会改变位图的值以指示该块的状态。这些位图值的改变不会产生回滚信息，因为它们不更新数据字典的任何表。所以，本地管理表空间具有以下优点。
使用本地化的扩展管理功能（包括自动大小和等同大小两种），可以避免发生重复的空间管理操作。[插图] 本地化管理的自动扩展（AUTOALLOCATE）能够跟踪临近的自由空间，这样可以消除结合自由空间的麻烦。本地化的扩展大小可以由系统自动确定（AUTOALLOCATE），也可以选择所有扩展有同样的大小（UNIFORM）。通常使用EXTENT MANAGEMENT LOCAL子句创建本地化的可变表空间。下面来看两个创建表空间的例子，一个是指定等同的扩展大小，另一个是由系统自动指定扩展大小。
通过本地化管理方式（LOCAL）创建一个表空间，其扩展大小为等同的256KB，代码及运行结果如下。
create tablespace test_01 datafile '/home/oracle/oracledata/ORCL/test-01.dbf'
size 10m
extent management local uniform size 256k;
通过本地化管理方式（LOCAL）创建一个表空间，其扩展大小为自动管理，代码及运行结果如下。
create tablespace test_02 datafile '/home/oracle/oracledata/ORCL/test-02.dbf'
size 10m
extent management local autoallocate;
3、通过段空间管理方式创建表空间
段空间管理方式是建立在本地化空间管理方式基础之上的，即只有本地化管理方式的表空间，才能在其基础上进一步建立段空间管理方式。它使用SEGMENT SPACE MANAGEMENT MANUAL/LOCAL语句，段空间管理又可分为手工段和自动段两种空间管理方式。
3.1 手工段空间管理方式
手工段空间管理方式是为了向后兼容而保留的，它使用自由块列表和PCT_FREE与PCT_USED参数来标识可供插入操作使用的数据块。在每个INSERT或UPDATE操作后，数据库都会比较该数据块中的剩余自由空间与该段的PCT_FREE设置。如果数据块的剩余自由空间少于PCT_FREE自由空间（也就是说剩余空间已经进入系统的下限设置），则数据库就会从自由块列表上将其取下，不再对其进行插入操作。剩余的空余空间保留给可能会增大该数据块行大小的UPDATE操作。而在每个UPDATE操作或DELETE操作后，数据库会比较该数据块中的已用空间与PCT_USED设置，如果已用空间少于PCT_USED已用空间（也就是已用空间未达到系统的上限设置），则该数据块会被加入到自由列表中，供INSERT操作使用。下面来看一个实例。
通过本地化管理方式（LOCAL）创建一个表空间，其扩展大小为自动管理，其段空间管理方式为手工，代码及运行结果如下。
create tablespace test_03 datafile '/home/oracle/oracledata/ORCL/test-03.dbf'
size 20m
extent management local autoallocate
segment space management manual;
3.2 自动段空间管理方式
如果采用自动段空间管理方式，那么数据库会使用位图而不是自由列表来标识哪些数据块可以用于插入操作，哪些数据块需要从自由块列表上将其取下。此时，表空间段的PCT_FREE和PCT_USED参数会被自动忽略。
由于自动段空间管理方式比手工段空间管理方式具有更好的性能，因此它是创建表空间时的首选方式，下面来看一个实例。
通过本地化管理方式（LOCAL）创建一个表空间，其扩展大小为自动管理，其段空间管理方式为自动，代码及运行结果如下
create tablespace test_04 datafile '/home/oracle/oracledata/ORCL/test-04.dbf'
size 20m
extent management local autoallocate
segment space management auto;
4 创建非标准块表空间
在Oracle数据库中，通常的块大小为8192B，即8KB，但Oracle 11g允许创建块大小与基本块不同的表空间。块大小可由创建表空间时的BLOCKSIZE参数指定，这样有利于存储不同大小的对象，但用户需要注意以下3点。
1.表空间的非标准块的大小为基本块的倍数。如大小为16KB、64KB、128KB。
2.oracle11g通常使用SGA自动共享内存管理，因此需要设置初始化参数DB_16K_CACHE_SIZE= 16K。
3.这种块较大的表空间通常用来存放大对象（LOB）类型。
创建一个非标准块的表空间，块的大小为标准块的2倍，代码及运行结果如下。
create tablespace test_05 datafile '/home/oracle/oracledata/ORCL/test-0.dbf'
size 64m
autoextend on next 4m maxsize unlimited
blocksize 16k
extent management local autoallocate
segment space management auto;
create tablespace test_05 datafile '/home/oracle/oracledata/ORCL/test-0.dbf'
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
设置下块大小
alter system set db_16k_cache_size = 16m scope=both;
create tablespace test_05 datafile '/home/oracle/oracledata/ORCL/test-0.dbf'
size 64m
autoextend on next 4m maxsize unlimited
blocksize 16k
extent management local autoallocate
segment space management auto;
5 建立大文件表空间
从Oracle 11g版本开始，引进了一个新的表空间类型——大文件（BIGFILE）。与以前版本的最多可由1022个文件组成的表空间不同，大文件表空间存放在一个单一的数据文件中，并且它需要更大的磁盘容量来存放数据。大文件表空间可以根据选择的块的大小而变化，从32TB增至128TB。
大文件表空间是为超大型数据库而设计的。当一个超大型数据库具有上千个读／写数据文件时，必须更新数据文件头部（如检查点）的操作可能会花费相当长的时间。如果降低数据文件的数量，那么，这些操作完成起来就可能会快很多。创建一个大文件表空间，只需要在CREATE语句中使用BIGFILE关键字即可，下面来看一个例子。
创建一个大文件表空间，指定一个数据文件，并且数据文件的大小为2GB，代码及运行结果如下。
create tablespace test_06 datafile '/home/oracle/oracledata/ORCL/test-06.dbf'
size 2g;
由于大文件表空间只有一个数据文件，因此当需要重新设置其大小时不需要标识数据文件的具体路径和名称，只需要使用ALTERTABLESPACE命令指定大文件表空间的名称，即可很方便地修改其大小。另外，需要注意的是：在创建表空间的语法中使用SIZE来标识数据文件的大小，而在修改表空间时，要使用RESIZE关键字来重置数据文件的大小，下面来看一个例子。
修改大文件表空间test_06，将其空间大小由2GB改变为1GB，代码及运行结果如下。
SQL> alter tablespace test_06 resize 1g;
alter tablespace test_06 resize 1g
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST_06
SQL> alter tablespace test_06 resize 3g;
alter tablespace test_06 resize 3g
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST_06
oracle从入门到精通（第3版）使用oracle11g是可以执行成功的，该文章使用的是oracle 19c执行报错。解决方法如下：
select file_id,tablespace_name from dba_data_files;
FILE_ID
----------
TABLESPACE_NAME
14
TEST_06
SQL> alter database datafile 14 resize 1g;
Database altered.
验证是否改变
再次更改为3g
alter database datafile 14 resize 3g;
与大文件表空间不同的是，传统表空间可能包含多个数据文件，如果要改变其大小，则需要在ALTER DATABASE语句后面指定完整的操作系统路径名或内部文件号辨别每个数据文件，然后再重新设置它们的大小。下面来看一个简单的例子。
把数据文件test-01.dbf（其所属的表空间是test_01）的大小由原来的10MB修改为100MB，代码及运行结果如下。
alter database datafile '/home/oracle/oracledata/ORCL/test-01.dbf' resize 100m;
验证是否更改
维护表空间与数据文件
1 设置默认表空间
在Oracle数据库中创建用户（使用CREATE USER语句）时，如果不指定表空间，则默认的临时表空间是TEMP，默认的永久表空间是SYSTEM，这样就导致应用系统与Oracle系统竞争使用SYSTEM表空间，会极大地影响Oracle系统的执行效率。为此，Oracle建议将非SYSTEM表空间设置为应用系统的默认永久表空间，并且将非TEMP临时表空间设置为应用系统的临时表空间。这样有利于数据库管理员根据应用系统的运行情况适时调整默认表空间和临时表空间。
将临时表空间test_07设置为临时表空间，代码如下
先创建一个临时表空间
create temporary tablespace test_07 tempfile '/home/oracle/oracledata/ORCL/test-07.dbf'
size 10m;
设置test_07为默认临时表空间
alter database default temporary tablespace test_07;
设置test_01为默认表空间
alter database default tablespace test_01;
3 查看某用户的默认临时表空间和默认表空间
方法1
SQL> conn sea/oracle123;
Connected.
SQL> select username,temporary_tablespace,default_tablespace from user_users;
方法2
select username,temporary_tablespace,default_tablespace from dba_users where username='SEA';
4更改表空间的状态
表空间有只读和可读写两种状态。
设置表空间为只读并不是可以随意进行的，必须要满足下列条件。
1.该表空间必须为ONLINE状态。
2.该表空间不能包含任何回滚段。
3.该表空间不能在归档模式下。
修改test_01表空间为只读状态，代码及运行结果如下
alter tablespace test_01 read only;
修改test_01表空间为可读写状态，代码及运行结果如下
alter tablespace test_01 read write;
5重命名表空间
将test_01表空间重命名为test_01_new
alter tablespace test_01 rename to test_01_new;
6 删除表空间
在默认情况下，Oracle系统不采用Oracle Managed Files方式管理文件，这样删除表空间实际上仅是从数据字典和控制文件中将该表空间的有关信息清除掉，并没有真正删除该表空间包含的所有物理文件。因此，要想彻底删除表空间来释放磁盘空间，在执行删除表空间的命令之后，还需要手工删除该表空间包含的所有物理文件。
当Oracle系统采用Oracle Managed Files方式管理文件时，删除某个表空间后，Oracle系统将自动删除该表空间包含的所有物理文件。删除表空间需要使用DROP TABLESPACE命令，其语法格式如下。
DROP TABLESPACE tbs_name[INCLUDING CONTENTS] [CASCADE CONSTRAINTS]
1.tbs_name：表示要删除的表空间名称。
2.INCLUDING CONTENTS：表示删除表空间的同时删除表空间中的数据。如果不指定INCLUDING CONTENTS参数，而该表空间又存有数据时，则Oracle会提示错误。
3.CASCADE CONSTRAINTS：表示当删除当前表空间时也删除相关的完整性限制。完整性限制包括主键及唯一索引等。如果完整性存在，而没有CASCADE CONSTRAINTS参数，则删除时Oracle会提示错误，并且不会删除该表空间。
删除test_01_new表空间
drop tablespace test_01_new
including contents
cascade constraints;
维护表空间中的数据文件
维护表空间中的数据文件主要包括向表空间中添加数据文件、从表空间中删除数据文件和对表空间中的数据文件进行自动扩展设置，下面分别进行讲解。
1．向表空间中添加数据文件
当某个非自动扩展表空间的扩展能力不能满足新的扩展需求时，数据库管理员就需要向表空间中添加新的数据文件（如添加一个能够自动扩展的表空间），以满足数据对象的扩展需要。下面来看一个向表空间添加新数据文件的例子。
向test_02表空间中添加一个新的数据文件test-02-add01.dbf,该文件支持自动扩展，扩展能力为每次扩展5MB，并且该文件的最大空间不受限制，代码及运行结果如下。
alter tablespace test_02 add datafile '/home/oracle/oracledata/ORCL/test-02-add01.dbf'
size 10m autoextend on next 5m maxsize unlimited;
2．从表空间中删除数据文件
在Oracle 11g R2以前的版本中，Oracle系统一直只允许增加数据文件到表空间，而不允许从表空间中删除数据文件。
删除test_02表空间中的test-02.dbf数据文件。
alter tablespace test_02 drop datafile '/home/oracle/oracledata/ORCL/test-02-add01.dbf';
注意：不能drop创建该表空间的时候指定的表空间。
SQL> alter tablespace test_02 drop datafile ‘/home/oracle/oracledata/ORCL/test-02.dbf'；
alter tablespace test_02 drop datafile '/home/oracle/oracledata/ORCL/test-02.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST_02
3．对数据文件的自动扩展设置
Oracle数据库的数据文件可以设置成具有自动扩展的功能，当数据文件剩余的自由空间不足时，它会按照设定的扩展量自动扩展到指定的值。这样可以避免由于剩余表空间不足而导致数据对象需求空间扩展失败的问题。
可以通过AUTOEXTEND ON命令将数据文件设置为在使用中能根据需求自动扩展。用户可以通过以下4种方式设置数据文件的自动扩展功能。
1.在CREATE DATABASE语句中设置。
2. 在ALTER DATABASE语句中设置。
3.在CREATE TABLESPACE语句中设置。
4.在ALTER TABLESPACE语句中设置。
首先查看test_02表空间数据文件是否为自动扩展。
SQL> select file_name,autoextensible from dba_data_files where tablespace_name='TEST_02';
FILE_NAME
---------------------
AUTOEXTEN
---------
/home/oracle/oracledata/ORCL/test-02.dbf
NO
注意:TEST_02要大写，小写查不出数据。
修改表空间test_02的数据文件为自动扩展
alter database datafile '/home/oracle/oracledata/ORCL/test-02.dbf'
autoextend on next 5m maxsize unlimited;
标题
查询OGG表空间内存放的数据对象及其类型和拥有者。
SQL> select segment_type,segment_name,owner from dba_segments where tablespace_nam'OGG';
在system模式下，从dba_data_files数据字典中查询表空间及其包含的数据文件。
select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;
查询OGG表空间所存放的用户及其所拥有的对象数量。
select owner as sea,count(segment_name) as 对象数量 from dba_segments where tablespace_name='OGG' group by owner;
登录
登录数据库，shell命令行执行
sqlplus ogg/oracle123 #ogg用户登录
sqlplus / as sysdba #超级用户登录
启动数据库
startup nomount --到非挂载模式
alter database mount; --到挂载模式
alter database archivelog; --启动归档
alter database open; --open
切换用户
conn ogg/oracle123; #切换到ogg用户，ogg用户密码为oracle123
conn / as sysdba; #切换到超级用户
查询未使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
查询
查询oracle数据库名
SQL> SELECT name FROM v$database;
NAME
---------------------------
ORCL
查询oracle的实例名
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
orcl
查询oracle的服务名
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL
查询所有表空间大小
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
查询有哪些表空间
select tablespace_name from dba_tablespaces;
查询表空间有哪些表
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='表空间名';
查看当前用户的表
select table_name from user_tables;
查询表中数据
SQL> conn test_ogg/test_ogg;
Connected.
SQL> select * from test_ogg;
ID NAME
---------- ------------------------------------------------------------
2 test
3 test3
4 test4
5 test5
6 test6
7 test7
1 test
7 rows selected.
查询当前用户下的所有表
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TEST_USER
TEST3
TEST2
触发器
查看某个用户下的触发器
查看触发器
SQL> conn test/test；
SQL> select TRIGGER_NAME from USER_TRIGGERS;
TRIGGER_NAME
--------------------------------------------------------------------------------
TEST_USER_SEQ_TR
TEST2_SEQ_TR
删除触发器
SQL> DROP TRIGGER TEST2_SEQ_TR;
Trigger dropped.
确认是否删除
SQL> select TRIGGER_NAME from USER_TRIGGERS;
TRIGGER_NAME
--------------------------------------------------------------------------------
TEST_USER_SEQ_TR
序列
查看某用户下序列名字
SQL> select sequence_name from user_sequences;
SEQUENCE_NAME
--------------------------------------------------------------------------------
TEST_USER_SEQ
SQL> select * from user_sequences;
SEQUENCE_NAME
--------------------------------------------------------------------------------
MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER SCA EXT SHA
---------- ---------- ------------ --- --- ---------- ----------- --- --- ---
SES KEE
--- ---
TEST2_SEQ
1 1.0000E+28 1 N N 20 1 N N N
N N
TEST_USER_SEQ
1 1.0000E+28 1 N N 20 10 N N N
N N
SEQUENCE_NAME
--------------------------------------------------------------------------------
MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER SCA EXT SHA
---------- ---------- ------------ --- --- ---------- ----------- --- --- ---
SES KEE
--- ---
SQL> DROP SEQUENCE TEST2_SEQ;
Sequence dropped.
确认是否删除
SQL> select * from user_sequences;
SEQUENCE_NAME
--------------------------------------------------------------------------------
MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER SCA EXT SHA
---------- ---------- ------------ --- --- ---------- ----------- --- --- ---
SES KEE
--- ---
TEST_USER_SEQ
1 1.0000E+28 1 N N 20 10 N N N
N N
索引
查看test用户下的索引名字
SQL> conn test/test;
Connected.
SQL> select index_name from user_indexes;
INDEX_NAME
--------------------------------------------------------------------------------
SYS_C007616
TEST_MODIFIED_INDEX
SYS_C007628
删除
删除空的表空间，但是不包含物理文件
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> select * from student5;
SNO SNAME SSEX SBIRTHDAY SCLASS
---------- --------------------------- --------- --------------- ---------------
131 曾华 男 01-SEP-77 95033
132 曾华 男 01-SEP-77 95033
133 曾华 男 01-SEP-77 95023
134 牛顿 男 01-SEP-77 95023
135 牛顿 男 01-SEP-77 95023
132 牛顿 男 01-SEP-77 95023
132 牛顿 男 01-SEP-77 95023
133 牛顿 男 01-SEP-77 95023
134 牛顿 男 01-SEP-77 95023
135 牛顿 男 01-SEP-77 95023
136 霍金 男 01-SEP-77 95023
SNO SNAME SSEX SBIRTHDAY SCLASS
---------- --------------------------- --------- --------------- ---------------
138 爱因 男 01-SEP-77 95023
12 rows selected.
SQL> delete from student5 where sno=131;
1 row deleted.
SQL> select * from student5;
SNO SNAME SSEX SBIRTHDAY SCLASS
---------- --------------------------- --------- --------------- ---------------
132 曾华 男 01-SEP-77 95033
133 曾华 男 01-SEP-77 95023
134 牛顿 男 01-SEP-77 95023
135 牛顿 男 01-SEP-77 95023
132 牛顿 男 01-SEP-77 95023
132 牛顿 男 01-SEP-77 95023
133 牛顿 男 01-SEP-77 95023
134 牛顿 男 01-SEP-77 95023
135 牛顿 男 01-SEP-77 95023
136 霍金 男 01-SEP-77 95023
138 爱因 男 01-SEP-77 95023
11 rows selected.
删除占用连接的用户
SQL> drop user ogg cascade;
drop user ogg cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> select sid,serial# from v$session where username='ogg';
no rows selected
SQL> select sid,serial# from v$session where username='OGG';
SID SERIAL#
---------- ----------
1 61401
81 36607
SQL> alter system kill session '1,61401';
System altered.
SQL> alter system kill session '81,36607';
System altered.
SQL> drop user ogg cascade;
User dropped.
48万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
