### 表管理/段管理

#### 表資訊查詢
``` sql
--檢視錶的資訊
set lines 150
select a.table_name,a.owner,a.tablespace_name,status,b.bytes/1024/1024 
from dba_tables a,dba_segments b
where a.table_name=b.segment_name
and a.table_name='XYSOUL';

--檢視錶對應的索引資訊
set lines 150
select a.index_name,a.owner,a.table_name,a.table_type,b.bytes/1024/1024
from dba_indexes a,dba_segments b
where a.index_name=b.segment_name and a.table_name='';

--檢視錶是否有主鍵
select CONSTRAINT_NAME,owner,table_name 
from dba_constraints
where CONSTRAINT_TYPE='P' and table_name='ZFI_GDZC_SJ';

```

#### 表實際大小
``` sql
--檢視錶實際大小
select table_name,round((blocks*8)/1024/1024,2) "size (gb)" , 
round((num_rows*avg_row_len/1024/1024/1024),2) "actual_data (gb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2))/1024/1024 "wasted_space (gb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) 
and table_name='T1' and owner='TT'
order by 4 desc;
```

#### 段顧問建議
``` sql
 SELECT
 'Segment Advice --------------------------'|| chr(10) ||
 'TABLESPACE_NAME  : ' || tablespace_name   || chr(10) ||
 'SEGMENT_OWNER    : ' || segment_owner     || chr(10) ||
 'SEGMENT_NAME     : ' || segment_name      || chr(10) ||
 'ALLOCATED_SPACE  : ' || allocated_space   || chr(10) ||
 'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
 'RECOMMENDATIONS  : ' || recommendations   || chr(10) ||
 'SOLUTION 1       : ' || c1                || chr(10) ||
 'SOLUTION 2       : ' || c2                || chr(10) ||
 'SOLUTION 3       : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
```
說明：
- all_runs：為true則儲存過程返回歷次執行的結果，而為false則僅返回最近一次執行的結果。 
- show_manual：為true則儲存過程返回手工執行段顧問的結果，為false則儲存過程返回自動執行段顧問的結果。 
- show_findings：僅顯示分析結果而不顯示建議。

其他語句
``` sql
--檢視段顧問執行情況
set lines 200 pages 999
col start_time for a25
col end_time for a25
select segments_processed,to_char(start_time,'yyyy/mm/dd hh24:mi:ss') start_time,
to_char(end_time,'yyyy/mm/dd hh24:mi:ss') end_time from dba_auto_segadv_summary
order by start_time;
--檢視顧問建議（另一種語句）
select
 'Task Name        : ' || f.task_name  || chr(10) ||
 'Start Run Time   : ' || TO_CHAR(execution_start, 'yyyy/mm/dd hh24:mi') || chr (10) ||
 'Segment Name     : ' || o.attr2    || chr(10) ||
 'Segment Type     : ' || o.type       || chr(10) ||
 'Partition Name   : ' || o.attr3      || chr(10) ||
 'Message          : ' || f.message    || chr(10) ||
 'More Info        : ' || f.more_info  || chr(10) ||
 '------------------------------------------------------' Advice
FROM dba_advisor_findings   f
    ,dba_advisor_objects    o
    ,dba_advisor_executions e
WHERE o.task_id   = f.task_id
AND   o.object_id = f.object_id
AND   f.task_id   = e.task_id
AND   e. execution_start > sysdate - 1
AND   e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;
```

參考：
- SEGMENT SHRINK and Details. (Doc ID 242090.1)
- Automatic Segment Advisor in Oracle 10g Release 2 (10.2) (Doc ID 314112.1)

#### 表收縮
``` sql
--Shrink a table and all of its dependent segments (including BASICFILE LOB segments):
alter table t1 shrink space compact;
alter table t1 enable  row movement nologging parallel 2;

sqlplus / as sysdba <<eof spool="" set="" time="" on="" alter="" table="" rm.vio_jdczp_temp="" enable="" row="" movement="" nologging="" parallel="" shrink="" space="" compact="" cascade="" noparallel="" logging="" off="" exit="" eof="" a="" basicfile="" lob="" segment="" only:="" employees="" modify="" single="" partition="" of="" partitioned="" table:="" customers="" cust_p1="" an="" iot="" index="" and="" the="" overflow="" segment:="" cities="" t1="" move="" tablespace="" test="" store="" as="" t1lob="" t1_pk="" rebuild=""> cascade：是指壓縮所有依賴的物件，比如壓縮表語句加上cascade，表上所有的索引都會被壓縮
compact：把壓縮過程分為兩個階段：第一個階段的語句帶compact，壓縮段空間，在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。由於涉及到rowid的改變，需要enable row movement.同時要disable基於rowid的trigger.這一過程對業務影響比較小。；第二個階段語句不帶compact，調整高水位並釋放收回的空間。此過程需要在表上加X鎖，會造成表上的所有DML語句阻塞。在業務特別繁忙的系統上可能造成比較大的影響。對於大表，建議採用compact選項

#### 表新增列
``` sql
--oracle10g
alter table test add (age int null);

--oracle11g  not null 提高速度
alter table test add (age int default '' not null);

--oracle12c
alter table test add (age int default '' null);
```

#### 段大小排序
``` sql
-- >10g for segment_name
col owner format a15
col Segment_Name format a40
col segment_type format a15
col tablespace_name format a15
select owner, Segment_Name,segment_type,
trunc(Sum(bytes)/1024/1024/1024,2) as size_GB 
From dba_segments where tablespace_name='USERS' 
group by owner,Segment_Name,segment_type
having trunc(Sum(bytes)/1024/1024/1024,2)>10
order by size_GB desc;
```
#### 檢視LOB表大小
``` sql
--vi lob_table_size.sql

ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name:  '
SELECT
 (SELECT SUM(S.BYTES/1024/1024/1024)                                                                                                 -- The table segment size
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT SUM(S.BYTES/1024/1024/1024)                                                                                                 -- The Lob Segment Size
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT SUM(S.BYTES/1024/1024/1024)                                                                                                 -- The Lob Index size
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE"
FROM DUAL;
```
#### 最後建立的物件
```sql 
--last create object
alter session set nls_date_format='yyyy/mm/dd hh24:mi;ss';
col object_name for a30
with obj_50 as (
select owner,object_name,object_type,created 
from dba_objects 
where created>=sysdate-2 and owner not in ('SYS') order by created desc
)
select o.*,s.bytes/1024/1024 "mb" 
from obj_50 o,dba_segments s 
where o.object_name=s.segment_name 
order by o.created desc;
```
#### 表空間佔用情況
```sql
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('<owner>', '<lob segment="" name="">', 'LOB', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
```

#### 行遷移
```sql
--取行遷移超過3%的表
select table_name,num_rows,CHAIN_CNT,ROUND((CHAIN_CNT/num_rows)*100,2) as "RT%" 
from (select * from dba_tables where num_rows>0 and CHAIN_CNT>0)
where (CHAIN_CNT/num_rows)*100>3
order by CHAIN_CNT/num_rows desc;

--檢視某錶行遷移建議
select
'Task name    :'|| f.task_name ||chr(10) ||
'Segment name :'|| o.attr2     ||chr(10) ||
'Segment type :'|| o.type       ||chr(10) ||
'Partition name :'|| o.attr3    ||chr(10) ||
'Message   :' || f.message  ||chr(10)||
'More Info  :'|| f.more_info  task_advice
from dba_advisor_findings f,dba_advisor_objects o where o.task_id=f.task_id
and o.object_id=f.object_id
and f.task_name like '&task_name'
order by f.task_name;
```

#### 序列
``` sql
--建立序列  Student_stuId_Seq --
 create sequence Student_stuId_Seq
 increment by 1
 start with 1
 minvalue 1
 maxvalue 999999999;
 
--更改序列  Student_stuId_Seq--
 alter sequence Student_stuId_Seq
    increment by 2  
    minvalue 1
    maxvalue 999999999;
 
--獲取序列自增ID --
 select Student_stuId_Seq.Nextval 自增序列ID from dual;
 
-- 刪除序列 -- 
 drop sequence Student_stuId_Seq;
 
--呼叫序列，插入Student資料 --
 insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,'張三');
 insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,'李四');
 --查詢插入的資料 --
 select * from Student
 
--設定自動增長 觸發器
create or replace trigger TABLE_NAME_trigger 
before insert on T22
for each row
begin
select Student_stuId_Seq.nextval into:NEW.ID from dual;
end;
```

#### 臨時表
``` sql
--基於會話的臨時表  退出會話 清空表   不同會話獨立
create global temporary table t_tmp_session on commit preserve rows as select * from dba_objects where 1=2;

--基於事務的臨時表  COMMIT清空表
create global temporary table t_tmp_transaction on commit delete rows as select * from dba_objects where 1=2;

select OWNER,table_name,temporary,duration from dba_tables where table_name in ('T_TMP_SESSION','T_TMP_TRANSACTION');

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col object_name for a30
set lines 150
select owner,object_name,created,status from dba_objects where object_name in ('T_TMP_SESSION','T_TMP_TRANSACTION');

exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'T_TMP_SESSION',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);

select * from dba_tab_histograms where table_name='T_TMP_SESSION' order by 3;
select table_name,column_name,num_distinct,low_value,high_value,DENSITY from dba_tab_col_statistics where table_name='T_TMP_SESSION';

set lines 150
select owner,table_name,last_analyzed,num_rows from dba_tab_statistics where table_name='T_TMP_SESSION';

exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'T_TMP_SESSION',method_opt=> 'for all columns size 1');
exec dbms_stats.delete_column_stats(user,'T_TMP_SESSION','OWNER');

alter session set statistics_level=all;

select count(*) from T_TMP_SESSION,t1 where T_TMP_SESSION.object_id=t1.object_id;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
```</lob></owner></eof>

【SCRIPT】Oracle表管理段管理常用語句

相關文章