alter tablespace



• CREATE DATABASE

• CREATE TABLESPACE ... DATAFILE

• ALTER TABLESPACE ... ADD DATAFILE

################################

ALTER DATABASE DATAFILE filespec [autoextend_clause]

autoextend_clause:== [ AUTOEXTEND { OFF|ON[NEXT integer[K|M]]

[MAXSIZE UNLIMITED | integer[K|M]]

#################################

CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf'

SIZE 500M EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE

(initial 1M NEXT 1M PCTINCREASE 0);

******************************************

ALTER TABLESPACE undotbs

ADD DATAFILE '/u01/oradata/undotbs2.dbf'

SIZE 30M

AUTOEXTEND ON;

******************************************

CREATE TABLESPACE userdata02

 DATAFILE '/u01/oradata/userdata02.dbf' SIZE 5M

 AUTOEXTEND ON NEXT 2M MAXSIZE 200M;

 By specifying AUTOEXTEND after tablespace creation

*******************************

SQL> ALTER DATABASE

2 DATAFILE '/u01/oradata/userdata02.dbf'

3 AUTOEXTEND ON NEXT 2M;

- Manually

******************************

SQL> ALTER DATABASE

2 DATAFILE '/u01/oradata/userdata02.dbf' RESIZE 5M;

*************************************

 CREATE TEMPORARY TABLESPACE "xxxxxx" TEMPFILE

 'xxxxxx.dbf' SIZE 838860800 REUSE

 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

RESIZE DATAFILE的时候会失败，因为一些OBJECT的EXTENTS已经扩展到DATAFILE的边缘（最大的地方）。
 下面的SQL可以让我们找到前5个最边缘的OBJECT
select *
 from (
select owner, segment_name,
      segment_type, block_id
 from dba_extents
 where file_id =
  ( select file_id
      from dba_data_files
     where file_name = :FILE )  --用你的DATAFILE代替
 order by block_id desc
      )
 where rownum <= 5

--这个在我的数据库里没有查出数据,先存放在这,以后会用到的

 

运行下面的这个脚本可以得到相应file做resize的命令 

SQL> variable blocksize number; 
SQL> begin execute immediate 'select value from v$parameter where name = ''db_block_size''' into :blocksize; end; 
2 / 
SQL>print :blocksize; 
SQL> select 'alter database datafile ''' || 
2 file_name || ''' resize ' || 
3 ceil( nvl(hwm,1)*:blocksize/1024/1024 ) || 'm;' cmd 
4 from dba_data_files a, 
5 ( select file_id, 
6 max(block_id+blocks-1) hwm 
7 from dba_extents 
8 group by file_id ) b 
9 where a.file_id = b.file_id(+) and b.file_id in (7, 8, 10);

 

下面的这个是个总的,和上面的这个脚本功能类似,都可以得到alter的cmd

 

SELECT
  a.file_id,
  a.file_name
  file_name,
  CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest,
  CEIL( blocks * blksize / 1024 / 1024 ) currsize,
  CEIL( blocks * blksize / 1024 / 1024 ) -
  CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings,
  'alter database datafile ''' || file_name || ''' resize ' ||
  CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
FROM
  DBA_DATA_FILES a,
  (
     SELECT  file_id, MAX( block_id + blocks - 1 ) hwm
     FROM    DBA_EXTENTS
     GROUP BY file_id
  ) b,
  (
     SELECT TO_NUMBER( value ) blksize
     FROM   V$PARAMETER
     WHERE  name = 'db_block_size'
  )
WHERE
  a.file_id = b.file_id(+)
AND
  CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0
ORDER BY 5 desc

 

 

alter完后 ,可以达到释放存储空间的止的,

还可以 exp/imp expdp/impdp(自我理解没有实践:可以导出整个数据库,也可以导出要清理表空间的数据,再DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; )

或者 一个表或者少量的表在那个表空间，可以通过creat table as select ...的方式或者move的方式，move完成后再干掉表空间，干掉表空间的时候，including contents and datefiles就可以了。