Oracle数据库表空间超详细介绍（oracle表空间最大多大）硬核推荐

113 0 0

目录表空间概述表空间的分类创建表空间参数详解1、undo2、tablespace3、datafile datefile_spec14、MININUM EXTENT integer [k|m]5、BLOCKSIZE integer [k]6、logging clause7、FORCE LOGGING8、DEFAULT storage_clause9、online|offline10、PERMANENT|TEMPORARY11、extent_management_clause12、segment_management_clause修改表空间名称查询表空间剩余字节大小dba_free_space表介绍 dba_date_files表介绍查看表空间大小以及使用率查询表空间所有数据文件路径为空间不足的表空间增加数据文件查看临时表空间的大小和数据文件路径重建并修改默认临时表空间办法查表空间使用率情况（含临时表空间）查看表空间大小以及使用率查看用户使用的表空间修改数据文件大小查看用户所在的表空间创建用户时指定表空间修改用户对应的表空间拓展表空间总结

Oracle的表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优。（Oracle数据库独特的高级应用）。

**永久表空间：**存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。

**临时表空间：**存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有temp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。

**UNDO表空间：**保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

CREATE [UNDO] TABLESPACE tablespace_name
[DATAFILE datefile_spec1 [,datefile_spec2] ……
[{MININUM EXTENT integer [k|m]
|BLOCKSIZE integer [k]
|logging clause
|FORCE LOGGING
|DEFAULT {data_segment_compression} storage_clause
|[online|offline]
|[PERMANENT|TEMPORARY]
|extent_manager_clause
|segment_manager_clause}]

说明系统将创建一个回滚表空间。

数据库管理员可以不必管理回滚段，只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。当然,也可以创建一般的表空间,在上面创建回滚段.不过对于用户来说,系统管理比自己理要好很多.如果需要自己管理,当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。

指出表空间的名称

指出表空间包含什么空间文件。datefile_spec1 是形如 [‘filename’] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause]，

[autoextend_clause]是形如： AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] }，

其中filename是数据文件的全路径名，size是文件的大小,REUSE表示文件是否被重用，

AUTOEXTEND表明是否自动扩展. OFF | ON 表示自动扩展是否被关闭.NEXT 表示数据文件满了以后,扩展的大小，

maxsize_clause表示数据文件的最大大小.形如MAXSIZE { UNLIMITED | integer [ K | M ] }.UNLIMITED 表示无限的表空间.integer是数据文件的最大大小，

DATAFILE ‘D:”oracle”oradata”IMAGEDATA01.dbf’ SIZE 2000M，

‘D:”oracle”oradata”IMAGEDATA02.dbf’ SIZE 2000M

指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。

这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db_block_size,至少一个db_nk_block_size,并且声明的integer的值必须等于db_nk_block_size。

注意：在临时表空间不能设置这个参数。

这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），包括表，索引，分区，物化视图，物化视图上的索引，分区。

使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。

注意：设置这个参数数据库不行open并且出于读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。

声明缺省的存储子句。

改变表空间的状态。online使表空间创建后立即有效.这是缺省值.offline使表空间创建后无效.这个值，可以从dba_tablespace中得到。

指出表空间的属性，是永久表空间还是临时表空间。永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。这个参数生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。如果要创建本地管理表空间，必须使用create temporary tablespace。

注意，声明了这个参数后，不能声明block size

说明了表空间如何管理范围。一旦声明了这个子句，只能通过移植的方式改变这些参数。

如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。autoallocate说明表空间自动分配范围，用户不能指定范围的大小。只有9.0以上的版本具有这个功能。uniform说明表空间的范围的固定大小，缺省是1m。不能将本地管理的数据库的system表空间设置成字典管理。

如果没有设置extent_management_clause，oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。如果大于9.0.0,那么按照如下设置：

如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。

否则，如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件，oracle创建一个本地管理表空间，extent size是initial.如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。

如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。如果满足oracle创建一个本地管理表空间并指定uniform。否则oracle将创建一个自动分配的本地管理表空间。

注意：本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary。

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

实例：

CREATE TABLESPACE YNCCIP –表空间名
DATAFILE ‘/home/u02/oradata/orcl/YNCCIP01.dbf’ –表空间对应的数据文件
SIZE 100M –数据文件大小
AUTOEXTEND ON NEXT 10M –数据文件不够用自动扩展，每次扩展大小
MAXSIZE 1000M –数据文件最大文件大小
LOGGING –启动重做日志
PERMANENT –指定表空间为永久性的表空间
EXTENT MANAGEMENT LOCAL AUTOALLOCATE –指定新建表空间为本地管理方式的表空间
BLOCKSIZE 16K –块大小
SEGMENT SPACE MANAGEMENT auto –指定本地管理表空间中段的存储管理方式，AUTO自动，MANUAL手工。

create tablespace NNC_INDEX01 datafile ‘D:\oracle\NNC_INDEX01.DBF’
size 500M autoextend on next 50M extent management local uniform size 256k;

alter tablespace TEST rename to TEST1;

select tablespace_name, sum(bytes)/1024/1024 as “free space(m)”
from dba_free_space
where tablespace_name=’&tablespace_name’
group by tablespace_name;

注：如果是临时表空间，请查询DBA_TEMP_FREE_SPACE

select tablespace_name, free_space/1024/1024 as “free space(m)”
from dba_temp_free_space
where tablespace_name=’&tablespace_name’;

字段名称字段含义TABLESPACE_NAME表示表空间名字FILE_ID表示表空间所在文件ＩＤBLOCK_ID表示空闲块开始的块ＩＤBYTES表示空闲的字节数BLOCKS表示空闲块数量RELATIVE_FNO表示相对文件ＩＤ

SQL> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
———————————————————— ———- ———- ———- ———- ————
SYSTEM 1 111096 65536 8 1
SYSTEM 1 112896 8388608 1024 1
SYSAUX 3 66728 30081024 3672 3
UNDOTBS1 4 472 262144 32 4
UNDOTBS1 4 1024 78643200 9600 4
UNDOTBS1 4 10752 121634816 14848 4
UNDOTBS1 4 25608 13631488 1664 4
UNDOTBS1 4 27280 54394880 6640 4
UNDOTBS1 4 34944 5242880 640 4
UNDOTBS1 4 35712 53477376 6528 4
UNDOTBS1 4 42368 2097152 256 4
UNDOTBS1 4 42752 917504 112 4
UNDOTBS1 4 43912 786432 96 4
UNDOTBS1 4 44016 1179648 144 4
USERS 7 344 2424832 296 7
ZHANGTEST 13 199456 96206848 11744 13
已选择 16 行。
SQL>

字段名称字段含义FILE_NAME文件名字FILE_ID文件ID，整个数据库中每个文件的ID都是唯一的。TABLESPACE_NAME文件所属的表空间，ORACLE中每个数据文件都和表空间是对应的。BYTES文件字节数量BLOCKS文件的块数量，和BYTES是可以换算的。（BYTES/1024/BLOCK_SIZE就可计算得到BLOCKS数量）STATUS状态表示，文件当前是否可用。RELATIVE_FNO相对文件号。相对文件号只在表空间唯一，就是说每个表空间都有自己的相对文件号AUTOEXTENSIBLE是否自动扩展MAXBYTES如果可以扩展，最大可以到多大？（12C是 3.4360E+10，就是32G）MAXBLOCKS如果可以扩展，最大可以多少数据块？INCREMENT_BY每次增加的块数量USER_BYTES文件中实际有用的字节数。USER_BLOCKS文件中实际有用的块。ONLINE_STATUS在线状态。

SELECT
B.FILE_NAME 物理文件名,
B.TABLESPACE_NAME 表空间名称,
B.BYTES/1024/1024 大小M,
(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,
SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 使用率
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
ORDER BY B.TABLESPACE_NAME;

select tablespace_name, file_id, file_name, bytes/1024/1024 as “bytes(m)”
from dba_data_files
where tablespace_name=’&tablespace_name’;

注：如果是临时表空间，请查询DBA_TEMP_FILES

select tablespace_name, file_id, file_name, bytes/1024/1024 as “space(m)”
from dba_temp_files
where tablespace_name=’&tablespace_name’;

alter tablespace &tablespace_name add datafile ‘&datafile_name’ size 2G;

注：如果要为临时表空间扩容，使用下面的语句

alter tablespace &tablespace_name add tempfile ‘&datafile_name’ size 2G;

select tablespace_name, file_id, file_name, bytes/1024/1024 as “space(m)”
from dba_temp_files
where tablespace_name=’temp’;

或者

select name, bytes/1024/1024 as “大小(M)” from v$tempfile order by bytes;

查询当前数据库默认临时表空间名

select * from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;

创建新的临时表空间

create temporary tablespace temp02 tempfile ‘E:\oracle\oradata\lims\TEMP02.DBF’ size 1024M autoextend on;

修改默认表空间为刚刚建立的临时表空间

alter database default temporary tablespace temp02;

查看用户所用临时表空间的情况

select USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;

删除原来的临时表空间

drop tablespace temp including contents and datafiles;

查看所有表空间名确认临时表空间是否已删除

select tablespace_name from dba_tablespaces;

select d.tablespace_name “name”, d.status “status”,
to_char (nvl (a.bytes / 1024 / 1024, 0), ‘99,999,990.90’) “size (m)”,
to_char (nvl (a.bytes – nvl (f.bytes, 0), 0) / 1024 / 1024,’99999999.99′) use,
to_char (nvl ((a.bytes – nvl (f.bytes, 0)) / a.bytes * 100, 0),’990.00′) “used %”
from sys.dba_tablespaces d,
(select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum (bytes) bytes from dba_free_space group by tablespace_name) f
where d.tablespace_name=a.tablespace_name(+)
and d.tablespace_name=f.tablespace_name(+)
and not (d.extent_management like ‘local’ and d.contents like ‘temporary’)
union all
select d.tablespace_name “name”, d.status “status”,
to_char (nvl (a.bytes / 1024 / 1024, 0), ‘99,999,990.90’) “size (m)”,
to_char (nvl (t.bytes, 0) / 1024 / 1024, ‘99999999.99’) use,
to_char (nvl (t.bytes / a.bytes * 100, 0), ‘990.00’) “used %”
from sys.dba_tablespaces d,
(select tablespace_name, sum (bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum (bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
where d.tablespace_name=a.tablespace_name(+)
and d.tablespace_name=t.tablespace_name(+)
and d.extent_management like ‘local’
and d.contents like ‘temporary’;

select
b.file_name 物理文件名,
b.tablespace_name 表空间名称,
b.bytes/1024/1024 大小m,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用m,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 使用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

select username,default_tablespace from dba_users;

alter database datafile ‘D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF’ resize 10240M;

select username,default_tablespace from dba_users order by username;

create user username identified by passworddefault tablespace user_datatemporary tablespace user_temp;

alter user c##zhang default tablespace ZHANGTEST01;

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

方法一：增加数据文件

ALTER TABLESPACE game ADD DATAFILE ‘/oracle/oradata/db/GAME02.dbf’ SIZE 1000M;

方法二：手动增加数据文件尺寸

ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf’ RESIZE 4000M;

方法三：设定数据文件自动扩展

ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

到此这篇关于Oracle数据库表空间超详细介绍的文章就介绍到这了,更多相关Oracle表空间内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:Oracle 查看表空间的大小及使用情况sql语句oracle 创建表空间详细介绍Oracle如何更改表空间的数据文件位置详解Oracle修改表空间大小的方法Oracle 删除用户和表空间详细介绍Oracle表空间查看sql使用情况Oracle删除表及查看表空间的实例详解oracle 创建表空间步骤代码oracle表空间扩容详情

# 随心笔谈