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

来源：这里教程网时间：2026-03-03 17:01:02 作者：

### 表管理/段管理
#### 表信息查询
``` 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 '/home/oracle/script/table_shrink20171124.log';
set time on
alter table /*+APPEND*/ RM.VIO_JDCZP_TEMP enable row movement nologging parallel 4;
alter table RM.VIO_JDCZP_TEMP shrink space compact;
alter table RM.VIO_JDCZP_TEMP shrink space cascade;
alter table RM.VIO_JDCZP_TEMP noparallel logging;
set time off
spool off
exit
EOF

--Shrink a BASICFILE LOB segment only:
ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);
--Shrink a single partition of a partitioned table:
ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;
--Shrink an IOT index segment and the overflow segment:
ALTER TABLE cities SHRINK SPACE CASCADE;
--Shrink an IOT overflow segment only:
ALTER TABLE cities OVERFLOW SHRINK SPACE;

--move table
alter table t1 nologging parallel 2;
alter table t1 move tablespace test lob(name) store as t1lob(tablespace t1);
alter index t1_pk rebuild nologging parallel 2;
alter table t1 logging noparallel;
```
> 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'));
```