Shulou(Shulou.com)06/02 Report--

How to create users in Oracle, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Create users and tablespaces:

1. Log in to linux and log in as oracle user (if you log in as root user, switch to oracle user with su-oracle command after logging in)

2. Open sqlplus in sysdba mode with the following command: sqlplus / as sysdba

Create temporary tablespaces:

--Query the absolute path of the temporary table space file. Absolute paths can be written by querying if desired. select name from v$tempfile; create temporary tablespace NOTIFY DB_TEMP tempfile '${ORACLE_HOME}\oradata\NOTIFY DB_TEMP.bdf' size 100m reuse autoextend on next 20m maxsize unlimited;

4. Create Tablespace:

--Query absolute path of user table space file: select name from v$datafile;create table space NOTIFY DB datafile '${ORACLE_HOME}\oradata\notifydb.dbf' size 100M reuse autoextend on next 40M maxsize unlimited default storage(initial 128k next 128k minextensions 2 maxextensions unlimited);

Create a user and password, specify the temporary table space and table space created above

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

6. Grant authority

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 operation, we can use hc_notify/hc_password to log in to the specified instance and create our own table.

Delete tablespaces:

1. View user permissions

--View that the user must have the permission to drop tablespace. If not, first authorize it with a more advanced user (such as sys) select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2where a1.privilege = 'DROP TABLESPACE'and a1.grant =a2.granted_role

2. Delete temporary tablespaces

Copy code--View temporary table space file select name from v$tempfile;--View relationship between user and table space select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS;--If the default temporary table space of a user is NOTIFYDB_TEMP, it is recommended to change alter user xxx temporary table space tempdefault;--Set tempdefault to default temporary table space alter database default temporary table space tempdefault;--Delete the tablespace NOTIFY DB_TEMP and its data objects and data files drop tablespace NOTIFY DB_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 NOTIFY DB_TEMP and its containing data objects and datafiles drop tablespace NOTIFY DB_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 current user's role select * from user_role_privs;--View current user's permissions select * from user_sys_privs;--View current user's table Operable permissions select * from user_tab_privs;--View constraints on 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 index composition, 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 records detailed information about tablespaces;--View user sequences select * from user_sequences;--View database sequences select * from dba_sequences; Case--Create temporary tablespace PINGAN_TEMP tempfile '/u01/app/oracle/oradata/XE/PINGAN_TEMP.bdf' size 100m reuse autoextend on next 20m maxsize unlimited;--Create table space create tablespace PINGAN datafile '/u01/app/oracle/oradata/XE/pingandb.dbf' size 200M reuse autoextend on next 40M maxsize unlimited default storage (initial 128k next 128k minextensions 2 maxextensions unlimited);--create user pingan identified by pingan default tablespace SZNS temporary tablespace SZNS_TEMP;--grant dba to pingan;grant connect,resource to pingan;grant select any table to pingan;grant delete any table to pingan;grant update any table to pingan;grant insert any table to pingan; After reading the above, do you know how to create users in Oracle? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.