alter tablespace

来源：互联网发布：万科股权之争始末知乎编辑：程序博客网时间：2024/05/02 04:55

• 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就可以了。

0 0