Sample Code used to create a tablespace in Oracle

The following articles mainly use the relevant Code to introduce how to create a tablespace in Oracle and grant permissions to users, the following describes how to view related permissions and how to create temporary tablespace in Oracle. We hope this will be helpful in your future study.

SQL code

 
 

  
  
CREATE TEMPORARY TABLESPACE test_temp   
  
  
TEMPFILE 'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'   
  
  
SIZE 32M   
  
  
AUTOEXTEND ON   
  
  
NEXT 32M MAXSIZE 2048M   
  
  
EXTENT MANAGEMENT LOCAL;   
  
  
CREATE TEMPORARY TABLESPACE test_temp  
  
  
TEMPFILE 'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'  
  
  
SIZE 32M  
  
  
AUTOEXTEND ON  
  
  
NEXT 32M MAXSIZE 2048M  
  
  
 
 
 

Extent management local; Oracle creates user tablespace

SQL code

 
 

  
  
CREATE TABLESPACE test_data   
  
  
LOGGING   
  
  
DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'   
  
  
SIZE 32M   
  
  
AUTOEXTEND ON   
  
  
NEXT 32M MAXSIZE 2048M   
  
  
EXTENT MANAGEMENT LOCAL;   
  
  
CREATE TABLESPACE test_data  
  
  
LOGGING  
  
  
DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'   
  
  
SIZE 32M   
  
  
AUTOEXTEND ON   
  
  
NEXT 32M MAXSIZE 2048M  
  
  
 
 
 

Extent management local; Delete tablespaces including physical data files)

SQL code

 
 

  
  
drop tablespace egov0 including contents and datafiles;   
  
  
drop tablespace egov0 including contents and datafiles;  
 
 

Create a user and customize a space in Oracle

SQL code

 
 

  
  
CREATE USER username IDENTIFIED BY password   
  
  
DEFAULT TABLESPACE TEST_DATA   
  
  
TEMPORARY TABLESPACE TEST_TEMP;   
  
  
CREATE USER username IDENTIFIED BY password  
  
  
DEFAULT TABLESPACE TEST_DATA  
  
  
TEMPORARY TABLESPACE TEST_TEMP;   
 
 

Empower users

SQL code

 
 

  
  
GRANT   
  
  
CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,   
  
  
ALTER ANY TABLE, ALTER ANY PROCEDURE,   
  
  
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,   
  
  
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE   
  
  
TO username;   
  
  
GRANT   
  
  
CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,  
  
  
ALTER ANY TABLE, ALTER ANY PROCEDURE,  
  
  
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,  
  
  
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE  
  
  
TO username;   
 
 

View User Permissions

View All Users

SQL code

 
 

  
  
SELECT * FROM DBA_USERS;   
  
  
SELECT * FROM ALL_USERS;   
  
  
SELECT * FROM USER_USERS;   
  
  
SELECT * FROM DBA_USERS;  
  
  
SELECT * FROM ALL_USERS;  
  
  
SELECT * FROM USER_USERS;   
  
  
 
 
 

View User System Permissions

SQL code

 
 

  
  
SELECT * FROM DBA_SYS_PRIVS;   
  
  
SELECT * FROM USER_SYS_PRIVS;   
  
  
SELECT * FROM DBA_SYS_PRIVS;  
  
  
SELECT * FROM USER_SYS_PRIVS;   
  
  
 
 
 

View user objects or role Permissions

SQL code

 
 

  
  
SELECT * FROM DBA_TAB_PRIVS;   
  
  
SELECT * FROM ALL_TAB_PRIVS;   
  
  
SELECT * FROM USER_TAB_PRIVS;   
  
  
SELECT * FROM DBA_TAB_PRIVS;  
  
  
SELECT * FROM ALL_TAB_PRIVS;  
  
  
 
 
 

SELECT * FROM USER_TAB_PRIVS; view all roles

SQL code

 
 

  
  
SELECT * FROM DBA_ROLES;  
 
 

SELECT * FROM DBA_ROLES; view roles owned by a user or role

SQL code

 
 

  
  
SELECT * FROM DBA_ROLE_PRIVS;   
  
  
SELECT * FROM USER_ROLE_PRIVS;   
  
  
SELECT * FROM DBA_ROLE_PRIVS;  
  
  
SELECT * FROM USER_ROLE_PRIVS;   
 
 

Delete all tables created in the tablespace

SQL code

 

 
 

  
  
Declare
  
  
Vsql varchar2 (200 );
  
  
Cursor c1 is
  
  
Select 'drop table' | table_name | 'cascade constraints 'v_name
  
  
From user_tables where tablespace_name = 'tablespace name ';
  
  
BEGIN
  
  
For I in c1 loop
  
  
Vsql: = I. v_name;
  
  
Execute immediate vsql;
  
  
End loop;
  
  
End;
 
 

The above content is related to creating tablespaces for Oracle, assigning permissions to users, and viewing the introduction of permissions. I hope you will get some benefits.