Oracle tablespace and sqlplus, oraclesqlplus

Oracle tablespace and sqlplus, oraclesqlplus
1. Concept of tablespace

I have never heard of the table space term in databases that I have used before. When I saw the Oracle database some time ago, I found that the table space is everywhere. Therefore, tablespace should be a very common and important concept in Oracle databases.

A tablespace is a logical concept, a logical storage unit. So what isLogical storage unitAnd. Now we can compare the two images.

Table space is the largest Logical Structure in Oracle databases. Logically, the Oracle database consists of several tablespaces. The tablespace is closely related to the physical structure of the database. It corresponds to several data files on the disk.

Physically, the database data is stored in the data file, logically, the data is stored in the tablespace.

Table space is user-oriented.When we use the Oracle database, we will find that we all create tablespaces, and then create database objects in the tablespace. Instead of creating data files, we can create database objects in database files.

2. SQL plus

When connecting to Oracle for the first time, no visual operation interface was found, but the operation was performed in dos using the SQL * plus command. So I am introducing an SQL plus.

Oracle SQL * plus is a client tool that interacts with oracle. With SQL * plus, you can view and modify Database records. In SQL * plus, you can run the SQL * plus command and SQL statement. SQLPLUS is a random Installation Product of ORACLE.

Currently, many Oracle clients are very useful, such as PL/SQL, which provides a visual graphical interface for easy operation, on the Internet to find an introduction to the web version of the client you are interested can learn to see: http://blog.csdn.net/ggibenben1314/article/details/38237279

3. Use the SQL * plus command to log on to the database (for Oracle 11g)

To use oracle, you must first log on to the oracle database. After the Oracle database is installed, there are two built-in users:

User Name	Password
System	The management password you entered during installation
Sys as sysdba	Root

 

If you do not know the system or sys password, how can you log on to the system?

However, there is another problem during login. The solution is to Log On As sysdba.

1. Enter sysplus and press Enter.

2. Enter the username system as sysdba and press enter (or sys as sysdba press Enter)

3. Press enter without entering any password.

You can log on to the database.

Specifically, as sysdba is used to log on to sysdba. There are three logon identities for oracle:

Normal identity

Sysdba System Administrator identity

Sysoper System Operator identity

Each identity has different permissions.

4. Use the SQL * plus command for some simple operations

Create a tablespace in the database. The command format is as follows:

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

Example:

Create tablespacetest_pf datafile 'd: \ testOracle \ test_pf.dbf 'size 300 m;

(300 MB refers to MB)

TestOracle needs to be created first. Otherwise, the system will prompt that the data file does not exist.

Create user

Create user Username identified by password default tablespace; (default tablespace)

Modify user permissions:

Grant role 1, role 2 to user name;

Example:

Create user test_user identified by test123 default tablespace test_pf;

Grant dba, connect to test_user;

 

5. Relationship between tablespaces and users

There is a many-to-many relationship between the tablespace and the user, that is, one user can operate multiple tablespaces, and one tablespace can be operated by multiple users.

One user can only allocate one default tablespace, but multiple non-default tablespaces can be allocated. Specific allocation syntax

Alter user test_pf

Quota unlimited on xxx1

Quota unlimited on xxx2;

6. Summary

Compared with SQL server, the tablespace in Oracle databases should be the biggest difference. However, apart from this concept, many other things are similar to other databases, so although it was just getting started with him, it was not very difficult to associate with the previous knowledge.

Q: What are the attributes of Oracle tablespace ??

You don't have to understand too much, it's too complicated, and you can't remember how to use the SYS user to log on to the database and create tablespaces and users after the database is installed.
The logon script is sqlplus/as sysdba.
Create a tablespace script:
Create tablespace project_data datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ project \ project_data.dbf' size 4096 M AutoExtend On Next 1024 M Maxsize 10240 M extent management local segment space management auto;
Create temporary tablespace project_temp tempfile 'e: \ oracle \ product \ 10.2.0 \ oradata \ project \ project_temp.dbf' size 2048 m autoextend on next 1024 m maxsize 10240 m extent management local;
If the disk space of the server is not large enough, you can adjust the size of a parameter or create a tablespace file to another hard disk with a relatively large space.
In practice, it is very important to know how to create a tablespace and a temporary space. You need to understand these two sections and start with them.

Oracle tablespace Adjustment

Oracle SQL developer does not have a visual table space adjustment interface. It can only be executed using commands.
The Enterprise Manager of Oracle can be visually adjusted. Table space can be reduced only at the beginning of 11gR2. The previous version can only be increased but cannot be reduced. Note: The Oracle Enterprise Manager cannot be found after 11 GB.