1 Reply Latest reply: Jun 29, 2011 8:20 AM by orafad RSS

    file mismatch

    Alain
      I had a linux 10g with 10g xe installed.
      I stopped db, removed all db files and xe stuff, and installed 11g xe.
      Then I created 2 extra tablespaces and imp(orted) a datafile with 10.2.0.3.0 oracle user's tables (from node 1 of a clustered enterprise edition database).

      Here is what I get, very strange:
      select ts.name, da.name, da.bytes, ta.status
      from dba_tablespaces ta, v$tablespace ts,
      v$datafile da
      where da.ts#=ts.ts#
      and ts.name=ta.TABLESPACE_NAME;
      NAME
      ------------------------------
      NAME
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      BYTES STATUS
      ---------- ---------
      SYSTEM
      /u01/app/oracle/oradata/XE/system.dbf
      377487360 ONLINE

      SYSAUX
      /u01/app/oracle/oradata/XE/undotbs1.dbf
      555745280 ONLINE

      UNDOTBS1
      */u01/app/oracle/oradata/XE/sysaux.dbf
      5620367360 ONLINE*

      USERS
      /u01/app/oracle/oradata/XE/users.dbf
      5242880 ONLINE

      UNDOTBS2
      /u01/app/oracle/oradata/XE/XE/datafile/o1_mf_undotbs2_6zyjnnxw_.dbf
      104857600 ONLINE

      INDX
      /u01/app/oracle/oradata/XE/XE/datafile/o1_mf_indx_70oxvj27_.dbf
      1048576000 ONLINE

      T_JOBINS
      /u01/app/oracle/oradata/XE/XE/datafile/o1_mf_t_jobins_6zyjqlbw_.dbf
      10485760 ONLINE

      USR
      /u01/app/oracle/oradata/XE/XE/datafile/o1_mf_usr_6zykwso9_.dbf
      104857600 ONLINE

      ABIEC
      /u01/app/oracle/oradata/XE/XE/datafile/o1_mf_abiec_70oxw6rw_.dbf
      2097152000 ONLINE


      9 rows selected.

      SQL>
      ---
      Problem 1:
      undotbs1 is undo tablespace of 10.2.0.3.0 db.
      Sysaux tablespace now works with undotbs1.dbf datafile, and undotbs1 undo tablespace now uses sysaux.dbf.

      why why why? (This would have been difficult to do this using sql :) ).

      Problem 2: sysaux.dbf datafile size grew to 5 Gb, I will have to look how to shrink it.