some sql

SQL> create table tmp_test (id number,phone varchar2(20),create_time date)
      compress;

SQL> alter table tmp_test compress;
SQL> alter table tmp_test nocompress;

SQL> alter tablespace sms default compress;
SQL> alter tablespace sms default nocompress;

SQL> alter table tmp_test move compress;
SQL> alter table tmp_test move nocompress;

SQL> create materialized view mv_tmp_test
     compress
     as
     select a.phone,b.create_time from tmp_test a,recv_stat b
     where a.id=b.id;

SQL> alter materialized view mv_temp_test compress;
SQL> alter table tmp_test move partition create_200606 compress;

PCTTHRESHOLD 50 OVERFLOW

SQL>SELECT TABLE_NAME, PARTITION_NAME,COMPRESSION FROM USER_TAB_PARTITIONS;

 
alter table tb_name move tablespace tbs_name;
alter table tb_name move tablespace tbs_name  
lob(col_lob1,col_lob2)   store   as(tablesapce   tbs_name);

 

=============================================
1.
REVOKE   UNLIMITED   TABLESPACE   ON   TABLESPACE_A   FROM   USER_A
ALTER   USER   USER_A   QUOTA   0   ON   TABLESPACE_A
ALTER   USER   USER_A   DEFAULT   TABLESPACE   TABLESPACE_B
ALTER   USER   USER_A   QUOTA   UNLIMITED   ON   TABLESPACE_B;  

 

Only below 4 ways can succeed to load compress data:

1> Use sql*load
2> Use /*+ append*/ insert
3> create table .. as select..
4> parallel insert  INSERT/*+ PARALLEL*/

Note:

1> query in compressed table will consume less time than nocompressed table;
2> Insert action will consume more time than do in nocompressed table;

so,
1> For metedata, it is good approach to use compress option.
2> For transaction data, it is bad practise to use compress option.

alter session enable parallel dml

Analyze table tablename compute statistics;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
Analyze index|cluster indexname estimate statistics;
ALTER TABLESPACE TABLESPACE_NAME COALESCE

dbms_space.space_usage
dbms_space.free_blocks
dbms_space.unused_space

SELECT 
substr(COLUMN_VALUE, 0,instr(COLUMN_VALUE,':',1,1)-1),
substr(COLUMN_VALUE,instr(COLUMN_VALUE,':',1,1)+1, instr(COLUMN_VALUE,':',1,2)-instr(COLUMN_VALUE,':',1,1)-1),
substr(COLUMN_VALUE,instr(COLUMN_VALUE,':',1,2)+1, (length(COLUMN_VALUE)-instr(COLUMN_VALUE,':',1,2))  )
FROM TABLE(sys.odcivarchar2list
('123444:99075:38383',
  '3393993:303030:3030',
  'abckd:hello:expr')
)

 

 

select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_data_files group by tablespace_name
union ALL
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_temp_files group by tablespace_name order by GB;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/781883/viewspace-664859/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/781883/viewspace-664859/