Good day,
There is no doubt a good reason for this (most likely to do with permissions) but it still looks humorous.
SQL> CTAS sh.customers jake.customers
Create Table As Select command failed to get DDL for table "SH"
SQL> DDL sh.customers
DDL Exception: sh.customers generation for TABLE failed - ORA-31608: specified o
bject of type COMMENT not found
ORA-06512: at "SYS.DBMS_METADATA", line 6480
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 6471
ORA-06512: at "SYS.DBMS_METADATA", line 9311
ORA-06512: at line 1
SQL> DESCRIBE sh.customers
Name Null? Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(50)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
SQL>
SQL> CREATE TABLE jake.customers
2 AS SELECT * FROM sh.customers;
Table JAKE.CUSTOMERS created.
Can anyone confirm if this is the intended behaviour? As well as what would be needed for the CTAS statement to succeed?
Regards,
jake