Re: problems with dbms

From: Ecce Nihil <sct_at_picknowl.com.au>
Date: 23 Jun 2003 19:23:19 -0700
Message-ID: <96ce817c.0306231823.78632c54@posting.google.com>

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<cjuefvku4f54hc4ojcfel9thnugne9909t_at_4ax.com>...
>
> To Ecce Nihil:
>
> Can you confirm how you're running this?

You are right...I am running within a SQLPLUS session

> You are running it as the MY_SCHEMA user, right?

Yep

> What's the definition of the AGREEMENTS table? (including constraints)

From TOAD I copied the following text...

CREATE TABLE AGREEMENTS
(

  AGREEMENT_KEY                 NUMBER(12)      NOT NULL,
  AGREEMENT_VERSION             NUMBER(10)      NOT NULL,
  EXTRACT_DATE                  DATE,
  LOCAL_SERVICE_NAME            VARCHAR2(25),
  PRORATING_OPTION_CODE         VARCHAR2(5),
  PRORATING_OPTION_DESC         VARCHAR2(240),
  RECORD_STATUS_CODE            VARCHAR2(1),
  SOURCE_SYSTEM_NAME            VARCHAR2(240)   NOT NULL,
  TARIFF_ASSIGN_IND             VARCHAR2(1),
  TARIFF_GROUP_CHANGED_BY_NAME  VARCHAR2(6),
  TIME_STAMP_DT                 DATE            NOT NULL

)
TABLESPACE CORE_DIM_DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (

            INITIAL          3328K
            NEXT             1M
            MAXEXTENTS       2147483645
            PCTINCREASE      0
           )

NOLOGGING
PARTITION BY HASH (AGREEMENT_KEY)
PARTITIONS 8
STORE IN (CORE_DIM_DATA, CORE_DIM_DATA, CORE_DIM_DATA, CORE_DIM_DATA, CORE_DIM_DATA, CORE_DIM_DATA, CORE_DIM_DATA, CORE_DIM_DATA)
NOCACHE
NOPARALLEL; I tried re-creating this in our development environment and the dbms_metadata.get_ddl function worked with no problems. So the problem doesn't seem to lie in the table definition. I'm reluctant to simply recreate it in rpoduction as it's not worth it.

> Database version could help, too; 9.0 or 9.2, and which patch?

database version is 9.0.1.3.0, I'm not sure about patches.

>
> Can you reduce it down to a script to reproduce it starting from 'create user'
> and ending up with the call that fails? (You'd have to do that anyway if it
> were a problem in Oracle, but the process of reducing it down often points out
> any mistakes)

I might try that eventually but as the above re-creation didn;t reproduce the problem then I don't have much faith in being able to pin the problem down. Below is the full SQLPLUS session, with another table from the schema where get_ddl works OK (most tables do, two or maybe more don't). I might try looking further in the dictionary tables to see how these two tables differ from others that get_ddl works on, or maybe putting something into metalink.

thanks again
Shaun

for AGREEMENTS:
11:24:38 SQL> select user from dual;

USER

DWH_ADMIN 11:24:41 SQL> select owner from all_tables where table_name='AGREEMENTS';

OWNER

DWH_ADMIN 11:24:47 SQL> select owner,OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects where object_name='AGREEMENTS';

OWNER        OBJECT_NAME                    OBJECT_TYPE        STATUS
------------ ------------------------------ ------------------ -------
PUBLIC       AGREEMENTS                     SYNONYM            VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE PARTITION    VALID
DWH_ADMIN    AGREEMENTS                     TABLE              VALID

10 rows selected.

11:24:56 SQL> select
dbms_metadata.get_ddl('TABLE','AGREEMENTS','DWH_ADMIN') from dual; ERROR:
ORA-31603: object "AGREEMENTS" of type TABLE not found in schema "DWH_ADMIN"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 626
ORA-06512: at "SYS.DBMS_METADATA", line 1205
ORA-06512: at line 1

no rows selected

For GROSS_MARGINS...
11:29:06 SQL> select user from dual;

USER

DWH_ADMIN 11:29:10 SQL> select owner from all_tables where table_name='GROSS_MARGINS';

OWNER

DWH_ADMIN 11:29:28 SQL> select owner,OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects where object_name='GROSS_MARGINS';

OWNER        OBJECT_NAME                    OBJECT_TYPE        STATUS
------------ ------------------------------ ------------------ -------
PUBLIC       GROSS_MARGINS                  SYNONYM            VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE PARTITION    VALID
DWH_ADMIN    GROSS_MARGINS                  TABLE              VALID

10 rows selected.

11:29:35 SQL> select
dbms_metadata.get_ddl('TABLE','GROSS_MARGINS','DWH_ADMIN') from dual;

DBMS_METADATA.GET_DDL('TABLE','GROSS_MARGINS','DWH_ADMIN')

CREATE TABLE "DWH_ADMIN"."GROSS_MARGINS" ( "GROSS_MARGIN_AMT" NUMBER(10,2 11:29:58 SQL> quit Received on Mon Jun 23 2003 - 21:23:19 CDT

Re: problems with dbms_metadata