Is it possible to export and then import specific sys objects without
doing a full import?

Basically my aud$ table got really large and made my system tablespace
6GB. I moved the aud$ table. I can't shrink it, because of some
blocks in the idl_ub1$ table.

I am running 10.2.0.3 on Solaris.

On Mar 27, 2:52*pm, "jerni...@nospam.kochind.com"
<jerni...@kochind.com> wrote:
> Is it possible to export and then import specific sys objects without
> doing a full import?
>
> Basically my aud$ table got really large and made my system tablespace
> 6GB. * I moved the aud$ table. * I can't shrink it, because of some
> blocks in the idl_ub1$ table.
>
> I am running 10.2.0.3 on Solaris.

Database-specific tables owned by SYS or SYSTEM never get exported
with a full export. The list of SYS objects exported with a full
export is:

DEF$_AQCALL
DEF$_AQERROR
DEF$_CALLDEST
DEF$_DEFAULTDEST
DEF$_DESTINATION
DEF$_ERROR
DEF$_LOB
DEF$_ORIGIN
DEF$_PROPAGATOR
DEF$_PUSHED_TRANSACTIONS
DEF$_TEMP$LOB
PLAN_TABLE
REPCAT$_AUDIT_ATTRIBUTE
REPCAT$_AUDIT_COLUMN
REPCAT$_COLUMN_GROUP
REPCAT$_CONFLICT
REPCAT$_DDL
REPCAT$_FLAVORS
REPCAT$_FLAVOR_OBJECTS
REPCAT$_GENERATED
REPCAT$_GROUPED_COLUMN
REPCAT$_KEY_COLUMNS
REPCAT$_OBJECT_PARMS
REPCAT$_PARAMETER_COLUMN
REPCAT$_PRIORITY
REPCAT$_PRIORITY_GROUP
REPCAT$_REFRESH_TEMPLATES
REPCAT$_REPCAT
REPCAT$_REPCATLOG
REPCAT$_REPCOLUMN
REPCAT$_REPGROUP_PRIVS
REPCAT$_REPOBJECT
REPCAT$_REPPROP
REPCAT$_REPSCHEMA
REPCAT$_RESOLUTION
REPCAT$_RESOLUTION_METHOD
REPCAT$_RESOLUTION_STATISTICS
REPCAT$_RESOL_STATS_CONTROL
REPCAT$_RUNTIME_PARMS
REPCAT$_SNAPGROUP
REPCAT$_TEMPLATE_OBJECTS
REPCAT$_TEMPLATE_PARMS
REPCAT$_TEMPLATE_SITES
REPCAT$_USER_AUTHORIZATIONS
REPCAT$_USER_PARM_VALUES
SQLPLUS_PRODUCT_PROFILE

Which doesn't mean you can't specify which SYS-owned table or tables
you want exported:

exp file=aud.dmp tables=aud$ log=aud.log ....

which resulted in

Connected to: Oracle Database 10g Enterprise Edition Release
10.2.0.3.0 - 64bit Production
With the Data Mining option
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table AUD$ 0 rows
exported
Export terminated successfully without warnings.

Granted, I don't have auditing running on that database, but the table
was exportable when specified in the tables list.


David Fitzjarrell

On Mar 27, 12:52*pm, "jerni...@nospam.kochind.com"
<jerni...@kochind.com> wrote:
> Is it possible to export and then import specific sys objects without
> doing a full import?
>
> Basically my aud$ table got really large and made my system tablespace
> 6GB. * I moved the aud$ table. * I can't shrink it, because of some
> blocks in the idl_ub1$ table.
>
> I am running 10.2.0.3 on Solaris.

In addition to what David said, see metalink Note:166301.1. It
appears to be an old note, so I don't know how much really applies to
O10, but I'm thinking the bit about killing off auditable sessions may
be relevant, and maybe the shrinking part too.

Aren't you using sysaux? Won't moving it there help?

Apologies if anything I say doesn't make sense, I'm new to 10g, though
I've been watching the discussions about it for a long time. I just
got excited a few minutes ago about the segment advisor for one of my
issues.

jg
--
@home.com is bogus.
http://financial.washingtonpost.com/...=ORCL&nav= el

I have already moved the aud$ table.
To shrink the tablespace I would need to rebuild the idl_ub1$
table.

I am using sysaux, but I can't move the idl_ub1$ table because it has
a long column in it.

On Mar 27, 5:40*pm, "jerni...@nospam.kochind.com"
<jerni...@kochind.com> wrote:
> I have already moved the aud$ table.
> To shrink the tablespace I would need to rebuild the idl_ub1$
> table.
>
> I am using sysaux, but I can't move the idl_ub1$ table because it has
> a long column in it.

Some suggestions and reminders:

1 - Never use extendable data files without setting a maximum size
limit
2 - if you had a maximum set on your system tablespace then there is
no reason for you to be trying to shrink it since it is still below
that limit otherwise someone made a bad decision when the limit was
chosen. No limit on datafiles almost always leads to trouble like you
now have.
3 - did you just alter table sys.aud$ move or did you move it to
another tablespace? Oracle has published a note on how to move the
table but Oracle is also on record as stating that this is an
unsupported configuration and with an upgrade there is no guarantee
that you will not experience problems because sys.aud$ is not in the
system tablespace.
4 - did you remember to rebuild the index?
5 - set up a purge task for sys.aud$

I would live with the 6G system tablespace till it is time to migrate
to a new platform or perform a major upgrade. Otherwise you are
likely to damage your system by attempting to move and reorganize sys
owned objects. Definitely avoid touching uet$ and fet$.

HTH -- Mark D Powell --

Well if I can't shrink the system tablespace anymore, I might as well
keep the aud$ table in there. I was regularly purging it, but we had
a bad process login over and over again which filled it.

The SYSTEM tablespace is the only one that didn't have a limit on it.
I will set one.

What would happen if it did happend to fill up and couldn't extend?
Would it prevent users from connecting because the audit data couldn't
be written?

On Fri, 28 Mar 2008 06:15:51 -0700 (PDT),
"jernigam@nospam.kochind.com" <jernigam@kochind.com> wrote:

>Well if I can't shrink the system tablespace anymore, I might as well
>keep the aud$ table in there. I was regularly purging it, but we had
>a bad process login over and over again which filled it.
>
>The SYSTEM tablespace is the only one that didn't have a limit on it.
>I will set one.
>
>What would happen if it did happend to fill up and couldn't extend?
>Would it prevent users from connecting because the audit data couldn't
>be written?

You got it.

--
Sybrand Bakker
Senior Oracle DBA