oracle+调整+表空间,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;