[Lone sword] Oracle knowledge point sorting (1) Table space, users, and oracle knowledge points

[Lone sword] Oracle knowledge point sorting (1) Table space, users, and oracle knowledge points
Link navigation for this series:

[Lone sword] Oracle knowledge point sorting (1) Table space and users

[Gu JIU Jian] Oracle knowledge point sorting (2) database connection

[Gu JIU Jian] Oracle knowledge point sorting (3) Import and Export

[Dan JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL

[Dan JIU Jian] Oracle knowledge point sorting (5) Table and View of common database objects

[Lone sword] Oracle knowledge point sorting (6) Procedure, function, and Sequence of common database objects

[Dan JIU Jian] Oracle knowledge point sorting (7) database Common Object Cursor

[Lone sword] Oracle knowledge point sorting (8) Common exceptions

[Lone sword] Oracle knowledge point sorting (9) package of common database objects

[Gu JIU Jian] Oracle knowledge point sorting (10) % type and % rowtype and common functions

1. tablespace

The structure of Oracle is partition/segment, tablespace, user, table... (partition is the concept of Oracle storage, and the coding staff do not need to know too much ). After Oracle is installed, a database is created by default. All subsequent operations are performed in this default database.

Note: This is different from SQL Server's database concept. The databases in SQL Server are the most common operations. We are impressed that each database is isolated from each other, after logging on to different users, you can view and operate all the databases. in Oracle, our operations are performed in the same database (database or database instance, users are isolated. After logging on to different users, users can only view their own objects (such as tables, views, processes, and functions). Of course, the premise is that the permission is minimal.

A user has multiple tables, views, Procedure, functions, and packages; users and their objects must be stored in a tablespace. A tablespace can store multiple user information.

1.1. Operate tablespaces

Oracle tablespaces are divided:

• Permanent tablespace (also called data table space) Stores permanent data, such as tables and indexes)
• Temporary tablespace (permanent objects cannot be stored, used to save temporary data generated during database sorting and grouping)
• UNDO tablespace (image before data modification is saved)

1.1.1 operate permanent tablespace

A) Create (one tablespace file)

1 create tablespace tablespaceName 2 logger -- you can record the tablespace creation information to the Oracle log 3 datafile 'tablespacename. dbf' -- specifies the path for saving the file. The tablespace name is used for the file name.
-- (You can set an absolute path. The relative path will be saved to the default directory) 4 size 64 m -- Initial data file size 5 autoextend on -- enable auto-increment 6 next 64 m maxsize 1024 m -- increase 64 m each time, Max. 1024m7 extent management local; -- extent management supports local (local management, default );
-- Dictionary (data dictionary management)

B) Create (multiple tablespace files)

1 create tablespace tablespaceName 2 logger 3 datafile 4 'tablespacename01. dbf 'size 64 m autoextend on next 64 m maxsize unlimited, -- set auto-growth and no upper limit of 5' tablespaceName02. dbf 'size 64 m autoextend on next 64 m maxsize unlimited, 6' tablespaceName03. dbf 'size 64 m autoextend on next 64 m maxsize unlimited7 extent management local;

C) add files to the tablespace.

1 alter tablespace tablespaceName2add datafile 'tablespaceName04.dbf'3     size 64m autoextend on4next 64m maxsize unlimited;

D) delete a tablespace.

1 drop tablespace tablespaceName including contents and datafiles cascade constraints;

1.1.2 create temporary tablespace

A) Create (one tablespace file)

1 create tablespace tablespaceName 2 logger -- you can record the tablespace creation information to the Oracle log 3 tempfile 'tablespacename. dbf' -- specifies the path for saving the file. The tablespace name is used for the file name.
-- (You can set an absolute path. The relative path will be saved to the default directory) 4 size 64 m -- Initial data file size 5 autoextend on -- enable auto-increment 6 next 64 m maxsize 1024 m -- increase 64 m each time, Max. 1024m7 extent management local; -- there are two methods: local (local management, default) and dictionary (data dictionary management)

B) Create (multiple tablespace files)

1 create tablespace tablespaceName 2 logger 3 tempfile 4 'tablespacename01. dbf 'size 64 m autoextend on next 64 m maxsize unlimited, -- set auto-growth and do not set launch 5' tablespaceName02. dbf 'size 64 m autoextend on next 64 m maxsize unlimited, 6' tablespaceName03. dbf 'size 64 m autoextend on next 64 m maxsize unlimited7 extent management local;

C) add files to the tablespace.

1 alter tablespace tablespaceName2 　　add tempfile 'tablespaceName04.dbf' size 64m autoextend on next 64m maxsize unlimited;

D) delete a tablespace.

1 drop tablespace tablespaceName including contents and datafiles cascade constraints;

1.1.3 create an UNDO tablespace

A) Create

1 create undo tablespace tablespaceName datafile 'tablespaceName.dbf' size 64m;

B) modify the default UNDO tablespace of the system (UNDOTBS is used to change the tablespace created by yourself)

1 alter system set undo_tablespace=tablespaceName;

C) delete the UNDO tablespace.

1 drop tablespace "tablespaceName" including contents and datafiles;

1.2 create a user

To create a user, you need to: a) set the user name and password; B) set the data table space; c) set the temporary tablespace; d) Set permissions.

• Create a user and set the user name, password, and tablespace

1 create user userName identified by password2 　　defalut tablespace dataTablespaceName3 　　temporary tablespace tempTablespaceName; -- a、b、c

• Set permissions

1 grant connect,resource,exp_full_database,imp_full_database to userName;    --d

• User modification and Deletion

1 -- Change Password 2 alter user userName identified by password2; 3 4 -- delete a user and its owner object 5 drop user userName cascade;

1.2.1. permissions and Roles

You have set permissions when creating a user. Oracle has multiple built-in roles with different permissions.

A) special permissions of Oralce

B) special roles

C) common user authorization parameters:

1 connect, resource, dba, unlimited tablespace, 2 create session -- create session 3 create any sequence -- create sequence 4 create any table -- create table 5 create any view -- create view 6 create any index -- create index 7 create any procedure -- create stored procedure 8 create any directory -- create directory 9 10 alter session -- modify session 11 alter any sequence -- modify sequence 12 alter any table -- Modify table 13 alter any view -- modify view 14 alter any index -- modify index 15 alter any procedure -- modify stored procedure 16 alter any directory -- modify directory 17 18 drop session -- delete session 19 drop any sequence -- delete sequence 20 drop any table -- delete table 21 drop any view -- delete view 22 drop any index -- delete index 23 drop any procedure -- delete stored procedure 24 drop any directory -- delete directory 25 26 select any table -- Query table 27 select any dictionary -- Query Directory 28 insert any table29 update any table30 delete any table31 debug any procedure -- debug stored procedure 32 debug connect session 33 exp_full_database -- export 34 imp_full_database -- Import

1.3 query user permissions (which requires a dba role) 1.3.1. view the roles owned by the user.

1 select * from dba_role_privs a where a. grantee = 'username'; 2 -- or 3 select * from dba_sys_privs a where a. grantee = 'username ';

1.3.2 view permissions of a role

1 select ROLE, PRIVILEGE from role_sys_privs where role = 'resource'; -- RESOURCE, CONNECT, DBA2 -- or 3 select grantee, privilege from dba_sys_privs where grantee = 'resource ';

1.3.3. Cancel the role for the user

revoke resource from userName;

1.3.4 cancel permissions for users

revoke unlimited tablespace from userName;

1.3.5. append a very useful technique: view the Oracle version number.

select * from v$version where rownum <=1; 

When importing and exporting databases of different versions, you need to mark the version number