CODEWITHSUNDEEP
oracle-databaseoracle-xe
solarenqu
solarenqu

Reputation: 814

Oracle XE SYSAUX space

i have a Oracle XE database and the SYSAUX.DBF file is 14GB.

I run this script: 

select occupant_desc, space_usage_kbytes
  from v$sysaux_occupants
  where space_usage_kbytes > 0 order by space_usage_kbytes desc;

And this is the result:

XDB 385920
Server Manageability - Automatic Workload Repository    92608
Server Manageability - Optimizer Statistics History 68352
Oracle Spatial  48896
Unified Job Scheduler   29888
LogMiner    12544
Server Manageability - Advisor Framework    7808
Server Manageability - Other Components 6720
Oracle Text 6272
Transaction Layer - SCN to TIME mapping 3328
SQL Management Base Schema  1728
PL/SQL Identifier Collection    1664
OLAP API History Tables 1536
Analytical Workspace Object Table   1536
Logical Standby 1408
Oracle Streams  1024
Automated Maintenance Tasks 320

So, it's only 655MB.

When i try to run this:

alter database datafile 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF' resize 1000m;

I got this error:

SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.

Can anybody please help me how could i reduce my SYSAUX.DBF file?

Upvotes: 2

Views: 1201

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

The used space is not a contiguous set of blocks at the start of the file, but is instead scattered throughout it.

This is the meaning of "Some portion of the file in the region to be trimmed is currently in use by a database object"

The solution is also given: "Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed."

So you will have to either drop segments, which pretty much means dropping tables and/or indexes, or move them. That's not necessarily an easy task, so I would first wonder why you want to do this at all?

Upvotes: 1

Related Questions