select file_name,
tablespace_name,
bytes / 1024 / 1024 MB,
autoextensible,
maxbytes / 1024 / 1024 MAX_MB
from dba_temp_files;
--1 /data/oracle/app/oracle/oradata/orcl/temp01.dbf TEMP 30720 YES 32767.984375
--2 /data/oracle/orclspace/pancis3_temp.dbf PANCIS3_TEMP 1024 YES 1024
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name like 'TEMP%';
select *
from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';
--在表空间中，查询占用存储空间最大的表
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = upper('PANCIS3_DATA') GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;
SELECT * FROM IDX_GROUP_DIXB_AUTH
SELECT * FROM DBA_SEGMENTS WHERE OWNER='PANICS3';
--我们先查询表空间的占用情况，使用sql如下：
select upper(f.tablespace_name) "表空间名",
d.tot_grootte_mb "表空间大小(M)",
d.tot_grootte_mb - f.total_bytes "已使用空间(M)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 4 desc;
/*备注：
表空间是数据库的逻辑组成部分
从物理上将：数据库数据存放在数据文件中
从逻辑上将：数据库则是存放在表空间中
表空间由一个或是多个数据文件组成
*/
--1.查看用户下面的所有的表
SELECT * FROM USER_TABLES;
SELECT * FROM DBA_TABLES WHERE OWNER = 'COMM_USER';
--1.1查看表所在的表空间
select TABLE_NAME,TABLESPACE_NAME from dba_tables t where t.table_name='DM_LIST';
--1.2查看用户所属的表空间
SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME = 'SYSTEM';
--1.3.查看所有的表空间
SELECT * FROM DBA_TABLESPACES;
SELECT * FROM V$TABLESPACE;
--1.3查看默认的表空间
select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
--1.4创建索引到指定表空间
create index IDX_DM_FINISHTIME_ORGANID on DM_COMM_PREM_LIST (FINISH_TIME,ORGAN_ID) tablespace TBS_DW_DATA;
--1.5查看表空间下有多少用户，tablespace_name表空间 的名字一定要大写
SELECT DISTINCT S.OWNER
FROM DBA_SEGMENTS S
WHERE S.TABLESPACE_NAME = 'SYSTEM';
--1.6所有表空间的使用情况
SELECT --B.file_name "文件名",
A.TABLESPACE_NAME "表空间名",
TOTAL "表空间大小",
FREE "表空间剩余大小",
(TOTAL - FREE) "表空间使用大小",
TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,
-- file_name,
SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME/*,file_name*/) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
--查看剩余表空间的大小：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
--方法一：修改数据文件大小
alter tablespace 表空间名称
add datafile 表空间存放路径 size 表空间大小 autoextend on next 增长的大小 maxsize 空间最大值(如果不限制空间最大值就用unlimited)
例：alter tablespace GENERAL2
add datafile '/oradata1/res189/GENERAL201.dbf'
size 10G ; --这边我用了两分钟，公司的服务器
-- 查看表空间详情：
select f.* from dba_data_files f where lower(f.tablespace_name)='general2'--PANCIS3_DATA
--
5 /data/oracle/orclspace/pancis3_data.dbf 5 PANCIS3_DATA 34359721984 4194302 AVAILABLE 5 YES 34359721984 4194302 6400 34358624256 4194168 ONLINE
select 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 and b.tablespace_name='SYSTEM' group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;
--3.查看一下表空间是否开启自动扩展功能
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;
方法二：增加数据文件
alter database
datafile 表空间文件路径
AUTOEXTEND（自动扩展） ON NEXT 表空间满后增加的大小
--2、linux中查看服务器硬盘的使用情况
df -k 以KB为单位显示磁盘使用量和占用率
df -m 以M为单位显示磁盘使用量和占用率
[root@vpnics3 ~]# df -m
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/vg_248-lv_root
114688 45301 63561 42% /
tmpfs 5957 1532 4425 26% /dev/shm
/dev/sda1 477 118 334 27% /boot
/dev/mapper/vg_data-lv_data
302240 183879 103005 65% /data
/dev/sdd 1031996 262317 717251 27% /data1
/dev/sde 1031996 181754 797813 19% /data2
--3、给表空间新增数据文件并设置30G大小 在命令窗口中执行，数据文件的大小最大不能超过32G，
否则会报错：ORA-01144: 文件大小 (13107200 块) 超出 4194303 块的最大数
--方法1）给白空间增加数据文件
SQL> alter tablespace AUDIT add datafile '/pi/app/oracle/oradata/enterprise_tbs05.dbf' size 30G;
alter tablespace 表空间名 add datafile '数据文件存放路径' size 数据文件大小;
--方法2）手动增加数据文件尺寸
SQL>ALTER DATABASE AUDIT '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;
--方法3）设定数据文件自动扩展
SQL> ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;
执行完上述1、2、3.1)步骤之后：DBA_DATA_FILES中新增的数据文件的bytes的值要比maxbytes的值要大的原因？
原因：当一个datafile 手工resize到一个大于maxsize（maxbytes）的值之后，DBA_DATA_FILES中bytes的值要比maxbytes的值要大。
当该datafile被设置为autoextend on之后，dba_data_files中的maxbytes 值被自动结算为一个非零值。dba_data_files中的bytes是该datafile的当前的大小（以bytes为单位）
--查询临时表空间状态:
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
--查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--扩展临时表空间：
-- 方法一、增大临时文件大小：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize100m;
Database altered.
-- 方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
--最后发现临时表空间文件已经32G，达到最大文件大小，只好添加文件。
ALTER TABLESPACE TEMP ADD
TEMPFILE '/u02/oradata/vids/default_lib/temp03.dbf' SIZE 5M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
11M ./app/oracle/product/11.2.0/dbhome_1/ide
4.1G ./app/oracle/product/11.2.0/dbhome_1
4.1G ./app/oracle/product/11.2.0
4.1G ./app/oracle/product
4.0K ./app/oracle/checkpoints
29G ./app/oracle/oradata/orcl
29G ./app/oracle/oradata
45G ./app/oracle
45G ./app
34G ./orclspace
43M ./logmnr
78G .
--ORACLE数据库增加表空间大小或给表空间增加数据文件
----查询表空间使用情况---
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
--查看表空间是否具有自动扩展的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
select f.* from dba_data_files f
--Meathod2：新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE PANCIS3_DATA ADD DATAFILE
'/data/oracle/orclspace/pancis3_data01.dbf' SIZE 10240M
AUTOEXTEND ON NEXT 1024M MAXSIZE 30720M;
--增加表空间大小的四种方法
--Meathod1：给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
--Meathod2：新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
--Meathod3：允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
--Meathod4：手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
RESIZE 100M;
扫一扫
977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
