#31 Schema extraction, list partitioning

open
nobody
None
5
2001-10-10
2001-10-08
Martin
No

Schema extraction, list partitioning
TOra 1.1.2
Oracle 9.0.1

The new Oracle 9i Feature "list partitioning" seems not
to be supported

Error in toExtract
Operation: Create
Object: TABLE FAMILY:DR.LIST_PARTITION_TABLE
Error: Not all input variables have been
initialized
SELECT subpartition_name,
tablespace_name
FROM all_tab_subpartitions
WHERE table_name = :nam
AND partition_name = :prt
AND table_owner = :own
ORDER BY subpartition_name

You can find the list values in
all_tab_partitions.high_value and the type of
partitioning in all_part_tables.partitioning_type
('LIST', 'RANGE', 'HASH','SYSTEM')

CREATE TABLE list_partition_table
( customer_id NUMBER(6)
, country VARCHAR2(2)
)
PARTITION BY LIST (country) (
PARTITION german VALUES ( 'de' , 'ch', 'at' , 'li'),
PARTITION french VALUES ( 'fr' , 'mc' , 'be'),
PARTITION english VALUES ( 'gb', 'ie' )
);

Discussion

  • Henrik Johnson
    Henrik Johnson
    2001-10-09

    • status: open --> pending
     
  • Henrik Johnson
    Henrik Johnson
    2001-10-09

    Logged In: YES
    user_id=2931

    No Oracle9 specific features are supported. It probably will
    not be until 2.0 (That is not the 1.2 release). I've tried
    installing Oracle9 but the installation program at up 200MB
    of ram and then died on me so I didn't finnish it. I will
    make another attempt at it sometime in the future. It is on
    the TODO list and will be fixed, only not just now.

    /Mauritz
    GlobeCom AB

     
  • Martin
    Martin
    2001-10-10

    • status: pending --> open
     
  • Martin
    Martin
    2001-10-10

    Logged In: YES
    user_id=327932

    Hi Henrik

    I know, installing Oracle 9i is *very* resource hungry
    (Java...). For installing the linux-version, the
    requirements are 512M RAM and the same amount of swap-space
    (see doc/releasenote.pdf on CD1).
    During the installation, 512M RAM and approx. 300M swap were
    occupied. I don't know, but I think it should also work when
    the total of RAM plus swap is 900M.

    Migrating TOra to 9i seems not to be a big task. I used TOra
    with 9i and most things will work without any change
    (exactly: the case above is the first and only one I have
    found)
    I downloaded your statically linked binary and it works, but
    only when you set $ORACLE_HOME to a version of 8i. With a
    path to 9i you will get "unable to connect to the database".
    I don't know why a statically linked program needs as
    specific oracle-installation.

    Compiling the source works with 8i and 9i

    When I will find 9i-features not supported in TOra, would
    you like to receive a feature request for version 2.0 or
    should I wait submitting it?

    Martin

     
  • Henrik Johnson
    Henrik Johnson
    2001-10-10

    Logged In: YES
    user_id=2931

    I do all the development of TOra on a laptop with 256 MB of
    memory. This should include linux, KDE, editor, browser,
    compilers and Oracle EE. How much more resource hungry is
    Oracle9 once you get it installed compared to Oracle8i? Do
    you have any idea, I will probably give it another whirl at
    installing on a really stripped down twm session if it is
    just the installation thats the problem.

    Please include the feature requests as you find them. Just
    don't be surprised if they aren't fixed right away (As I try
    to do with most of the feature requests as long as they are
    easy).

    I cant really say that I support Oracle 9 without having one
    to play with. Even if I could read the specs and figure most
    things out it would almost certanly not work if I can't test it.

    I've more or less decided to start freezing the 1.2 release
    just about now and will probably have a stable documented
    release ready before christmas (Though I'm not giving any
    guarantees). So the first 1.3 release (Where 9.0 stuff could
    start to turn up) should be appearing around january 2000,
    so it's not so long in the future.

    /Mauritz
    GlobeCom AB

     
  • Martin
    Martin
    2001-10-11

    Logged In: YES
    user_id=327932

    Hi Henrik

    I think, Oracle 9i itself ist not much more resource hungry
    as 8i. It seems that the SGA will increase by approx. 20M.

    First, I started an instance with version 8.1.7
    (db_block_buffers=7200, shared_pool_size=20000000)

    SVRMGR> startup
    ORACLE instance started.
    Total System Global Area 103047328 bytes
    Fixed Size 73888 bytes
    Variable Size 43720704 bytes
    Database Buffers 58982400 bytes
    Redo Buffers 270336 bytes
    Database mounted.
    Database opened.
    SVRMGR>

    Then, I started the same instance (with same init.ora) with
    9.0.1 (as you will do it for migration)
    SQL> startup
    ORA-32004: obsolete and/or deprecated parameter(s) specified
    ORACLE instance started.

    Total System Global Area 122185052 bytes
    Fixed Size 279900 bytes
    Variable Size 58720256 bytes
    Database Buffers 62914560 bytes
    Redo Buffers 270336 bytes
    Database mounted.
    Database opened.
    SQL>

    If your laptop has an ethernet adaptor and you have another
    machine, try one of the following possibilities:

    1. the other system can be any machine being able to display
    X11 graphics, so you can remote login to the laptop and use
    the X-server on the other machine (you don't need to start
    X11 on laptop)

    2. the other machine (L2) with more memory than the laptop
    (L1) must also be a linux machine with the correct version
    of glibc (2.2) and kernel (2.4) and it must exist an
    oracle-user with same uid/gid as on L1. Now, you can mount
    via NFS the oracle-directory from L1 to the same location on
    L2 "mount L1:/opt/oracle /opt/oracle". Maybe copy
    /etc/oratab and /etc/oraInst.loc from L1 to L2. Then, you
    can do the installation task on L2.

    Martin

     
  • Bobby O
    Bobby O
    2003-09-24

    Logged In: YES
    user_id=743978

    I also get errors for composite partitioning (range
    partition with list subpartition):

    Error in toExtract
    Operation: Create
    Object: TABLE FAMILY:MM3.PSL_PK
    Error: ORA-00942: table or view does not exist

    SELECT
    DECODE(
    t.monitoring
    ,'NO','NOMONITORING'
    , 'MONITORING'
    ) AS monitoring
    , t.table_name
    , LTRIM(t.degree) AS degree
    , LTRIM(t.instances) AS instances
    , DECODE(
    t.iot_type
    ,'IOT','INDEX'
    , 'HEAP'
    ) AS organization
    , DECODE(
    LTRIM(t.cache)
    ,'Y','CACHE'
    , 'NOCACHE'
    ) AS cache
    , p.def_pct_used
    , p.def_pct_free AS pct_free
    , p.def_ini_trans AS ini_trans
    , p.def_max_trans AS max_trans
    ,DECODE(
    p.def_initial_extent
    ,'DEFAULT',s.initial_extent
    ,p.def_initial_extent * :bs * 1024
    ) AS initial_extent
    ,DECODE(
    p.def_next_extent
    ,'DEFAULT',s.next_extent
    ,p.def_next_extent * :bs * 1024
    ) AS next_extent
    , DECODE(
    p.def_min_extents
    ,'DEFAULT',s.min_extents
    ,p.def_min_extents
    ) AS min_extents
    , DECODE(
    p.def_max_extents
    ,'DEFAULT',DECODE(
    s.max_extents
    ,2147483645,'unlimited'
    ,s.max_extents
    )
    ,2147483645,'unlimited'
    ,p.def_max_extents
    ) AS max_extents
    , DECODE(
    p.def_pct_increase
    ,'DEFAULT',s.pct_increase
    ,p.def_pct_increase
    ) AS pct_increase
    , DECODE(
    p.def_freelists
    ,0,1
    ,N<ERROR>VL(p.def_freelists,1)