Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Assign a Tablespace to a User

By: FYIcenter.com

(Continued from previous topic...)

How To Assign a Tablespace to a Users?

When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> ALTER USER dev DEFAULT TABLESPACE USERS;
User altered.

SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> CREATE TABLE fyi (id NUMBER);
Table created.

SQL> DROP TABLE fyi;
Table dropped.

SQL> CREATE TABLE fyi (id NUMBER);
Table created.

As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.

(Continued on next topic...)

  1. What Is a User Account?
  2. What Is the Relation of a User Account and a Schema?
  3. What Is a User Role?
  4. What Are the System Predefined User Roles?
  5. What Are Internal User Account?
  6. How To Connect to the Server with User Account: SYS?
  7. How To Use Windows User to Connect to the Server?
  8. How To List All User Accounts?
  9. How To Create a New User Account?
  10. How To Change User Password?
  11. How To Delete a User Account?
  12. What Privilege Is Needed for a User to Connect to Oracle Server?
  13. How To Grant CREATE SESSION Privilege to a User?
  14. How To Revoke CREATE SESSION Privilege from a User?
  15. How To Lock and Unlock a User Account?
  16. What Privilege Is Needed for a User to Create Tables?
  17. How To Assign a Tablespace to a Users?
  18. What Privilege Is Needed for a User to Create Views?
  19. What Privilege Is Needed for a User to Create Indexes?
  20. What Privilege Is Needed for a User to Query Tables in Another Schema?
  21. What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
  22. What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
  23. How To Find Out What Privileges a User Currently Has?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...