How to create a temporary table in Oracle SQL when you get the error: insufficient privileges tips

Question

I am using Toad and Oracle SQL.

I am trying to create a table as

create global temporary table tmptbl
on commit preserve rows as  select * from mySchem.MyTable;

But I get an error

ORA-01031: insufficient privileges tips

Probably because I don't have the rights to save into mySchem.

However how can I get around the problem so I save the table locally somehow and I am ok with it if the table disappears when I close Toad. I am looking for creating a temporary "work table" as we know from SAS.

This post was not a help: How do you create a temporary table in an Oracle database?

Answer 1

Oracle workaround:– grant rights to create a table.

create global temporary table tmptbl
on commit preserve rows as  select * from mySchem.MyTable;

Answer 2

You need to grant CREATE TABLE in addition to have SELECT privileges to read the source table. There is no need to be able to write to the tablespace used by the source table because the temporary table will be stored in the default temporary tablespace.