Oracle ORA-03297 -尝试收缩数据文件时

亲爱的团队：

我正在使用这个网站上提供的maxshlink脚本来尝试减小数据文件的大小。以下是特定数据文件的输出：

SQL> set verify off
SQL> column file_name format a50 word_wrapped
SQL> column smallest format 999,990 heading "Smallest|Size|Poss."
SQL> column currsize format 999,990 heading "Current|Size"
SQL> column savings  format 999,990 heading "Poss.|Savings"
SQL> break on report
SQL> compute sum of savings on report
SQL> column value new_val blksize
SQL> select value from v$parameter where name = 'db_block_size'
  2  /

VALUE
--------------------------------------------------------------------------------
8192

SQL> ed
Wrote file d:\afiedt.buf

  1  select file_name,
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
  3         ceil( blocks*&&blksize/1024/1024) currsize,
  4         ceil( blocks*&&blksize/1024/1024) -
  5         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  6  from dba_data_files a,
  7       ( select file_id, max(block_id+blocks-1) hwm
  8           from dba_extents
  9          group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11* and a.file_id = 38
SQL> /

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
              1   16,587   16,586

                                                                     --------
sum                                                                    16,586

SQL> column cmd format a75 word_wrapped
SQL> select 'alter database datafile ''' || file_name || ''' resize ' ||
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
  3  from dba_data_files a,
  4       ( select file_id, max(block_id+blocks-1) hwm
  5           from dba_extents
  6          group by file_id ) b
  7  where a.file_id = b.file_id(+)
  8    and ceil( blocks*&&blksize/1024/1024) -
  9        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
 10  and a.file_id = 38;

CMD
---------------------------------------------------------------------------
alter database datafile
'' resize 1m;


SQL> alter database datafile
  2  '' resize 1m;
alter database datafile
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> ed
Wrote file d:\afiedt.buf

  1  alter database datafile
  2* '' resize 1g
SQL> /
alter database datafile
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> SELECT sum(bytes)/1024/1024 from dba_free_space where file_id = 38;

SUM(BYTES)/1024/1024
--------------------
               16586

SQL> SELECT count(*) FROM dba_extents where file_id = 38;

  COUNT(*)
----------
         0

你能帮我解开这个谜吗？

致以问候，

穆罕默德里亚兹

当我试图压缩文件时，经常会有一些事情困扰我：

1)文件至少需要一小部分来保存某些范围的管理信息，因此不能将其缩小到低于该范围的大小。此外， （如果内存起作用） ，文件本身也可以在文件的结尾处具有此信息。

2)你那里有临时片段吗？

3)里面有回收站吗？


在12c中（可能是11.2.0.4? ） ，您可以删除一个空的数据文件，而不会影响表空间

SQL> create tablespace demo datafile 'C:\ORACLE\ORADATA\NP12\DEMO1.DBF' size 10m;

Tablespace created.

SQL> alter tablespace demo add datafile 'C:\ORACLE\ORADATA\NP12\DEMO2.DBF' size 10m;

Tablespace altered.

SQL> create table X ( y int ) tablespace demo;

Table created.

SQL> insert into x values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_extents
  2  where tablespace_name = 'DEMO';

OWNER
------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME
----------------------------------------
PARTITION_NAME
------------------------------------------------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME           EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------ ------------------------ ---------- ---------- ---------- ---------- ---------- ------------
MCDONAC
X

TABLE              DEMO                              0          8        128      65536          8         8


SQL> select * from dba_data_files
  2  where tablespace_name = 'DEMO';

FILE_NAME                                                 FILE_ID TABLESPACE_NAME               BYTES     BLOCKS
------------------------------------------------------ ---------- ------------------------ ---------- ----------
STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
--------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
C:\ORACLE\ORADATA\NP12\DEMO1.DBF                                8 DEMO                       10485760       1280
AVAILABLE            8 NO           0          0            0    9437184        1152 ONLINE

C:\ORACLE\ORADATA\NP12\DEMO2.DBF                                9 DEMO                       10485760       1280
AVAILABLE            9 NO           0          0            0    9437184        1152 ONLINE


SQL> alter tablespace demo drop datafile 9;

Tablespace altered.

SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces where tablespace_name = 'DEMO';

TABLESPACE_NAME          STATUS 
------------------------ ----------
DEMO                     ONLINE    


SQL>

希望这个能帮上忙。