Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use sqlplus command line tools for oracle to create users and tablespaces, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

It is relatively simple to create tablespaces and users and assign permissions using the enterprise manager or PL/SQL graphical method that comes with Oracle10g.

To create tablespaces, this method is more concise and quick to use.

Assumption: the article assumes that if you are using a Linux system, the Oracle user name is oracle. At the same time, you are operating on the oracle server.

If you are on Windows, click start, then click run, enter cmd and click OK to open the command line window

If you are in the graphics window of Linux, right-click on the desktop and click "Open Terminal", then type su-oracl

After making the above preparations, enter the following command:

When sqlplus / nolog returns, the prompt SQL > will appear and type conn / as sysdba.

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

Next, let's take a look at where your current database files are typically placed:

The results you may see under select name from vault data windows are as follows: SQL > select name from v$datafile NAME-D:\ oracle\ oradata\ orcl\ system01.dbf D:\ oracle\ oradata\ orcl\ undotbs01.dbf D:\ oracle\ Oradata\ orcl\ cwmlite01.dbf D:\ oracle\ oradata\ orcl\ drsys01.dbf D:\ oracle\ oradata\ orcl\ indx01.dbf D:\ oracle\ oradata\ orcl\ tools01.dbf

Indicates that your data files are placed in D:\ oracle\ / oradata\ orcl\ this directory

The results you may see under Linux are as follows:

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

Indicates that your data files are placed in the / oracle/oradata/orcl/ directory

OK, we can start creating database tablespaces. The format of the command to create database tablespaces is as follows:

File name corresponding to create tablespace tablespace name datafile 'size size

Examples are as follows:

For the above windows situation:

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

3000m means 3000MB.

In the case of the above Linux:

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

At this point, the required tablespaces have been established.

Next, let's start to create a user. The format of the command to create a user is as follows:

Create user username identified by password which tablespace does the default tablespace user use by default

Modify the permissions of the user:

Grant role 1, role 2 to user name

Examples are as follows:

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

Authorization successful.

Ps: let's take a look at how Oracle creates a user. The specific code is as follows:

Create a 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; thank you for reading this article carefully. I hope the article "how to use the sqlplus command line tool to create users and tablespaces for oracle" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and follow the industry information channel. More related knowledge is waiting for you to learn!

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.