--查看表结构，有的分区表在view视图里面看不全建表语句
SELECT DBMS_METADATA.GET_DDL('TABLE','表名','用户') from dual;

--查看表分析信息
select owner, table_name, num_rows, last_analyzed
  from dba_tables
 where table_name IN ('CH_SETTLE_SUBS_CHARGEALL', 'CH_SETTLE_SUBS_IMEI_SIM', 'CH_SETTLE_SUBS_TOTALFEE')
 order by table_name;

--分析表
--exec dbms_stats.gather_table_stats(ownname=>’OWNER’,tabname=>’TABLE_NAME’);

xecute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,degree =>5,cascade => true);

这个degree =>5是下面的Number Of Processors，最好为一半，否则会影响

[CCBSDB1][/]#prtconf
System Model: IBM,9117-MMA
Machine Serial Number: 061A306
Processor Type: PowerPC_POWER6
Processor Implementation Mode: POWER 6
Processor Version: PV_6
Number Of Processors: 12
Processor Clock Speed: 4400 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 1 06-1A306
Memory Size: 94208 MB
Good Memory Size: 94208 MB
Platform Firmware level: EM350_071
Firmware Version: IBM,EM350_071
Console Login: enable
Auto Restart: true
Full Core: true

 --查询表是否被存过等使用，如果被使用，重命名会使存过失效，要重新编译
 select * from user_dependencies a where a.referenced_name='HIST_CUSTOM_VARIABLES';

--session blocked 检查
SELECT sid,ctime FROM v$lock WHERE BLOCK = 0 AND id2 IN (SELECT id2 FROM v$lock WHERE BLOCK = 1)

select c.owner,
       c.object_name,
       c.object_type,
       b.sid,
       b.serial#,
       b.status,
       b.osuser,
       b.machine
  from v$locked_object a, v$session b, dba_objects c
 where b.sid = a.session_id
   and a.object_id = c.object_id;

--表被锁掉了，查询出session并且kill

--检查哪些被锁
select * from v$locked_object c;
--查询被锁对象名
select * from dba_objects a where a.object_id=273583;
--查询session
select * from v$session b where b.SID=2346;-- sid=2346 serial#=6492
--杀掉session
alter system kill session '2346,6492' immediate;

--查询出被锁的表的信息 以上语句综合
select b.owner,b.object_id,b.object_type,b.object_name,c.SID,c.SERIAL#,a.ORACLE_USERNAME,c.LOGON_TIME,DECODE(a.LOCKED_MODE,0,'None',1,'Null',2,'Row share',3,'Row Execlusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type
  from v$locked_object a, dba_objects b, v$session c
 where b.object_id = a.OBJECT_ID
   and c.SID = a.SESSION_ID;
--杀掉session
--c.SID=68, c.SERIAL#=9384
alter system kill session '68,9384' immediate;

--查询在某个时间点，表里面还存在的数据
select *
  from topeng.dic_privilege as of timestamp(to_timestamp('15-08-10 13:23:27', 'DD-MM-YY HH24:MI:ss'));

--查看分区表
select *
  from all_tables t
 where t.OWNER in ('YY0A', 'SETTLE')
   and t.PARTITIONED = 'YES';

--2 turncate 分区
alter table inf_order_sale_resource truncate partition T_LIST_30;

--降水位
--blocks就是已经分配的空间即HWM，实际分配的空间，不是实际大小
SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='MONEY_TEST_1' AND OWNER='CCARE';
--删除数据
delete from MONEY_TEST_1 where rownum < 1000;
--降水位
alter table INTF_SMS_POOL enable row movement;
alter table INTF_SMS_POOL shrink space compact;
alter table INTF_SMS_POOL shrink space;
--重建索引
Alter index INDEX_INTF_SMS_POOL rebuild online;
--分析表
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'CCARE',TABNAME=>'MONEY_TEST_1');
--blocks就是已经分配的空间即HWM，实际分配的空间，不是实际大小
SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='MONEY