Oracle Operations on table spaces, users, user permissions

I. Operations on the tablespace 1, creating tablespace (Create Tablespace)

--' hpdb_tablespace ' specifies tablespace name--' e:\hpdb.dbf ' specifies tablespace data file name--Size Specifies the initialization size of the tablespace--Autoextend on next 30M specifies the amount of space that is automatically amplified when table space is low
--Autoextend off to stop auto-expanding table space
--MaxSize Maximum space for unlimited table space does not set the upper limit
CreateTablespace hpdb_tablespace datafile'e:\hpdb.dbf'Size 100M Autoextend on Next30M MaxSize Unlimited;
CreateTablespace table DataFile' e:\aa.dbf 'Size 10M;

2. Modify the size of the table space

Alter Database ' e:\hpdb.dbf ' resize 30M;

3. Set automatic expansion for table space

Alter Database ' e:\hpdb.dbf '  on Next 30M MaxSize Unlimited;

4. Turn off automatic expansion of table space

Alter Database ' e:\hpdb.dbf ' off

5. Add a data file to the table space

Alter Add ' e:\22.dbf ' size 100m;

6. Delete a data file in the Tablespace

Alter Drop ' e:\\22.dbf '

7. Delete Table space

--only the table space is deleted, and the corresponding data file is not deletedDroptablespace hpdb_tablespace;--Delete both tablespace and data files--(1) offline the table space firstAltertablespace hpdb_tablespace offline;--(2) Delete table space and data files
--Delete table space, data fileDropTablespace Hpdb_tablespace including contents anddatafiles;
--Delete table space, data files, associations DropTablespace Hpdb_tablespace including contents anddatafiles cascade constraint;

Ii. operations on temporary tablespace 1, creating a temporary table space (Create temporary tablespace)

--Similar to how table spaces are created
--Note: The tablespace is created by creating tablespace, the temporary tablespace is created through the Create temporary tablespace class, and the tablespace specifies the name of the data file by DataFile for the table space The temporary tablespace uses Tempfile to specify the name of the data file-the temporary tablespace: The primary purpose of the staging table space is to provide temporary computing space when the database is sorted, manage indexes, access views, and so on, and the system will automatically clean up after the operation is complete.  Create' e:\hpdb_tmp.dbf 'onnext 30M MaxSize Unlimited;

2. Viewing temporary table spaces

--View temporary table space
Select from V$tempfile
--View the temporary table space for the current user
Select  from where property_name=' default_temp_tablespace ';

3. Modify the size of the temp table space

Alter Database ' e:\hpdb_tmp.dbf ' resize 30M;

4. Set auto-scale for temporary table space

Alter Database ' e:\hpdb_tmp.dbf '  on Next 20M MaxSize Unlimited;

5. Turn off automatic expansion of temporary table space

Alter Database ' e:\hpdb_tmp.dbf ' off;

6. Add a data file for the temp table space

Alter Add ' e:\2.dbf ' size 100M;

7. Delete a data file in the temp table space

--Delete 2.dbf files
Alterdrop'e:\2.dbf';

8. Delete temporary table space

-- Delete table space without deleting. dbf files Drop  and Cascade constraints;

9. Delete the default temporary tablespace

The default temporary table space cannot be deleted directly, and we can only indirectly delete the default temporary tablespace by following these steps:

1. First, define a new temporary tablespace temp2, so that temp2 as a transit table space;
2. Replace the default tablespace temp1 with Temp2;  alter database default temporary tablespace temp1; )
3. The previous default temporary tablespace Temp1 is then deleted;
4. In defining a new temporary tablespace Temp3, make Temp3 the new default temporary table space;
5. Delete the transit table space Temp2;

After these steps, the default Tablespace Temp1 is replaced with the new tablespace Temp3.

Third, the user's Operation 1, create the user

-- user followed by username -- identified by login password -- default tablespace user defaults table space -- Temporary tablespace User Temp table space
--If you do not specify default tablespace,oracle when creating a user, the system table space will be used as the user default tablespace.
--If you cannot specify temporary tablespace,oracle when you create a user, the database default temporary tablespace will be used as the temporary table space for the user.
Create User  by 123456 DEFAULT temporary tablespace hpdb_tmp;

2. Change the user's default tablespace

Alter User default tablespace newtemp;

3. Change the user's default temporary tablespace

Alter User temporary tablespace Temp3;

4. Modify User Password

Alter User  by 000000

5. Delete users

-- Delete the user and delete the user-created object
--You cannot delete a user who is connecting to dropusersCascade

6. Lock users

Alter User CS account lock;

7. Unlock the Lock

Alter User CS account unlock;

Iv. user rights, role management

Our newly created user does not have any permissions or even the right to log on to the connection data, so we have to assign permissions, or roles, after the user is created.

The user's permissions fall into two categories: System permissions, object permissions

　　System Permissions : Allows the user to perform certain database operations (such as the permissions required to log on to the create session; creating a data table).

　　Object Permissions : Allows a user to perform specific actions (such as SELECT, Delete, update, and so on) on a particular object.

　　roles : To simplify rights management, the concept of roles is introduced, and roles are a combination of a set of permissions with a name.

System pre-defined user roles

CONNECT: When the user (only login permission)
RESOURCE: more reliable and formal users
DBA: The database Administrator role, which has the highest permissions to administer the database, should not be given to the general user.

1. Grant User Roles

-- grant the Connect, resource role to user CS GRANT  to CS;

2. Grant User System privileges

-- Grant user CS permission to log on to the connection database Grant Create  to CS;
--Grant User CS permission to create data tables
Grant Create  to CS;
--Give the user CS permission to use the table space
Grant  ulimited tablespace  to CS;

3. Grant User Object Permissions

-- give the User CS View data table Dept Permissions Grant Select  on  to CS; -- give the User CS View all data table Permissions Grant Select  any Table  to CS;

4. Revoke User Role

 --revoke user CS's Connect, resource role  revoke  Connect,resource from  CS; 
 --revoke permission to create data table for user CS  
 revoke  create  table from  CS; 
 --revoke user CS permissions to use tablespace  
 revoke  ulimited tablespace from  CS; 
 --revoke user CS Dept permissions to view data table  
 revoke  Span style= "COLOR: #0000ff" >select  on dept from  CS; 
 --revoke user CS Permissions to view all data tables  
 revoke  select  any table   from  CS; 

5. Permission Cascade

Grant Select  on  to  with Grant option; -- give the user CS the right to view the data table student, and the user CS can assign that permission to other users

• With GRANT option (Used for object permission authorization, permissions are cascaded) to assign rights to the account to be deleted or permissions revoked, the privileges of the given user will also be revoked
• With admin option (for system permission authorization, permissions are not cascaded) to assign rights to the account to be deleted or permissions to be revoked, permissions granted to the user will not be revoked

Oracle Operations on table spaces, users, user permissions