1

I tried to simply create a table with the structure of another table to sync them contents later. When is use "CREATE as SELECT", it just returns the error ORA-00933 "SQL command not properly ended"

As mentioned in the link, the syntax should be OK. https://www.techonthenet.com/oracle/tables/create_table2.php Both tables should be stored in the same user scheme. The only difference is the added user scheme and tablespace.

CREATE TABLE "MYUSER.TABLE_B"
as (SELECT * FROM "MYUSER.TABLE_A")
TABLESPACE "SANDBOX" ;
Alex
  • 21
  • 2
  • 5
  • Ge rid of all those double-quotes. – OldProgrammer Aug 31 '17 at 16:36
  • 1
    Possibly wrong syntax for the placement of tablespace? https://stackoverflow.com/questions/20620595/creating-a-table-from-a-query-using-a-different-tablespace-oracle-sql – xQbert Aug 31 '17 at 16:37
  • the double quotes WERE part of the problem, but were used by some generated code in the SQLD assistant. I kept them just in case a query over a dblink to another shema needs them. TABLESPACE apparently WAS on the wrong position, but i use it at that position when creating new tables and it works fine. Also thanks for pointing out the other answer, i didnt find it when i was searching SO – Alex Sep 01 '17 at 14:45

1 Answers1

0

I don't have Oracle database with me now, but I am sure, you can use like this-

CREATE TABLE "MYUSER.TABLE_B"
TABLESPACE "SANDBOX" 
AS
SELECT *
FROM "MYUSER.TABLE_A";
Chetan Pangam
  • 303
  • 3
  • 9