An example of Oracle's complete creation of tablespace

Daoteng Oracle for a while. Because the project often need to use, some basic function or need to grasp, convenient for peacetime project docking and maintenance, truth-seeking, speak, today record an Oracle 11g complete creation of tablespace example

1. Login

Su-oracel

Sqlplus/as SYSDBA

2. Create a user

1. Create user TestUser identified by testuser123;

3. Change the password

Alter user testuser identified by testuser123456;

4. View the table space where the user resides

1. Select username,default_tablespace from Dba_users;

Set Linesize 300;

Set PageSize 30;

Col USERNAME format A20;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/8B/B3/wKioL1hWWVby8YjBAACudrfMAmU626.png "title=" Oracle-user1.png "alt=" Wkiol1hwwvby8yjbaacudrfmamu626.png "/>

5. Create a new table space

Select file_name, tablespace_name from Dba_data_files; To view the table space's directory location

2. Create tablespace testuser logging datafile '/opt/oracle/oradata/orcl/orcl_data.dbf' size 50m autoextend on Next 50m maxsize 2048m extent management local;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/8B/B7/wKiom1hWWXrwet2qAACwBMK5THA801.png "style=" float: none; "title=" Create-datafile-3.png "alt=" Wkiom1hwwxrwet2qaacwbmk5tha801.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/8B/B3/wKioL1hWWXvRlEK8AAB0b3bbEd4042.png "style=" float: none; "title=" Oracle-chakan.png "alt=" Wkiol1hwwxvrlek8aab0b3bbed4042.png "/>

6. Assign a newly created tablespace to a user

1. Alter User testuser default tablespace testuser;

2. 
   

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/B7/wKiom1hWWaij05JYAACDI5qRZlA196.png "title=" 4- Alter-tablespace.png "style=" Float:none; "alt=" Wkiom1hwwaij05jyaacdi5qrzla196.png "/>

7. Assigning Login rights to users

Grant Create session, create table,create view,create sequence , unlimited tablespace to TestUser;

SELECT * from All_users;

SELECT * from Dba_sys_privs;

SELECT * from Dba_sys_privs WHERE GRANTEE = ' TESTUSER '; # # #查看该用户具有哪些权限

8. New User Login Test

1. Conn testuser/testuser123456

2. Select * from Session_privs; # # #查看当前用户的权限

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/8B/B7/wKiom1hWWanBi3QBAAA4zfQLxTQ875.png "title=" 5-- Connect. png "style=" Float:none; "alt=" Wkiom1hwwanbi3qbaaa4zfqlxtq875.png "/>

9. Create a table

CREATE TABLE testnew_ime (ID int primary key,name VARCHAR2 (Ten)) segment creation immediate;

Select table_name from User_tables where rownum<=3; # # #查看该用户具有哪些表

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/8B/B3/wKioL1hWWanzzRQGAAETxAg4Yrc345.png "title=" 6-- Sqldeveloper.png "style=" Float:none; "alt=" Wkiol1hwwanzzrqgaaetxag4yrc345.png "/>

9. Delete the created user and related objects

1. Drop User testuser Cascade;

This article is from "Good Sir 2020" blog, please make sure to keep this source http://fuyuan2016.blog.51cto.com/8678344/1883776

An example of Oracle's complete creation of tablespace