Create a user-created table in the Oracle database

The first attempt to use the Oracle database today is indeed worth some twists and turns, but it is really hard to work in only one environment. I am used to SQL Server. Although SQL Server is used for addition, deletion, modification, and query, it is strange. The reason is that the long-term use of the same tool is the reason for appearance, and the root cause is that the recognition of such products is not enough, so I did not have to think deeply about it, so I could not give a similar picture. There is a very simple way to do this well, that is, starting from the appearance reasons, tools from different vendors with multiple touchpoints for similar products, the difference in their use will prompt me to gain a deep understanding of such products and think about such products. The following describes the process and statements for creating users and data tables in Oracle.

 

First, you must know that Oracle has three connection identities: Normal, sysdba, and sysoper. They represent common users, system administrators, and system operators. Sysdba has the highest permissions. (For details, visit http://blog.csdn.net/tianlesoftware/archive/2009/10/23/425155.aspx.)

 

Then, when installing the Oracle database, we will have a sysdba user. For example, the user name and password are Scott/orcl. In this case, we need to create a normal user and use this user to import the SQL script of the table to the database system. To use sqlplus in this example, you need to go to the command line tool. The process is as follows:

 

1. Connect to the database system as a system administrator

Sqlplus Scott/orcl as sysdba

 

2. Create a tablespace

Create tablespace tablespacename datafile 'd:/test/tablespacefile. dbf'size 100 m;

 

3. Create a new account

Create user D identified by D;

 

4. Specify the tablespace to the new account.

Alter user D default tablespace tablespacename;

 

Note: You can write 3, 4 together: create user D identified by D default tablespace tablespacename;

 

5. Set permissions. Now the newly created account has the identity of a common user. You can use some tools such as Oracle SQL handler to try it out!

Grant create session, create table, create view, unlimited tablespace to dd;

 

6. Check the current connected user (which can be omitted)

Show user;

 

7. Connect to the database as account d

Conn D/d;

 

8. Import the SQL script to the database.

@ D:/test/drp-create. SQL;

 

9. After the import, you can check which tables and views have been created.

Select * From tab;

 

The newly created account only has a normal identity. We can also make it that sysdba is normal.

Create user dd identified by dd default tablespace tablespacename;

Grant sysdba to dd;

Grant create session, create table, create view, unlimited tablespace to dd; // grant general support Permissions

Now, you can enter the system as sysdba or normal. The syntax is as follows:

1. sqlplus DD/DD as sysdba

2. sqlplus DD/dd

If it is already in the sqlplus tool, you can use the conn or connection command in the same format as the two above.

 

It is only a matter of time to learn more about Oracle databases, but more importantly, this is the starting point for in-depth consideration of database products!