Oralce new database, new Telnet user entire process

After Oracle installs, there is a default database, and in addition to this default database, we can create our own database.

For starters, to avoid trouble, you can use the ' Database Configuration Assistant ' Wizard to create a database.

After you create a database, you cannot immediately build a table in the database, you must first create the user of the database, and specify the table space for that user.

Relationship: A large data is divided into several table spaces, create a few users and then specify the corresponding table space and authorization, so that users can independently operate their own resources, often the user login into the table space in their own new table Ah, and so on objects, non-interference.

The following are the specific procedures for creating database users:

1. If a database named ' News ' is now in place, the DB2 directory already exists under the E:appadministratororadata directory (note: My oracle11g is installed under E:app).

2. Create a table space before creating a user

The format is: Format: Create tablespace table name datafile ' data file name ' size table space;

Such as:

sql> Create Tablespace Db2tabs

DataFile ' e:appadministratororadatadb2db2tabs.dbf '

Size 500M;

Where ' Db2tabs ' is your custom table space name, can be arbitrarily named; E:APPADMINISTRATORORADATADB2DB2TABS.DBF ' is the location of the data file, generally stored in your instance (database) directory, the ' db2tabs.dbf ' filename is arbitrary; ' Size 500M ' is the size of the specified data file, which is the size of the table space.

3. Now that the table space named ' Db2tabs ' is built, users can be created in the following

The format is: Format: Create user username identified by password default Tablespace table space table;

Such as:

Sql> create user User2 identified by user2 default Tablespace db2tabs;

--This creates the User2 user, the password is user2, the default tablespace, ' default Tablespace ', uses the tablespace created above, and if unspecified, the default table space is users, and the temporary tablespace is temp.

--Modify User default table space: Alter USER news default tablespace xxx;

--Modify user password: Alter USER news identified by XXX;

--Lock accounts: Alter user news account lock; --the corresponding and unlocked, is unlock

4. Then authorize to the new user

Sql> Grant Connect,resource to User2; ---To grant news users Connect,resource permissions

Sql> Grant DBA to User2; --Means to grant DBA authority to news users

The authorization was successful.

--Authorization of an object: Grant Select on Scott.dept to News-grants the SELECT permission of the Scott User's Dept table to news users

--Joint Authorization: Grant SELECT on Scott.dept to news with GRANT option-- News users also have the right to grant select rights to other users of the Scott.dept object, which is authorized under the Super Administrator, and where news has the power to authorize

--all permissions granted to the "grant all" scott.dept to news--scott.dept object are given to the news user

---Remove permission: Revoke select on Scott.dept to News

Note that officially, because there are fewer characters and more users, we typically create a role and grant the appropriate permissions, and then subordinate the user to the role, so that the user has the permissions that the role has, rather than directly authorizing the user, which is easy to manage, as follows:

--Create myrole role myrole;

--Grant its DBA and create Tablespace permissions: Grant unlimited TABLESPACE,DBA to Myrole;

--Subordinate to role: Grant Myrole to News

5. New Object

Once the authorization is successful, you can create new objects, and here are examples of new tables:

First log in with the news user created above, and then create the table:

CREATE TABLE TT (t VARCHAR2 (10));

--The default tablespace for the new table TT is the default tablespace for the news user, and if you want to build the table in another table space, this way:

Create Talbe TT (T varchar2 ()) tablespace othertabs;

--------------------------------------------------------------------------------------------------------------- ---

In addition, when it comes to teamwork, in general, we give each person an account to do the remote login to their server, for example, I want to create a new Kute user for all the resources of Scott's users, but all the resources of Scott's users are the same for each user. You can do this:

Create a new user and assign it to someone else to use to log on to my server

1. Backup Scott User

First create a new folder to store the backup: E:oracle_new_scott

Enter this folder in the DOS window and enter: EXP

The purpose is to export all the data of Scott's user to this folder, Exp is export, and exported.

When you enter a carriage return, prompt for the username and password, enter the user Scott and password to export.

2. Create user

First, you should enter your database as a super administrator.

Sqlplus sys/kute@db2 as SYSDBA;

--and then create the user, you can create your own table space, so the best, if not created then use the Default users table space

Create user User2 identified by user2 default Tablespace users quota 10M on users

The quota here is to give the 10M space of the Users table space to the User2 user

3. Authorization

Grant Create Session,create table,create view,connect to Kute

If the import does not succeed here, then authorize the unlimited tablespace permission to grant

4. Import

Also enter in that folder: Imp

There's a whole lot of stuff, and you can always return. Note that there are a total of two times you want to enter the user name: The first time, enter who to import the user, here is USER2@DB2, here user2 is my user, DB2 is the global database name, must write, otherwise the error user name/password is incorrect, it can not find the user , because your users are created under DB2, and the second time, enter who you want to import, this is Scott. Then you can always return to the car and finish.