Oracle creates table spaces and assigns user permissions

1. Create User table Space

Su-oraclesqlplus/as SYSDBA

--Create a temporary tablespace--queries the absolute path of the temporary tablespace file. The absolute path can be written by query, if necessary. General use ${oracle_home} on it  --query Oracle_home:echo $ORACLE _home

Select name from V$tempfile;  
--The table space queried is/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF

Create temporary tablespace notifydb_temp tempfile ' \u01\app\oracle\oradata\notifydb_temp.bdf ' size 100m reuse Autoextend on next 20m MaxSize Unlimited; --Create TABLE space--Query the absolute path of the user tablespace file: Select name from V$datafile;create tablespace notifydb datafile ' \u01\app\oracle\oradata\  NOTIFYDB.DBF ' size 100M reuse autoextend on next 40M maxsize Unlimited default storage (initial 128k next 128k minextents 2 Maxextents Unlimited); --Create user and password, specify temporary tablespace and tablespace created on top create user hc_notify identified by hc_password default tablespace notifydb temporary Tablespa CE notifydb_temp; --Empowering Grant DBA to hc_notify; Grant Connect,resource to Hc_notify; Grant Select any table to hc_notify; Grant Delete any table to hc_notify; Grant update any table to hc_notify; Grant insert any table to hc_notify;

2. Delete Table space

--View User rights--view user's permission to have drop tablespace, if not, give authorization to select A2.username,a1.privilege from Dba_sys_privs A1 with a more advanced user (such as SYS), User_role_privs a2where a1.privilege = ' DROP tablespace ' and a1.grantee =a2.granted_role--Delete temp tablespace--View temporary tablespace file select name From v$tempfile;--View user and tablespace relationships select Username,temporary_tablespace from dba_users;--if a user's default temporary tablespace is Notifydb_temp, It is recommended to make a change to alter user xxx temporary tablespace tempdefault;---set Tempdefault to the default temporary tablespace ALTER DATABASE defaults temporary Tablespace tempdefault;--Delete Tablespace notifydb_temp and its containing data objects and data files drop tablespace notifydb_temp including contents and datafiles; --Delete User tablespace--View tablespace file select name from v$datafile;--stop table space online using the alter tablespace tablespace name offline;--Delete table Space notifydb_ TEMP and its containing data objects and data files drop tablespace notifydb_temp including contents and datafiles; --oracle user Rights query related actions:--View all user select * from all_users;--View Current user Information select * from user_users;--View the role of the current user select * from User_ role_privs;--View permissions for the current user select * from user_sys_privs;--view the current user's table operational permissions select * FROM user_tab_privs;--view constraints on a table, note that the table name should be capitalized SELect * from user_constraints where table_name= ' tbl_xxx ';--to view all indexes of a table, note that the table name should be capitalized in select Index_name,index_type,status, Blevel from user_indexes where table_name = ' tbl_xxx ';--View the composition of the index, note the name of the table to capitalize select Table_name,index_name,column_name, Column_position from User_ind_columns WHERE table_name= ' tbl_xxx ';--detailed information about tablespaces is recorded in the System data dictionary dba_tablespaces select * FROM sys.dba_tablespaces;--View User Sequence select * from user_sequences;--view database sequence select * from Dba_sequences;

　　

Oracle creates table spaces and assigns user permissions