Table spaces and Sqlplus for Oracle

1. The concept of tablespace

None of the previously contacted databases have ever heard of the word tablespace, and the table space was found everywhere in the previous time when the Oracle database was seen. So tablespaces should be a very common and important concept in an Oracle database.

Tablespace is a logical concept, a logical storage unit. So what is called a logical storage unit ? Now compare them with two graphs

The tablespace (table space) is the largest logical structure in the Oracle database. Logically, an Oracle database consists of several table spaces. Table spaces are closely related to the physical structure of a database, which corresponds to several data files on disk.

Physically, the data of the database is stored in the data file, which logically means that the data is stored in the table space.

The concept of tablespace is user-oriented , and when we use the Oracle database we will find that we are creating table spaces and then creating database objects inside the table space. We do not create a data file, and then we create the database object in the database file.

2. SQL Plus

The first time I connected to Oracle, I found that there was no visual interface, but it was operated in DOS with the SQL * Plus command. So we're introducing a SQL plus.

Oracle of the Sql*plus is with Oracle client-side tools for interacting, with Sql*plus You can view and modify database records. In sql*plus , you can run the sql*plus command with the SQL statement. Sqlplus is a randomly installed product of Oracle Corporation

Now there are a lot of Oracle client is very useful, such as PL/SQL, provides a visual graphical interface, very convenient operation, on the Internet to find an introduction to the Web version of the client we are interested to learn to read: http://blog.csdn.net/ggibenben1314/ article/details/38237279

3. Log in to the database using the Sql*plus command (for Oracle 11g)

Use Oracle to log in to the Oracle database first. After the Oracle database is installed, there are two system-built users:

User name	Password
System	The admin password that you entered when you installed it
SYS as SYSDBA	Root

If you do not know the system or sys password, then how to log in

But there is a problem when landing, the solution is to use SYSDBA identity login

1. Enter Sysplus carriage return

2. User name Input system as SYSDBA carriage return (or SYS as SYSDBA enter)

3. The password does not enter any direct return

You can log in to the database

Where as SYSDBA is logged in with SYSDBA. There are three types of Oracle login identities:

Normal General identity

SYSDBA system Administrator Status

Sysoper System Operator Identity

Each of these identities corresponds to different permissions.

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

start creating a database tablespace with the following command format:

Create tablespace table space name datafile ' corresponding filename ' size;

Examples are as follows:

Create TABLESPACETEST_PF datafile ' D:\testOracle\test_pf.dbf ' size 300m;

(300m refers to 300MB)

Where testoracle needs to be created first, or the data file does not exist.

Create user

Create user username identified by password-default tablespace;(defaults table space)

To modify a user's permissions:

Grant role 1, role 2 to user name;

Examples are as follows:

Create user Test_user identified by test123 default Tablespace TEST_PF;

Grant DBA, connect to Test_user;

5. The relationship between tablespaces and users

table spaces and users are a many-to-many relationship in which a user can manipulate multiple table spaces, and a table space can be manipulated by multiple users.

A user can assign only one default tablespace, but multiple non-default tablespaces. The specific allocation syntax

Alter user TEST_PF

Quota Unlimited on xxx1

Quota Unlimited on xxx2;

6. Summary

about Oracle databases and previously contacted SQL Server should be the biggest difference compared to the table space, but in addition to this concept is a new thing, many other things and other databases are figured out, so although it is just beginning to touch this he, But it will not be difficult to relate to previous knowledge.