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 表名称名称;
