-1

I have two schemas A and B. I'm trying to copy over table B.users to A. I've followed these steps:

Run in schema B:

GRANT ALL ON users TO A;

Run in schema A:

CREATE TABLE users AS (SELECT * FROM B.users);

This gives the error:

SQL Error: ORA-01536: space quota exceeded for tablespace 'A' 01536. 00000 - "space quota exceeded for tablespace '%s'" *Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace. *Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner.

The error seems to be misleading and I suspect something else is the problem here, because the users table has only some 1000 users or so and the tablespace in question has 2 GB allocated to it, all of it free. Furthermore trying to copy just a single row also gives the same error:

CREATE TABLE users AS (SELECT * FROM B.users where rownum < 2);

However, copying schema without any data succeeds:

CREATE TABLE users AS (SELECT * FROM B.users where rownum = -1); /* This works */

What am I missing here?

2

The user A must be running out of quota(or you might have forgotten to assign quota) on assigned tablespace.

The error seems to be misleading and I suspect something else is the problem here, because the users table has only some 1000 users or so and the tablespace in question has 2 GB allocated to it, all of it free.

Error is self-describing. It's stating that quota for the user(owner of the segment) has exceeded.

However, copying schema without any data succeeds:

CREATE TABLE users AS (SELECT * FROM B.users where rownum = -1); /* This works */

To execute this command user don't need quota because its simply creating an empty table whose definition will be stored in data dictionary.

Increase the quota for the user A as shown below.

SQL> conn sys as sysdba
SQL> alter user A quota <size> on <tablespace>;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.