蓝帆天诚
一、表空间分配的大小和真实使用空间查看：
CLEAR COLUMNS BREAKS COMPUTES
SET ECHO OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 160
SET PAGESIZE 2000
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES
COLUMN status FORMAT a9 HEADING 'Status'
COLUMN name FORMAT a30 HEADING 'TS. Name'
COLUMN type FORMAT a15 HEADING 'TS Type'
COLUMN extent_mgt FORMAT a10 HEADING 'Ext.Mgt.'
COLUMN segment_mgt FORMAT a10 HEADING 'Seg.Mgt.'
COLUMN ts_size FORMAT 999,999,999 HEADING 'TS.Size (MB)'
COLUMN used FORMAT 999,999,999 HEADING 'Used (MB)'
COLUMN free FORMAT 999,999,999 HEADING 'Free (MB)'
COLUMN pct_used FORMAT 999 HEADING 'Pct.Used'
COLUMN pct_free FORMAT 999 HEADING 'Pct.FREE'
BREAK ON report
COMPUTE count OF type ON report
COMPUTE sum OF ts_size ON report
COMPUTE sum OF used ON report
SELECT
d.tablespace_name name
, d.status status
, d.contents type
-- , d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0)/1024/1024 ts_size
, NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 used
, NVL(f.bytes, 0)/1024/1024 free
, NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
,NVL(f.bytes/a.bytes*100,0) pct_free
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
, d.contents type
-- , d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0)/1024/1024 ts_size
, NVL(t.bytes, 0)/1024/1024 used
, NVL(a.bytes - NVL(t.bytes,0), 0)/1024/1024 free
, NVL(t.bytes / a.bytes * 100, 0) pct_used
,NVL((a.bytes - NVL(t.bytes,0))/a.bytes*100,0) pct_free
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'
ORDER BY
8 desc
/
二、cdb 层面查看所有pdb空间：
set line 500
col DB_NAME for a10
SELECT nvl(t.name, 'CDB$ROOT') as DB_NAME,D.TABLESPACE_NAME,(SPACE - NVL(FREE_SPACE, 0)) used,
FREE_SPACE,ROUND((SPACE - NVL(FREE_SPACE, 0)) / ((SPACE - NVL(FREE_SPACE, 0))+FREE_SPACE),2)*100 use_ratio,
TOTAL_SPACE max_space FROM (SELECT TABLESPACE_NAME,con_id,ROUND(SUM(BYTES) / (1024 * 1024)) SPACE,
ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /(1024 * 1024)) TOTAL_SPACE,SUM(BLOCKS) BLOCKS
FROM containers(DBA_DATA_FILES) GROUP BY TABLESPACE_NAME, con_id) D,(SELECT TABLESPACE_NAME,con_id,
ROUND(SUM(BYTES) / (1024 * 1024)) FREE_SPACE FROM containers(DBA_FREE_SPACE) GROUP BY TABLESPACE_NAME, con_id) F,
v$pdbs t WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.CON_ID = F.CON_ID and F.con_id = t.CON_ID(+)
ORDER BY 1
/
三、ASM空间使用率：
select name,free_mb,total_mb from v$asm_disk;
select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
select path,total_mb,free_mb from v$asm_disk_stat;
四、数据文件表空间
1，查看数据文件大小及路径
SQL> select name from v$datafile;
select * from dba_tablespaces;
set line 300
col FILE_NAME for a50
col TABLESPACE_NAME for a10
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024 from dba_data_files; 不显示容器数据库
2，创建数据文件表空间
create tablespace pdms datafile 'E:\app\tecpie\oradata\orcl1234\PDMS.DBF' size 400m autoextend on next 10m maxsize 5120m；
create tablespace his datafile 'D:\data\hisdata.dbf' size 200M autoextend on next 5m maxsize 500m；
创建表空间时指定表空间块大小：CREATE TABLESPACE test2 DATAFILE '+DATA' SIZE 50M BLOCKSIZE 16K;必须设置下db_16k_cache_size的大小。否则不能创建
3，改表空间名并指定数据库默认表空间
alter tablespace USERS rename to JIEK_TABLESPACE;
ALTER DATABASE DEFAULT TABLESPACE JIEK_TABLESPACE;
4，给表空间添加数据文件
alter tablespace CS_TABLESPACE add datafile '/database/HEBCS/CS_TABLESPACE05.dbf' size 4G autoextend on;
alter tablespace erpindx add datafile '+DBFILE' size 50G autoextend on maxsize 100G;
5，手动指定数据文件大小
alter database datafile 7 autoextend on maxsize 131071M
alter database datafile 7 resize 131000M
6,大文件表空间
create bigfile tablespace bigtab datafile '/u01/app/oracle/oradata/qycs/big01.dbf' size 500m autoextend on; -------------大文件表空间，32T
alter tablespace bigtab add datafile '/u01/app/oracle/oradata/qycs/big02.dbf' size 500m autoextend on;
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace --------------一个大文件表空间只能有一个数据文件
7,删除数据文件
SQL> select status,name from v$datafile;
SQL> alter database datafile 5 offline drop;
Oracle处于recover状态的数据文件是无法使用exp导出数据的
8,数据文件更换路径
SQL> startup mount
select name from v$datafile;
select 'alter database rename file '''||name||''' to '''||replace(name,'/u01/app/oracle/product/11.2.0/db_1/dbs/','/u01/app/dbfile/')||''' ;' from v$datafile;
select 'alter database rename file '''||member||''' to '''||replace(member,'/u01/app/oracle/product/11.2.0/db_1/dbs/','/u01/app/dbfile/')||''' ;' from v$logfile;
alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORPRIPRIREDO03.LOG' to '/u01/app/oracle/huobao3/REDO03.LOG' ;
alter pluggable database rename file 'E:\ORACLE\ORADATA\SKY\5D65300BC42A495BAA7F82A5FAD39B15\DATAFILE\O1_MF_SYSTEM_GGPZ71NY_.DBF' to 'E:\o
radata\SKY\PDBSKY\DATAFILE\SYSTEM01.DBF';
五、REDO日志文件组
SQL> select GROUP#, STATUS,TYPE,MEMBER from v$logfile;
SQL> select thread#,group#,bytes/1024/1024 from v$log;
SQL> alter system checkpoint;
--节点1操作：
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATA', 'FRA') SIZE 200M;
....
---节点2操作：
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ('+DATA', 'FRA') SIZE 200M;
....
每个节点分别操作：
alter system switch logfile; ---日志切换4次
alter system archive log current 归档当前的日志组，仅在归档模式下使用
alter system checkpoint ; --- 将active 的日志变为 inactive
---删除日志组：提示需要归档的话：
SQL> alter database clear unarchived logfile group 7;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
查看现有日志组信息
SELECT v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread# FROM v$log, v$logfile WHERE v$log.group# = v$logfile.group# group by v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024 /1024 ,v$log.thread# order by group# ;
使归档日志强制半小时切换一次
SQL> alter system set ARCHIVE_LAG_TARGET=2700 scope=both;
六、UNDO表空间
扩大UNDO表空间
alter database datafile '/opt/oracle/oradata/inms/undotbs02.dbf' resize 4000M;
1、创建新的UNDO表空间，并设置自动扩展参数；
create undo tablespace undotbs2 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size 2 1000m reuse autoextend on next 800m maxsize unlimited;
2、动态更改spfile配置文件；
alter system set undo_tablespace=undotbs2 scope=both;
3、删除原有的UNDO表空间；
drop tablespace undotbs1 including contents;
4、确认删除是否成功；
select name from v$tablespace;
七、TEMP表空间
1,查看临时文件的路径，大小，是否自动扩展
select * from dba_temp_files;
select * from v$tempfile; -------查看路径，状态。
select * from dba_tablespaces;
2，暂时释放临时表空间的磁盘占用空间
新建临时表空间temp2
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/huobao3/TEMP2.DBF' SIZE 200m AUTOEXTEND ON;
设置temp2为默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
删除原temp表空间-----看（4，查看temp使用率）里面的current_users是0才可以删除
drop tablespace temp including contents and datafiles;
3，给临时文件表空间添加数据文件
alter tablespace temp add tempfile '+DBFILE' size 32760m;
alter tablespace temp add tempfile '+DATA' size 30g;
手动指定数据文件大小
alter database tempfile 文件号 resize 6000m;
alter database tempfile '绝对路径' resize 20G; ----------adg指定绝对路径
4，查看temp使用率 ----看USED_BLOCKS是使用的
SQL> select inst_id, tablespace_name,current_users, total_blocks, used_blocks, free_blocks, free_blocks/ total_blocks as pct_free from gv$sort_segment;
INST_ID TABLESPACE CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
----------------------------------------------------------------------------------------------------------------------------------
1 TEMP 0 12559232 0 12559232
2 TEMP 2 195968 384 195584
2 rows selected.
八、删除表空间
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
