Forum Stats

  • 3,851,436 Users
  • 2,263,977 Discussions
  • 7,904,707 Comments

Discussions

Categories

SQLcl CTAS Command question

daniel_hauke
daniel_hauke Member Posts: 211 Bronze Badge
edited Aug 27, 2018 8:01AM in SQLcl

Hi everybody,

we are using Oracle SQLDeveloper Command-Line (SQLcl) version: 18.2.0.0.

I was trying to use the ctas Command to create the ddl from a table in a different tablespace. I was executing this command as sys User.

But it always throws me the following error:

SQL> ctas user1.test1 test;Create Table As Select command failed to get DDL for table "user1"

Is it not possible to use this command to create a table from different schemas? The command execute without errors when i want to create a table from my own schema.

Greetings Daniel

· Share on TwitterShare on Facebook

Best Answer

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Aug 24, 2018 11:05AM Answer ✓

    Even when logged in as HR, if I run

    SQL> ctas hr.employees hr.employees_Clone

    then it returns

    Create Table As Select command failed to get DDL for table "HR"

    so the issue is with the CTAS command not expecting "table" or "new_table" to be provided in the form of "schema"."table".

    Sounds like a bug, or at least an enhancement request.  Best to log an SR on MOS if you have an account.

    · Share on TwitterShare on Facebook

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Aug 24, 2018 11:05AM Answer ✓

    Even when logged in as HR, if I run

    SQL> ctas hr.employees hr.employees_Clone

    then it returns

    Create Table As Select command failed to get DDL for table "HR"

    so the issue is with the CTAS command not expecting "table" or "new_table" to be provided in the form of "schema"."table".

    Sounds like a bug, or at least an enhancement request.  Best to log an SR on MOS if you have an account.

    · Share on TwitterShare on Facebook
  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Aug 24, 2018 11:11AM

    In the meanwhile, check out the BRIDGE command:

    Capture.JPG

    · Share on TwitterShare on Facebook
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Aug 27, 2018 8:01AM

    Hi Glen,

    thanks for the reply. I also came across the bridge command which sounds also very interessting. I will try that command.

    And also i will raise an SR in MOS.

    Greetings Daniel

    · Share on TwitterShare on Facebook