Oracle表空间调整

1.创建表空间
create temporary tablespace jydhtds_temp
tempfile 'G:\oradata\jydhtds_temp01.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;



create tablespace jydhtds_data
logging
datafile 'G:\oradata\jydhtds_data01.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;


2. 修改表空间
    --表 
    select 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE '||tablespace_name||' STORAGE(INITIAL 64K NEXT 32K);'  
      from dba_tables  
     where owner='AA' and initial_extent>65536   
     
      
    --索引 
    select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD STORAGE(INITIAL 64K NEXT 32K);'  
      from dba_indexes  
     where owner='AA'  and initial_extent>65536 
     
      
    --分区表 
    select 'ALTER table '||table_owner||'.'||table_name||' MOVE PARTITION '||PARTITION_NAME||' STORAGE(INITIAL 64K NEXT 32K);'    
      from DBA_tab_PARTITIONS  
     where table_owner='AA' and initial_extent>65536 
     
     
    --分区索引 
    select 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||PARTITION_NAME||' STORAGE(INITIAL 64K NEXT 32K);'    
      from DBA_ind_PARTITIONS  
     where index_owner='AA' and initial_extent>65536 

3.压缩表空间文件大小
select file#, name from v$datafile;


select max(block_id) from dba_extents where file_id=2

select 114248*8/1024 from dual
31209

alter database datafile 'D:\ORADATA\ORCL\SYSAUX01.DBF' resize 893m;