【Oracle19C】创建&设置表空间

1.使用OMF创建表空间

//1.登录pdb
//需要...账号权限
 sqlplus pdb3_admin/oracle4U@19c01:1621/orclpdb3
//或者先登录cdb后set session
sqlplus / as sysdba
alter session set container=orclpdb3;

//2.查看表空间
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
TPCCTAB

//3.查看OMF配置db_create_file_dest;
SQL> show parameter db_create_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/orclpd
                                                 b3
//4.创建表空间
SQL> create tablespace INVENTORY;
Tablespace created.

//5.查看文件位置

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/
o1_mf_system_k3ng86b1_.dbf

/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/
o1_mf_sysaux_k3ng86bb_.dbf

/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/
o1_mf_undotbs1_k3ng86bc_.dbf

/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/
o1_mf_tpcctab_k4gy27yq_.dbf

NAME
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/
o1_mf_inventor_k4ykocb9_.dbf

• 查看默认表空间

SQL> select property_name ,property_value from database_properties where property_name like 'DEFAULT_%TABLE%';

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE
SYSTEM

DEFAULT_TEMP_TABLESPACE
TEMP

• 设置默认表空间

alter database default tablespace ....;

//默认临时表空间
alter database default temporary tablespace ....

• 删除表空间

drop tablespace TPCHTAB;
//若表空间有数据，使用如下指令删除
drop tablespace TPCHTAB including contents and datafiles;

2.查看及设置表空间大小

//1.查看表空间大小
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; 

TABLESPACE_NAME                   TS_SIZE
------------------------------ ----------
SYSTEM                                270
SYSAUX                                350
UNDOTBS1                              150
TPCCTAB                               100
INVENTORY                             100

//2.查看表空间使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;

//2-1.查看单个表空间使用情况
select df.tablespace_name tablespace, fs.bytes free,
df.bytes , fs.bytes *100/ df.bytes pct_free
from dba_data_files df ,dba_free_space fs
where df.tablespace_name = fs.tablespace_name
and df.tablespace_name = 'CDATA';

//2-2.查看表空间使用情况(注：若表空间未使用或者占满，sys.sm$ts_used、sys.sm$ts_free可能为空)
SELECT a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes * 100) / a.bytes "% USED ",(c.bytes * 100) / a.bytes "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b,sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_nameAND a.tablespace_name = c.tablespace_name;

TABLESPACE_NAME                     TOTAL       USED       FREE    % USED
------------------------------ ---------- ---------- ---------- ----------
   % FREE
----------
SYSTEM                          283115520  281804800     262144  99.537037
.092592593

SYSAUX                          367001600  348454912   17498112 94.9464286
4.76785714

UNDOTBS1                        157286400          0    2228224          0
1.41666667


TABLESPACE_NAME                     TOTAL       USED       FREE    % USED
------------------------------ ---------- ---------- ---------- ----------
   % FREE
----------
TPCCTAB                         104857600   18546688   85262336    17.6875



//3.查看表空间是否可扩展
SQL> 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;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
AUT      BYTES   MAXBYTES STATUS
--- ---------- ---------- ---------
INVENTORY
/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/
o1_mf_inventor_k4ykocb9_.dbf
YES  104857600 3.4360E+10 AVAILABLE

SYSAUX
/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/

//设置表空间大小
alter database datafile '/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/o1_mf_inventor_k4ykocb9_.dbf' resize 64M;

//设置表空间是否可扩展s
alter database datafile '/u01/app/oracle/oradata/orclpdb3/ORCL/DAD5E8983D36442CE053BF38A8C0BB73/datafile/o1_mf_inventor_k4ykocb9_.dbf' autoextend off;

设置行展示长度

set line 128
set serveroutput on

3.查看表空间下的表

select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='CDATA';

4.数据库告警阈值

• 数据库告警阈值设定查看

select warning_value, critical_value from dba_thresholds 
where metrics_name='Tablespace Space Usage' and object_name is 
NULL;

• 数据库当前告警和可用的处理方法

select reason,SUGGESTED_ACTION from dba_outstanding_alerts where object_name='CDATA';

5.数据压缩

//1.创建索引
CREATE INDEX "ADVISOR_TEST_INDEX1" ON 
"ADVISOR_TEST" ("OWNER") 
 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE 
STATISTICS 
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT 
CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "CDATA" ;
 
 //2.查看索引压缩状态
  select index_name, compression from user_indexes 
where index_name = 'ADVISOR_TEST_INDEX1';

//3.查询索引使用空间
select blocks from user_segments where 
segment_name='ADVISOR_TEST_INDEX1';

//4.查询表使用空间
select blocks from user_segments where 
segment_name='ADVISOR_TEST';

• 执行压缩

alter index ADVISOR_TEST_INDEX1 rebuild compress advanced high;