Oracle表領域の作成と監視(継続的な更新)
4926 ワード
システムが表領域の説明を持参する
SYSTEM # , , SYS 、 、 。
UNDOTBS1 # undo
SYSAUX #
TEMP # , SQL
USERS # ,
表領域を作成し、ユーザーに割り当てる
--
create tablespace SDA DATAFILE 'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' SIZE 10M;
--
alter database datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' autoextend on;
--
create user c##SDA identified by SDA default tablespace SDA;
--
grant dba to c##SDA;
grant connect to c##SDA;
grant resource to c##SDA;
--
--drop user c##SDA cascade;
--
--drop tablespace SDA including contents and datafiles;
表領域の使用状況の表示
select a.tablespace_name,
a.bytes / 1024 / 1024 "Sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc
--
SELECT UPPER(F.TABLESPACE_NAME) AS " ",
ROUND(D.AVAILB_BYTES, 2) AS " (G)",
ROUND(D.MAX_BYTES, 2) AS " (G)",
ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS " (G)",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
2),
'999.99') AS " ",
ROUND(F.USED_BYTES, 6) AS " (G)",
F.MAX_BYTES AS " (M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) /
(1024 * 1024 * 1024),
6) MAX_BYTES
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
-- sql
SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME,
ROUND(A.BYTES / (1024 * 1024 * 1024), 2) AS "TOTAL(G)",
ROUND(B.BYTES / (1024 * 1024 * 1024), 2) AS "USED(G)",
ROUND(C.BYTES / (1024 * 1024 * 1024), 2) AS "FREE(G)",
ROUND((B.BYTES * 100) / A.BYTES, 2) AS "% USED",
ROUND((C.BYTES * 100) / A.BYTES, 2) AS "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
表領域対応データファイルの表示
select file_name,
tablespace_name,
bytes / 1024 / 1024 "bytes MB",
maxbytes / 1024 / 1024 "maxbytes MB",
autoextensible,
increment_by
from dba_data_files
where tablespace_name = 'SDA';
クエリー表領域の作成に関するSQL
select dbms_metadata.get_ddl('TABLESPACE', 'SDA') from dual;
次のようなclobフィールドを返します.
CREATE TABLESPACE "SDA" DATAFILE
'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' SIZE 10485760
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' RESIZE 17571184640
ディスク容量が十分な場合にデータファイルを追加
--
alter tablespace SDA add
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF'
size 10M;
-- ,
alter tablespace SDA add
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF'
size 10M autoextend on next 5M maxsize 1G;
--
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF'
autoextend on next 5M maxsize 1G;
--
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF'
RESIZE 1G;
:ORACLE db_block_size db_block 。
db_block(ORACLE ) 2**22-1(4194303).
= *
増加が正しく確認されました
select file_name,
tablespace_name,
bytes / 1024 / 1024 "bytes MB",
maxbytes / 1024 / 1024 "maxbytes MB",
autoextensible,
increment_by
from dba_data_files
where tablespace_name = 'SDA';
表領域のデータファイルの削除
alter tablespace SDA drop datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF'