SQL create user authorization CREATE TABLE Space

Create user
SQLCode

1.

SQL code

1. SQL>Create UserVisiontv identifiedByVisiontvDefaultTablespace visiontv quota 10 mOnUsers;

SQL> create user visiontv identified by visiontv default tablespace visiontv quota 10 m on users;

Grant privilege
SQL code

1.

SQL code

1. SQL>Grant Connect, ResourceToVisiontv;

SQL> grant connect, resource to visiontv;

Create tablespace
SQL code

SQL code

1. 1. SQL>CreateTablespace visiontv datafile 'd:/dbserver/Oracle/oradata/oradb01/vis
2. 2. iontv. ora'Size100 m ReuseDefaultStorage (initial 500 kNext500 k pctincrease 20
3. 3 .);

1. SQL> Create tablespace visiontv datafile 'd:/dbserver/Oracle/oradata/oradb01/vis 2. iontv. ora 'size 100 m reuse default storage (initial 500 K next 500 k pctincrease 20 3 .);

It can be used after import.

//

To create a table from a user:
Server
Created: create user username identified by "password ";
Authorization: grant create session to user name;
Grant create table to user name;
Grant create tablespace to user name;
Grant create view to user name;

Client:
Create a tablespace (generally, create n tablespaces for storing data and an index space ):
Create tablespace name
Datafile 'path (the path must be created first)/***. dbf' size * m
Tempfile 'path/***. dbf' size * m
Autoextend on -- Automatic Growth
-- There are some commands that define the size, as needed
Default storage (
Initialize 100 K,
Next 100 K,
);
Server:
Grant the user the permission to use the tablespace:
Alter User Username quota unlimited on tablespace;
Or alter User Username quota * m on tablespace;
Since then, we have achieved that we can create tables and run SQL scripts at will!

// 1. Create a tablespace

SQL code

1. CreateTablespace osdb datafile'F:/Oracle/oradata/glsqjz/osdb. ora' Size100 m ReuseDefaultStorage (initial 500 kNext500 k pctincrease 20 );

Create tablespace osdb datafile 'f:/Oracle/oradata/glsqjz/osdb. ora 'size 100 m reuse default storage (initial 500 K next 500 k pctincrease 20 );

2. Create a user

SQL code

1. Create UserOsuser identifiedByOsuser

Create user osuser identified by osuser

; // The password is behind identified by, and the user name is above

3. user authorization

SQL code

2. GrantResource,Connect, Recovery_catalog_ownerToOsuser;
6. Grant Create Table ToOsuser;
10. Alter UserOsuser quota UnlimitedOnOsdb;
14. Alter UserOsuserDefaultTablespace osdb;

Grant resource, connect, recovery_catalog_owner to osuser; grant create table to osuser; Alter user osuser quota unlimited on osdb; Alter user osuser default tablespace osdb;

4. delete a tablespace

SQL code

1. DropTablespace tablespacename including contentsAndDatafiles;

Drop tablespace tablespacename including contents and datafiles;

5. delete a user

SQL code

1. Drop UserUser_nameCascade

Drop user user_name Cascade

6. Notes for deleting a table

When deleting all data in a table, you must use

SQL code

1. Truncate TableTable Name

Truncate table name

Because the drop table and delete * from table names are used, the space occupied by the table in the tablespace is not released, and the drop operation is performed several times. After the delete operation, the hundred megabytes of space on the tablespace are exhausted.