cascade：是指压缩所有依赖的对象，比如压缩表语句加上cascade，表上所有的索引都会被压缩
compact：把压缩过程分为两个阶段：第一个阶段的语句带compact，压缩段空间，在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。；第二个阶段语句不带compact，调整高水位并释放收回的空间。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。对于大表，建议采用compact选项

表添加列

--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);

段大小排序

-- >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表大小

--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;

最后创建的对象

--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;

表空间占用情况

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 ('', '', '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;
/

行迁移

--取行迁移超过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;

序列

--创建序列  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;

临时表

--基于会话的临时表  退出会话 清空表   不同会话独立
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'));