Dictionary views (VIEW$, COBJ$, I_COL1) are allocating a lot of unusable space due to excessive DDLs (Doc ID 756027.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 9.2.0.8 [Release 9.2]
Information in this document applies to any platform.
Information in this document applies to any platform.
***Checked for relevance on 29-DEC-2016***

Goal

* Due to continuously performing DDLs, dictionary views consume huge space than needed.
* Some Dictionary views (e.g: VIEW$, COBJ$, I_COL1) become really huge. Their sizes are very close
to the whole SYSTEM tablespace size.
* Doing "CREATE TABLE <> AS SELECT * FROM view$" results in very small table which points to
the fact that these dictionary views allocate huge space but their actual contents are really small.
* How can we re-organize these objects?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms