Oracle tablespaces, users, permissions, roles, audits

Find Oracle difficult to drill down. One or two hours a day, stick to it! ---structure system and principle

Dba_data_files: Data file dba_tablespaces: Tablespace dba_segments: Segment Information dba_extents: Block information dba_free_space: Table space available information dba_objects: Object information v$ DataFile: Information from the control file user rights and System permissions view: View user roles: SELECT * from Dba_role_privs;select * from User_role_privs; View all roles, system permissions, Object permissions: SELECT * FROM Dba_roles;select * from System_privilege_map;select * from V$object_privilege; View User system permissions: SELECT * FROM DB A_sys_privs;select * from User_sys_privs; View User object permissions: SELECT * from Dba_tab_privs where grantee= ' B '; select * from All_tab_pri Vs;select * from User_tab_privs; system permissions, roles, object permissions authorization and Revocation syntax: Grant system Permissions | role to User; Grant object permission on object (table, etc.) to user; Revoke System Permissions | role from user; Revoke Object permissions on objects (tables, etc.) from the user;

Tablespace à segment à extent à block

|             | |

|----------------------------------------------à datafile |

| ------------|---à osblock

Tablespace: Can contain multiple data files

Segment: Can be considered a table, containing multiple extents

Zone: A set of contiguous numbered Oracle blocks in a data file

BLOCK: Contains one or more OS blocks, which are basic I/O units of the Oracle database

data file: consists of multiple OS blocks

Operating system block: File system I/O unit

Show parameter db_block_size : View The size of the Oracle block, default is 8192byte

Syntax: Using EM to display SQL

Table Space creation: Create smallfile tablespace "Hxw_data"  datafile '/ns_data/oracle/oradata/nsdc/hxw_ DATA01.DBF ' size 1m autoextendonnext 1m maxsize 10m  --does not automatically expand by default logging  --all the actions of the tablespace for the segment will generate redo content, the default extentmanagementlocal --area is automatically managed, the default segmentspacemanagement auto--segment uses the bitmap tracking block usage, Default defaultnocompress  --not compressed, default   Add data file: Altertablespace "Hxw_data"  adddatafile '/ns_data/oracle /ORADATA/NSDC/HXW_DATA02.DBF ' size 10m autoextendonnext 1m maxsize 20m  -- Set extent to manually specify the size. Create smallfile tablespace "Hxw_data"  datafile '/ns_data/oracle/oradata/nsdc/hxw_data01.dbf ' SIZE  1m autoextendonnext 1m maxsize 10m loggingextentmanagementlocaluniformsize  512KSEGMENTSPACEMANAGEMENT AUTODEFAULTNOCOMPRESS ALLOCATION_TYPE  This value has 3 options: 1, System: Once this value is set , Next_extent will be empty with only extents values. The value is the default value. The minimum of this option is 64K2, User: Once this value is set, it allows us to control the next_extent. There are only two cases where the users: one is the data dictionary management, the other is the TS is from the data dictionaryManagement transfer to local (with dbms_space_admin.tablespace_migrate_to_local) 3, uniform: will indicate that all extent will be the same size, the temp table space can only be used this way ; The size of the extent of the above two cases will be inconsistent; The default value in uniform is 1m   temporary table empty Create smallfile temporarytablespace "Hxw_tmp"  tempfile '/ns_data/oracle/oradata/nsdc/hxw_tmp01.dbf ' size 5m autoextendonnext 1m  maxsize 10m extentmanagementlocaluniformsize 1m  Add data file: Altertablespace "HXW_TMP"   Addtempfile '/ns_data/oracle/oradata/nsdc/hxw_tmp02.dbf ' size 100m autoextendonnext 1m  Maxsizeunlimited  undo tablespace: Create smallfile undotablespace "UNDOTBS02"  datafile '/ns_ DATA/ORACLE/ORADATA/NSDC/UNDOTBS02.DBF ' size 5m autoextendonnext 1m maxsize 10m  retention noguarantee --Restore retention time is not guaranteed, guarantee guarantee

-- CREATE table space and user

Create Tablespace H_test

DataFile '/ns_data/oracle/oradata/nsdc/t_test01.dbf '

Size 2m autoextend on next 1m maxsize 5m;

Create user Huangxw identified by Huangxwdefault tablespace H_test;

This article from "Despite the wrong, let me wrong to die!" "Blog, be sure to keep this provenance http://hxw168.blog.51cto.com/8718136/1554423

Oracle tablespaces, users, permissions, roles, audits