You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Jun Li <al...@gmail.com> on 2006/02/10 06:10:43 UTC

Anyone did some tests on Oracle?

Hi all,

Just tried to read a live database into a XML file.
But there are a lot of system tables generated which I think shouldn't be
included.
My database has only one table called test. However, the XML looks like
this:

  <database>
    <table name="DR$POLICY_TAB">
      <column name="PLT_POLICY" primaryKey="false" required="false"
type="CHAR" size="1" autoIncrement="false"/>
      <column name="PLT_LANGCOL" primaryKey="false" required="false"
type="CHAR" size="1" autoIncrement="false"/>
    </table>
    <table name="SQLN_EXPLAIN_PLAN">
      <column name="STATEMENT_ID" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="TIMESTAMP" primaryKey="false" required="false"
type="TIMESTAMP" size="7" autoIncrement="false"/>
      <column name="REMARKS" primaryKey="false" required="false"
type="VARCHAR" size="80" autoIncrement="false"/>
      <column name="OPERATION" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OPTIONS" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OBJECT_NODE" primaryKey="false" required="false"
type="VARCHAR" size="128" autoIncrement="false"/>
      <column name="OBJECT_OWNER" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OBJECT_NAME" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OBJECT_INSTANCE" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="OBJECT_TYPE" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OPTIMIZER" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="SEARCH_COLUMNS" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="ID" primaryKey="false" required="false" type="INTEGER"
size="22" autoIncrement="false"/>
      <column name="PARENT_ID" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="POSITION" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="COST" primaryKey="false" required="false" type="INTEGER"
size="22" autoIncrement="false"/>
      <column name="CARDINALITY" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="BYTES" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="OTHER_TAG" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="PARTITION_START" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="PARTITION_STOP" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="PARTITION_ID" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="OTHER" primaryKey="false" required="false"
type="LONGVARCHAR" size="0" autoIncrement="false"/>
      <column name="DISTRIBUTION" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="STATEMENT_ID" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="TIMESTAMP" primaryKey="false" required="false"
type="TIMESTAMP" size="7" autoIncrement="false"/>
      <column name="REMARKS" primaryKey="false" required="false"
type="VARCHAR" size="80" autoIncrement="false"/>
      <column name="OPERATION" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OPTIONS" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OBJECT_NODE" primaryKey="false" required="false"
type="VARCHAR" size="128" autoIncrement="false"/>
      <column name="OBJECT_OWNER" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OBJECT_NAME" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OBJECT_INSTANCE" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="OBJECT_TYPE" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
      <column name="OPTIMIZER" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="SEARCH_COLUMNS" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="ID" primaryKey="false" required="false" type="INTEGER"
size="22" autoIncrement="false"/>
      <column name="PARENT_ID" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="POSITION" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="COST" primaryKey="false" required="false" type="INTEGER"
size="22" autoIncrement="false"/>
      <column name="CARDINALITY" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="BYTES" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="OTHER_TAG" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="PARTITION_START" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="PARTITION_STOP" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="PARTITION_ID" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="OTHER" primaryKey="false" required="false"
type="LONGVARCHAR" size="0" autoIncrement="false"/>
      <column name="DISTRIBUTION" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
    </table>
    <table name="TEST">
      <column name="TESTID" primaryKey="false" required="true"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="TEST" primaryKey="false" required="false" type="VARCHAR"
size="30" autoIncrement="false"/>
    </table>
    <table name="CS_SRS">
      <column name="CS_NAME" primaryKey="false" required="false"
type="VARCHAR" size="68" autoIncrement="false"/>
      <column name="SRID" primaryKey="true" required="true" type="INTEGER"
size="22" autoIncrement="false"/>
      <column name="AUTH_SRID" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="AUTH_NAME" primaryKey="false" required="false"
type="VARCHAR" size="256" autoIncrement="false"/>
      <column name="WKTEXT" primaryKey="false" required="false"
type="VARCHAR" size="2046" autoIncrement="false"/>
      <column name="CS_BOUNDS" primaryKey="false" required="false"
type="OTHER" size="1" autoIncrement="false"/>
    </table>
    <table name="OGIS_SPATIAL_REFERENCE_SYSTEMS">
      <column name="SRID" primaryKey="true" required="true" type="INTEGER"
size="22" autoIncrement="false"/>
      <column name="AUTH_NAME" primaryKey="false" required="false"
type="VARCHAR" size="100" autoIncrement="false"/>
      <column name="AUTH_SRID" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
      <column name="SRTEXT" primaryKey="false" required="false"
type="VARCHAR" size="1000" autoIncrement="false"/>
      <column name="SRNUM" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
    </table>
    <table name="SDO_ANGLE_UNITS">
      <column name="SDO_UNIT" primaryKey="false" required="false"
type="VARCHAR" size="32" autoIncrement="false"/>
      <column name="UNIT_NAME" primaryKey="false" required="false"
type="VARCHAR" size="100" autoIncrement="false"/>
      <column name="CONVERSION_FACTOR" primaryKey="false" required="false"
type="INTEGER" size="22" autoIncrement="false"/>
    </table>

........

I'm using Oracle 9i with JDBC ojdbc14-10.2.0.1.0

And for a database with more tables (about 200), it took very long to read
the database into model, but it has no problem of reading SQL Server
database in with the same amount of tables. Any problem there?

Thanks

--------------
Cheers,
Jun

Re: Anyone did some tests on Oracle?

Posted by Jun Li <al...@gmail.com>.
>
> The XML looks like you did not specify the schema to read which
> results in an XML with all tables that you have the rights to see. For
> the bigger databases (Oracle, Db2, Sql Server) you should always
> specify the schema. For Oracle this is usually the same as the
> username, though completely in uppercase.



Thanks, now it works well.



--
--------------
Cheers,
Jun

Re: Anyone did some tests on Oracle?

Posted by Thomas Dudziak <to...@gmail.com>.
On 2/10/06, Jun Li <al...@gmail.com> wrote:

> Just tried to read a live database into a XML file.
> But there are a lot of system tables generated which I think shouldn't be
> included.
> My database has only one table called test. However, the XML looks like
> this:

<snip>

> I'm using Oracle 9i with JDBC ojdbc14-10.2.0.1.0
>
> And for a database with more tables (about 200), it took very long to read
> the database into model, but it has no problem of reading SQL Server
> database in with the same amount of tables. Any problem there?

The XML looks like you did not specify the schema to read which
results in an XML with all tables that you have the rights to see. For
the bigger databases (Oracle, Db2, Sql Server) you should always
specify the schema. For Oracle this is usually the same as the
username, though completely in uppercase.

Tom