Create oracle tablespace and user permission query in linux

Create oracle tablespace and user permission query create user and tablespace in linux: www.2cto.com 1. log on to linux and Log On As an oracle user (if it is logged on as a root user, after logon, use the su-oracle command to switch to an oracle user. 2. Open sqlplus in sysdba mode. Run the following command: sqlplus/as sysdba 3. Create a temporary tablespace: SQL code: query the absolute path of temporary tablespace files. If necessary, you can write the absolute path through the query. You can select name from v $ tempfile using $ {ORACLE_HOME; create temporary tablespace NOTIFYDB_TEMP tempfile '$ {ORACLE_HOME} \ oradata \ NOTIFYDB_TEMP.bdf' size 100 m reuse autoextend on next 20 m maxsize unlimited; 4. create a tablespace: SQL code -- query the absolute path of a user tablespace file: select name from v $ datafile; create tablespace NOTIFYDB datafile '$ {ORACLE_HOME} \ oradata \ notifydb. dbf 'size 100 M reuse autoextend on next 40 M maxsize unlimited defaul T storage (initial 128 k next 128 k minextents 2 maxextents unlimited); 5. Create a user and password, SQL code create user hc_policy identified by hc_password default tablespace policydb temporary tablespace policydb_temp; 6. grant permissions SQL code grant dba to hc_policy; 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; gra Nt insert any table to hc_notify; after the above operation, you can use hc_notify/hc_password to log on to the specified instance and create our own table. Delete tablespace: 1. view the user permission SQL code -- view that the user must have the permission to drop tablespace. If not, first authorize select a2.username with a more advanced user (such as sys, 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 TABLESPACE SQL code -- view temporary TABLESPACE file select name from v $ tempfile; -- view the relationship between users and tablespaces select USERNAME, TEMPORARY_TABLESPACE from DBA_USERS; -- if a user's default temporary tablespace is policydb_temp, it is recommended to change alter user xxx temporary tablespace tempdefault; --- set tempdefault to the default temporary tablespace alter database default temporary tablespace tempdefault; -- delete the tablespace yydb_temp and its data objects and data files drop tablespace policydb_temp including contents and datafiles; 3. delete user tablespace SQL code -- View tablespace file select name from v $ datafile; -- stop online use of alter tablespace name offline; -- delete the tablespace policydb_temp and its data objects and data files drop tablespace policydb_temp including contents and datafiles; Oracle user permission query related operations: SQL code -- View All users select * from all_users; -- view the current user information select * from user_users; -- view the current user's role select * from user_role_privs; -- view the current user's permission select * from user_sys_privs; -- view the current user's table operation permission select * from user_tab_privs; -- view the constraints of a table. Note that the table name should be capitalized select * from user_constraints where table_name = 'tbl _ XXX '; -- View All indexes of a table. Note that the table name must be capitalized: select index_name, index_type, status, blevel from user_indexes where table_name = 'tbl _ XXX'; -- view the index structure, note that the table name should be capitalized select table_name, index_name, column_name, column_position FROM user_ind_columns WHERE table_name = 'tbl _ XXX '; -- the system data dictionary DBA_TABLESPACES records details about the tablespace. select * from sys. dba_tablespaces; -- view the user sequence select * from user_sequences; -- view the database sequence select * from dba_sequences;