Use the sqlplus command line tool to create users and tablespaces for oracle, sqlplusoracle

Use the sqlplus command line tool to create users and tablespaces for oracle, sqlplusoracle

Creating tablespaces and users and assigning permissions using the Enterprise Manager or PL/SQL graphical method that comes with Oracle10g is relatively simple. This article will introduce another method, use the command line tool of Oracle 9i: SQLPLUS

To create a tablespace. This method is more concise and convenient to use.

Hypothesis: assume that if you are using a Linux system, the Oracle user name is oracle. At the same time, you operate on the oracle server.

In Windows, click Start, click Run, Enter cmd, and click OK to open the command line window.

In the Linux graphic window, right-click the desktop and click "open terminal", and enter su-else l

After completing the preparations, enter the following command:

After sqlplus/nolog press enter, the prompt SQL> enter conn/as sysdba

Generally, you can log on. If it fails, try again with the password of the conn sys/sys user as sysdba.

Next, let's see where your current database files are generally stored:

Select name from v $ datafile; in windows, you may see the following results: SQL> select name from v $ datafile; NAME mongod: \ oracle \ oradata \ orcl \ system01.dbf D: \ oracle \ oradata \ orcl \ mongod: \ oracle \ oradata \ orcl \ cwmlite01.dbf D: \ oracle \ oradata \ orcl \ drsys01.dbf D: \ oracle \ oradata \ orcl \ indx01.dbf D: \ oracle \ oradata \ orcl \ tools01.dbf

Your data files are stored in the Directory D: \ oracle \/oradata \ orcl \.

In Linux, you may see the following results:

SQL> select name from v$datafile;　　NAME　　--------------------------------------------------------------------------------　　/oracle/oradata/orcl/system01.dbf　　/oracle/oradata/orcl/undotbs01.dbf　　/oracle/oradata/orcl/cwmlite01.dbf　　/oracle/oradata/orcl/drsys01.dbf　　/oracle/oradata/orcl/indx01.dbf　　/oracle/oradata/orcl/tools01.dbf

Your data files are stored in the/oracle/oradata/orcl/directory.

Well, we can start creating the database tablespace. The command format for creating the database tablespace is as follows:

The 'size of the file name corresponding to the create tablespace name datafile;

Example:

For windows:

create tablespace yang datafile 'D:\oracle\oradata\orcl\yang.dbf' size 3000m;

3000 m indicates MB

For the above Linux:

create tablespace yang datafile '/oracle/oradata/orcl/yang.dbf' size 3000m;

At this point, the required tablespace has been created.

Next, create a user. The command format for creating a user is as follows:

Create user Username identified by password default tablespace which tablespace is used by the user by default;

Modify user permissions:

Grant role 1, role 2 to user name;

Example:

create user yanglei identified by yang123 default tablespace yang;　　grant dba, connect to yanglei;

Authorization successful.

Ps: The following describes how to create a user in Oracle. The specific code is as follows:

Create user

-- Create the user create user MEP identified by whq1987 default tablespace MEP temporary tablespace MEP_TEMP profile DEFAULT;-- Grant/Revoke role privileges grant connect to MEP;grant datapump_exp_full_database to MEP;grant datapump_imp_full_database to MEP;grant dba to MEP;grant exp_full_database to MEP;grant imp_full_database to MEP;grant resource to MEP;-- Grant/Revoke system privileges grant alter_user to MEP;grant comment any table to MEP;grant create any view to MEP;grant create session to MEP;grant create user to MEP;grant delete any table to MEP;grant drop user to MEP;grant export full database to MEP;grant unlimited tablespace to MEP;

Summary

The above section describes how to use sqlplus to create users and tablespaces for oracle. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!