Use sqlplus to create a tablespace

1. Open the command line window and enter the following command: sqlplus/nolog and press Enter. The prompt SQL>,
Enter Conn/As sysdba to log on. If it fails, try again with the password of the conn sys/sys user as sysdba.

2. Check where the current database files are generally stored: input: Select name from V $ datafile;
Name
--------------------------------------------------------------------------------
D: \ oracle \ oradata \ orcl \ system01.dbf

D: \ oracle \ oradata \ orcl \ undotbs01.dbf

D: \ oracle \ oradata \ orcl \ cwmlite01.dbf

D: \ oracle \ oradata \ orcl \ drsys01.dbf

D: \ oracle \ oradata \ orcl \ indx01.dbf

D: \ oracle \ oradata \ orcl \ tools01.dbf

Your data files are stored in the Directory D: \ oracle \/oradata \ orcl \.

3. Create a tablespace in the database. The command format is as follows:
The 'size of the file name corresponding to the create tablespace name datafile;
Example:
Create tablespace wbppcs datafile 'd: \ oracle \ oradata \ orcl \ wbppcs. dbf'size 3000 m;
3000 m indicates MB

4. Create a user. The command format is as follows:
Create User Username identified by password default tablespace which tablespace is used by the user by default;
Modify user permissions:
Grant role 1, role 2 to user name;
Example:
Create user wbppcs identified by wbppcs123 default tablespace wbppcs;
Grant dBA, connect to wbppcs;

5. Grant Permissions
1. The default normal user Scott is not unlocked by default and cannot be used. The new user does not have any permissions and must be granted permissions.
/* Administrator authorization */
Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, the login permission.
Grant unlimited session to zhangsan; // grant the zhangsan user the permission to use the tablespace.
Grant create table to zhangsan; // grant the table creation permission
Grante drop table to zhangsan; // grant the table deletion permission
Grant Insert table to zhangsan; // insert table permission
Grant update table to zhangsan; // Modify Table Permissions
Grant all to public; // This is important. Grant all permissions to all users (public)
2. oralce is more rigorous in permission management. Common users cannot access each other by default and must be authorized to each other.
/* Oralce strictly manages permissions, and common users cannot access each other by default */
Grant select on tablename to zhangsan; // grant the zhangsan user the permission to view the specified table.
Grant drop on tablename to zhangsan; // grant the table deletion permission.
Grant insert on tablename to zhangsan; // grant the insert permission
Grant update on tablename to zhangsan; // grant the table modification permission.
Grant insert (ID) on tablename to zhangsan;
Grant Update (ID) on tablename to zhangsan; // grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.
Grant alert all table to zhangsan; // grant permissions to any table of the zhangsan user alert
5. revoke permissions
The basic syntax is the same as grant. The keyword is revoke.
6. View Permissions
Select * From user_sys_privs; // view all permissions of the current user
Select * From user_tab_privs; // view the table permissions of the users used
VII. User table of the operation table
/* Add the user name before the table name, as shown below */
Select * From zhangsan. tablename
8. Permission Transfer
That is, user a grants permissions to user B, and user B can grant operation permissions to user C. The command is as follows:
Grant alert table on tablename to zhangsan with admin option; // keyword with admin Option
Grant alert table on tablename to zhangsan with grant option; // The keyword with grant option is similar to admin.
9. Roles
A role is a set of permissions. You can assign a role to a user.
Create role myrole; // create a role
Grant create session to myrole; // grant the create session permission to myrole
Grant myrole to zhangsan; // grant the role of myrole to the zhangsan user
Drop role myrole; delete a role
/* However, some permissions cannot be granted to the role, such as the unlimited tablespace and any keywords */