oracle 查询表空间，新建表空间，赋权表空间，扩展表空间

oracle查看表空间的方法

一、Oracle查询表空间占用情况

SELECT a.tablespace_name "表空间名",

total "表空间大小",

free "表空间剩余大小",

(total - free) "表占用空间大小",

ROUND((total - free) / total * 100, 2) || '%' "已使用空间百分比"

FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

ORDER BY (total - free) DESC;

二、Oracle查询表占用的空间

SELECT segment_name "表名",

segment_type "对象类型",

sum(bytes) / 1024 / 1024 "占用空间（MB）"

FROM dba_extents

WHERE segment_name = '表名'

GROUP BY segment_name, segment_type;

三、Oracle查询表空间使用情况

SELECT tablespace_name,

file_id,

bytes,

blocks,

maxbytes

FROM dba_data_files;

四、Oracle查询每张表占用空间

SELECT owner,

segment_name,

segment_type,

sum(bytes) / 1024 / 1024 "占用空间（MB）"

FROM dba_segments

WHERE owner = '表所有者'

GROUP BY owner, segment_name, segment_type;

五、Oracle表空间大小查询

SELECT tablespace_name,

SUM(bytes) / 1024 / 1024 "占用空间（MB）"

FROM dba_segments

WHERE segment_type IN ('TABLE', 'INDEX')

GROUP BY tablespace_name;

六、Oracle查表空间大小

SELECT tablespace_name,

SUM(bytes) / 1024 / 1024 "占用空间（MB）"

FROM dba_segments

WHERE tablespace_name = '表空间名'

GROUP BY tablespace_name;

七、Oracle查询表空间大小

SELECT tablespace_name,

sum(bytes) / 1024 / 1024 "占用空间（MB）"

FROM dba_data_files

GROUP BY tablespace_name;

八、查看Oracle各个表占空间

SELECT owner,

segment_name,

segment_type,

format_number(bytes / (1024 * 1024), 2) "已用空间（MB）",

format_number(maxbytes / (1024 * 1024), 2) "最大空间（MB）"

FROM dba_segments

WHERE owner = '表所有者'

ORDER BY bytes DESC;

select TABLE_NAME,

COLUMN_NAME,

SEGMENT_NAME

from dba_lobs -- 对应的上面语句查到的segment_name名称，如SYS_LOB0000152921C00008$$

where SEGMENT_NAME='segment_name名称';

九、Oracle查看表空间大小

SELECT tablespace_name,

sum(blocks) * 8192 / 1024 / 1024 "表空间大小（MB）",

sum(blocks) * 8192 / 1024 / 1024 - sum(bytes) / 1024 / 1024 "剩余空间（MB）",

sum(bytes) / 1024 / 1024 "已用空间（MB）",

sum(MAXBYTES) / 1024 / 1024 "最大空间（MB）"

FROM (SELECT tablespace_name,

file_id,

MAX(blocks) MAXBLOCKS,

SUM(blocks) blocks

FROM dba_extents

GROUP BY tablespace_name, file_id),

(SELECT tablespace_name, file_id, SUM(bytes) bytes

FROM dba_free_space

GROUP BY tablespace_name, file_id)

WHERE tablespace_name NOT LIKE 'UNDO%'

AND tablespace_name NOT LIKE 'TEMP%'

AND tablespace_name NOT LIKE 'SYSAUX%'

AND tablespace_name NOT LIKE 'SYSTEM%'

AND tablespace_name NOT LIKE 'RMAN%'

AND tablespace_name NOT LIKE 'FLASH%'

GROUP BY tablespace_name;

1.查询用户所属的表空间

-- 1.查询所有用户所属的表空间

SELECT * FROM dba_tablespaces;

-- 2.查询用户和默认表空间的关系。

SELECT username,default_tablespace FROM dba_users;

-- 3.查询当前用户的表

SELECT table_name FROM user_tables;

-- 4.查询所有用户的表名：

SELECT table_name FROM all_tables;

-- 5.查询所有表名（其中包括系统表）

SELECT table_name FROM all_tables;

-- 6.查询表空间使用清空

SELECT a.tablespace_name "表空间名",

total "表空间大小",

free "表空间剩余大小",

(total - free) "表占用空间大小",

ROUND((total - free) / total * 100, 2) || '%' "已使用空间百分比"

FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

ORDER BY (total - free) DESC;

查看表空间大小

SELECT FILE_NAME AS 数据文件路径,TABLESPACE_NAME AS 表空间名称,AUTOEXTENSIBLE AS 自动扩展,STATUS AS 状态,MAXBYTES AS 可扩展最大值,USER_BYTES AS 已使用大小,INCREMENT_BY AS 自动扩展增量 FROM dba_data_files;

扩展表空间

-- 1.扩展空间，将数据文件扩大至1000MB

alter database datafile 'D:\DataBase\Test.DBF' resize 100m;

-- 2.自动增长，表空间不足时增加200MB，最大扩展50MB

alter database datafile 'D:\DataBase\Test.DBF' autoextend on next 200m maxsize 50m;

-- 3.扩展无限大空间

alter database DATAFILE 'D:\DataBase\Test.DBF' autoextend on maxsize unlimited;

注意表空间大小限制

表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K,8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块(由操作系统决定)

即：

4K最大表空间为: 16384M

8K最大表空间为: 32768M

16K最大表空间为: 65536M

32K最大表空间为: 131072M

64k最大表空间为: 262144M

为用户创建表空间

-- 创建表空间的基本SQL语句

CREATE TABLESPACE tablespace_name

DATAFILE 'datafile_path_size' SIZE datafile_size

[AUTOEXTEND ON NEXT extend_size MAXSIZE max_size]

[LOGGING | NOLOGGING];

-- tablespace_name 是您想要创建的表空间的名称。

-- datafile_path_size 是数据文件的路径和名称。

-- datafile_size 是数据文件的初始大小。

-- AUTOEXTEND ON 是数据文件自动扩展的选项。

-- NEXT extend_size 是数据文件每次自动扩展的大小。

-- MAXSIZE max_size 是数据文件可以自动扩展到的最大尺寸。

-- LOGGING | NOLOGGING 是日志记录选项，用于决定是否在数据库的重做日志中记录对表空间中数据的更改。

-- 这个例子创建了一个名为 users 的表空间，数据文件位于'D:\路径\USERS_01.DBF'初始大小为100MB，并且配置为当数据文件满时自动扩展，每次扩展10MB，无最大尺寸限制。同时启用了日志记录。

CREATE TABLESPACE users

DATAFILE 'D:\路径\USERS_01.DBF' SIZE 100M

AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED

LOGGING;

创建表空间赋权给用户

-- 创建表空间

create tablespace 表名称名称 datafile 'D:\路径\USERS_01.DBF' size 100M;

-- 赋权

ALTER USER 用户 QUOTA UNLIMITED ON 表名称名称;