Tablespace
From Oracle FAQ
<!-- start content -->
A tablespace
is a container for segments
(tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files
. Tables
and indexes
are created within a particular tablespace.
Oracle has a limit of 64,000 data files
per database.
<script type="text/javascript"> if (window.showTocToggle) { var tocShowText = "show"; var tocHideText = "hide"; showTocToggle(); } </script>
Default tablespaces
When a new database is created
, it will have the following tablespaces (as created by the Database Configuration Assistant
):
- SYSTEM
(the data dictionary
)
- SYSAUX
(optional database components)
- TEMP (temporary tablespace, see tablespace types below)
- UNDOTBS1 (undo tablespace, see tablespace types below)
- USERS (default users tablespace created)
Tablespace types
Different tablespace types can be created for different purposes:
Permanent tablespaces
Permanent tablespaces are used to store user data and user created
objects like tables, indexes and materialized views. Sample create
statements:
CREATE TABLESPACE tools DATAFILE '/u01/oradata/orcl/tools/file_1.dbf' SIZE 100M;
CREATE TABLESPACE tools DATAFILE 'C:\ORA\tools01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M;
Temp tablespaces
Temp or temporary tablespaces are used to store data with short
lifespan (transient data), for example: global temporarily tables or
sort results.
CREATE TEMPORARY
TABLESPACE temp TEMPFILE '/u01/oradata/orcl/temp/file_1.dbf' SIZE 100M;
With a single temp tablespace, the database will only write to one temp file
at a time. However, Temporary tablespace groups
, an Oracle 10g
feature, can be created to allow Oracle to write to multiple temp files simultaneously.
Undo tablespaces
Undo tablespaces are used to store "before image" data that can be used to undo transactions. See ROLLBACK
.
CREATE UNDO
TABLESPACE undots DATAFILE '/u01/oradata/orcl/undo/file_1.dbf' SIZE 20M;
Assign tablespaces to users
Users cannot create objects in a tablespace (even it's their default
tablespace) unless they have a quota on it (or UNLIMITED TABLESPACE
privilege). Some examples:
Grant user scott access to use all space in the tools tablespace:
ALTER USER scott QUOTA UNLIMITED ON tools;
Prevent user scott from using space in the system tablespace:
ALTER USER scott QUOTA 0 ON system;
Check free/used space per tablespace
Example query to check free and used space per tablespace:
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Sample output:
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1 65 17.8125 27 73
EXAMPLE 100 22.625 23 77
USERS 5 1.0625 21 79
TEMP 20 2 10 90
SYSAUX 625.125 54.5 9 91
SYSTEM 700 9.0625 1 99
Best practices
- Do not create objects in the SYSTEM tablespace. The system tablespace is reserved for the data dictionary
.
- Don't create tablespaces, with hundreds of small datafiles, these files needs to be checkpointed
, resulting is unnecessary processing.
分享到:
相关推荐
CREATE TABLESPACE命令详解 CREATE TABLESPACE命令详解
Mysql innodb tablespace 表空间实践
rlv方式增加oracle tablespace datafile
Oracle_create_tablespace语法详解.docx
1tablespace.sql
本文详细介绍和阐述了 Oracle 用户(user)和表空间(tablespace)的概念和使用方法等内容,并对用户和表空间使用过程中的注意事项、关键知识点等进行了重点标注和详尽解析,以便于读者进行深入学习和理解。...
create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步:创建数据表空间...
get_tablespace_usage.sh
select a.tablespace_name tablespace_name ,nvl(ceil((1 - b.free / a.total) * 100), 100) usage_of_tablespace% ,nvl(b.free, 0) left_space(M) ,c.extent_management Extent_management from (select tables
ERP管理系统资料:SAP专业教材资料T_Tablespace_Ext.doc
将设置目标用户的默认表空间为导入的目的表空间,在默认表空间上授予QUOTA UNLLIMITED,回收改用户的UNLIMITED TABLESPACE权限: 看一个简单的例子: SQL> CREATE TABLESPACE TESTEXP DATAFILE '/data/oradata/...
Oracle_tablespace_(表空间)的创建、删除、修改、扩展及检查等
CREATE TABLESPACECREATE TABLESPACECREATE TABLESPACECREATE TABLESPACECREATE TABLESPACE
oracle tablespace usage
-- Create the user create user BOS identified by "" default tablespace SYSTEM temporary tablespace TEMP profile DEFAULT password expire; -- Grant/Revoke role privileges grant connect to BOS; grant ...
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner, object_...
NULL 博文链接:https://j2ee2009.iteye.com/blog/1005083