Create users and tablespaces:

1. Log in to linux and log in as oracle user (if root is logged in, switch to oracle user with Su oracle command after login)

2. Open sqlplus in sysdba mode. The command is as follows: sqlplus / as sysdba

3. Create a temporary table space:

--Queries the absolute path of the temporary tablespace file. If necessary, the absolute path can be written through query. General use ${ORACLE_HOME}That's all right.  
select name from v$tempfile;  
create temporary tablespace NOTIFYDB_TEMP tempfile '${ORACLE_HOME}\oradata\NOTIFYDB_TEMP.bdf' size 100m reuse autoextend on next 20m maxsize unlimited;  

4. Create a table space:

--To query the absolute path of a user tablespace file:
select name from v$datafile;
create tablespace NOTIFYDB datafile '${ORACLE_HOME}\oradata\notifydb.dbf' size 100M reuse autoextend on next 40M maxsize unlimited default storage(initial 128k next 128k minextents 2 maxextents unlimited);

5. Create user and password, and specify temporary tablespace and tablespace created above

create user hc_notify identified by hc_password default tablespace NOTIFYDB temporary tablespace NOTIFYDB_TEMP;

6. Authorization

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;

After the above operations, you can log in to the specified instance and create our own table using HC ﹣ notify / HC ﹣ password.

Delete tablespace:

1. View user permissions

--View users to have drop tablespace If not,Use more advanced users first(as sys)Grant authorization
select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2
where a1.privilege = 'DROP TABLESPACE'
and a1.grantee =a2.granted_role

2. Delete temporary table space

Copy code
 --View temporary tablespace files
select name from v$tempfile;
--View the relationship between users and tablespaces
select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS;
--If the default temporary table space of a user is notifydb? Temp, it is recommended to change it.
alter user xxx temporary tablespace tempdefault;
---Set tempdefault as the default temporary tablespace
alter database default temporary tablespace tempdefault;
--Delete tablespace notifydb? Temp and its containing data objects and data files
drop tablespace NOTIFYDB_TEMP including contents and datafiles; 

3. Delete user tablespace

--View tablespace files
select name from v$datafile;
--Stop online use of tablespaces
alter tablespace Tablespace name offline;
--Delete tablespace NOTIFYDB_TEMP And contains data objects and data files
drop tablespace NOTIFYDB_TEMP including contents and datafiles; 

Oracle user permission query related operations:

--View all users
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 the permissions of the current user
select * from user_sys_privs;
--View the table actionable permissions of the current user
select * from user_tab_privs;

--View constraints for a table,Note that table names should be capitalized
select * from user_constraints where table_name='TBL_XXX';
--View all indexes of a table,Note that table names should be capitalized
select index_name,index_type,status,blevel from user_indexes where table_name = 'TBL_XXX';
--View composition of index,Note that table names should be capitalized
select table_name,index_name,column_name, column_position FROM user_ind_columns WHERE table_name='TBL_XXX';

--System data dictionary DBA_TABLESPACES Details about tablespaces are recorded in
select * from sys.dba_tablespaces;

--View user sequence
select * from user_sequences;
--View database sequence
select * from dba_sequences;