[Oracle] tablespaces, users, permissions, roles, auditing

Oracle is hard to understand. One or two hours a day, persevere! ---Structural system and principle

Dba_data_files： Data file Dba_tablespaces: Tablespace Dba_segments: Segment information Dba_extents: Block information Dba_free_space: Tablespace availability information Dba_objects: Object information V$datafile: Information from control files User rights and system rights view: View user roles: select * from dba_role_privs;select * from user_role_privs; View all roles, system rights, object rights: select * from dba_roles;select * from system_privilege_map;select * from v$object_privilege; View user system rights: select * from dba_sys_privs;select * from user_sys_privs; View user object rights: select * from dba_tab_privs where grant ='B ';select * from all_tab_privs;select * from user_tab_privs; system permissions, roles, object permissions Grant and revoke Syntax: Grant system permissions| roles to user;Grant object permissions on objects (tables, etc.) to user; Revoke system permissions| roles from user;Revoke object permissions on objects (tables, etc.) from user;

Tablespace à segment à extent à block

| | |

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

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

Tablespace: can contain multiple data files

Segment: can be thought of as a table, containing multiple sections

A group of consecutively numbered oracle blocks in a data file.

Block: Contains one or more os blocks, a block is the basic I/O unit of an oracle database

Data file: consists of multiple os blocks

Operating system block: I/O unit of file system

show parameter db_block_size: View the size of the oracle block, the default is 8192 bytes

syntax: use em to display sql

Tablespace creation: CREATE SMALLFILE TABLESPACE"HXW_DATA" DATAFILE'/ns_data/oracle/oradata/nsdc/hxw_data01.dbf'SIZE 1M AUTOEXTENDONNEXT 1M MAXSIZE 10M --Default does not automatically expand LOGGING --Tablespace generates redo content for all operations on segments, default EXTENTMANAGEMENTLOCAL --Zone auto-management, default SEGMENTSPACEMANAGEMENT--Segment uses bitmap to track block usage, default DEFAULTNOCOMPRESS --No compression, default adds data files: ALTERTABLESPACE"HXW_DATA" ADDDATAFILE'/ns_data/oracle/oradata/nsdc/hxw_data02.dbf'SIZE 10M AUTOEXTENDONNEXT 1M MAXSIZE 20M --Set extent to manually specified size. CREATE SMALLFILE TABLESPACE"HXW_DATA" DATAFILE'/ns_data/oracle/oradata/nsdc/hxw_data01.dbf'SIZE 1M AUTOEXTENDONNEXT 1M MAXSIZE 10M LOGGINGEXTENTMANAGEMENTLOCALUNIFORMSIZE 512 KSEGGMENTSPACEMANAGEMENT AUTODEFAULTNOCOMPRESS ALLOCATION_TYPE This value has 3 options: 1. system: Once this value is set, next_extent will be empty and only the values of extents will be available. This value is the default. The minimum value for this option is 64K2, user: Once this value is set, it allows us to control the next_extent. There are only two types of users: One is that the ts is managed by the data dictionary; the other is that the ts is transferred from the data dictionary management to the local (dbms_space_admin.tablespace_migrate_to_local) 3. uniform: will indicate that the size of all extents will be the same, temp table space can only be used in this way; the size of the extents in the above two cases will be inconsistent; Default value in uniform is 1M Temporary table empty CREATE SMALLFILE TEMPORYTABLESPACE "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 table space: CREATE SMALLFILE UNDOTABLESPACE"UNDOTBS02" DATAFILE'/ns_data/oracle/oradata/nsdc/undotbs02.dbf'SIZE 5M AUTOEXTENDONNEXT 1M MAXSIZE 10M RETENTION NOGUARANTEE --Restore retention time not guaranteed, GUARANTEE guaranteed

--Create Tablespaces and Users

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;

Attachment: down.51cto.com/data/2364873