Re: import sys objects

From: <fitzjarrell_at_cox.net>
Date: Thu, 27 Mar 2008 13:11:01 -0700 (PDT)
Message-ID: <dea104a3-2fd4-472e-b549-0cbce829f37c@d21g2000prf.googlegroups.com>

On Mar 27, 2:52 pm, "jerni..._at_nospam.kochind.com" <jerni..._at_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 Received on Thu Mar 27 2008 - 15:11:01 CDT