Oracle Database Objects and User Manage www.ncn.cn 1. The management and maintenance of the Oracle database The main content of this section is the management and maintenance of the mode objects of the Oracle database, including: tablespace, table, view , Index, sequence, synonym, aggregation, and integrity constraints. For each mode object, the definition is described, which describes its function, and finally how to manage them in the SQL language-based instance. 1.1 Table spaces Because tablespaces are logical spaces containing these mode objects, it is necessary to maintain it first. Create a table space SQL> CREATE TABLESPACE JXZY> DATAFILE '/USR/OrCle/dbs/jxzy.dbf'> Online; Modify Table Space SQL> ALTER TABLESPACE JXZY OFFLINE NORMAL; SQL> ALTER TABLESPACE JXZY> RENAME DATAFILE '/ USR / ORACLE / DBS / JXZY.DBF '> to' /usr/oracle/dbs/jxzynew.dbf '> Online SQL> CREATE TABLESPACE JXZY Online Delete Table Space SQL> DROP TABLESPACE JXZY> INCLUDING Contents 1. 2 Table Maintenance Table is Data Storage in Database The basic unit, a table contains several columns, each column name, type, length, etc. The establishment of SQL> Create Table Jxzy.Switch (> Office_num Number (3, 0) Not NULL,> Switch_code Number (8, 0) Not Null,> Switch_name Varchar2 (20) Not Null; Table Modify SQL> ALTER TABLE JXZy.Switch> Add (Desc varcha2 (30)); Table Delete SQL> DROP TABLE JXZY.SWITCH> Cascade constraints // Removes the integrity constraint of other tables for this table 1. 3 View maintenance view is one or several The data set generated by the base table, but the view does not account for storage space. Create a view to protect data security (only some of the ranks that you can see), simplify the independence of the query operation and protect the data. SQL view is established> CREATE VIEW jxzy.pole_well_view AS> (SELECT pole_path_num AS path, pole AS device_num FROM pole> UNION> SELECT pipe_path_num AS path,> well AS device_num FROM well); alternatively View SQL> REPLACE VIEW jxzy.pole_well_view AS > (SELECT pole_path_num AS path, pole AS support_device FROM pole> UNION> SELECT pipe_path_num AS path, well AS support_device FROM well); delete view SQL> DROP vIEW jxzy.pole_well_view; 1.4 sequence maintenance sequence is generated by sequence generator only Integer.
Sequence established SQL> CREATE SEQUENCE jxzy.sequence_cable> START WITH 1> INCREMENT BY 1> NO_MAXVALUE; established sequence, jxzy.sequence_cable.currval returns the current value, jxzy.sequence_cable.nextval returns the new value of the current sequence value plus 1 Modify SQL> ALTER SEQUENCE JXZY.SEQUENCE_CABLE> Start with 1 // The starting point cannot be modified, if you modify, you should delete, then redefine> incote 2> maxValue 1000; Sequence Delete SQL> Drop Sequence Jxzy.SEquernce_cable 1. 5 The index maintenance index is a structure related to the table, which is established to improve the retrieval speed of the data. Therefore, in order to increase the index speed on the table, one or more indexes can be created on the table, and an index can be built on one or more columns. For the query type, establish multiple indexes, greatly improve the query speed, the updated table, if the index is too large, will increase the overhead. SQL index points to establish a unique index and a non-unique index Index> CREATE INDEX jxzy.idx_switch> ON switch (switch_name)> TABLESPACE jxzy; index changes SQL> ALTER INDEX jxzy.idx_switch> ON switch (office_num, switch_name)> TABLESPACE jxzy; Index Delete SQL> DROP INDEX JXZY.IDX_SWITCH; 1. 6 Integrity Constraint Management Database Purpose of Integrity Index The correctness and compatibility. Data Type Check Prevents data in the database from doing semantics. Integrity constraints are defined a set of rules description methods for columns.
Oracle provides the following integrity constraints. A. NOT NULL non-empty B. UNIQUE Unique keyword c. Primaty Key Primary key A table can only have one, non-empty D. Foreiga Key foreign key E.Check table Each line of each line of the specified condition Must be true or unknown (for null), for example: a column definition non-empty constraint SQL> ALTER TABLE Office_organization> Modify (Desc varcha2 (20)> constraint nn_desc not null) A list definition unique keyword SQL> ALTER TABLE Office_organization> Modify (office_name VATCHAR2 (20)> cONSTRAINT uq_officename UNIQUE) define the primary key constraint, primary key required non-empty SQL> CREATE TABLE switch (switch_code NUMBER (8)> cONSTRAINT pk_switchcode pRIMARY kEY,) so that the primary key constraint invalid SQL> ALTER TABLE switch DISABLE pRIMARY kEY defined foreign key SQL> CREATE TABLE POLE (pole_code NUMBER (8),> office_num number (3)> CONSTRAINT fk_officenum> REFERENCES office_organization (office_num)> ON DELETE CASCADE); define check SQL> CREATE TABLE office_organization (> office_num NUMBER (3), > Constraint check_officenum> Check (Office_num Between 10 and); II, Oracle Database User and Permissions Management Oracle is a multi-user system, which allows many users to share system resources. To ensure the security of the database system, the database management system is configured with good security Mechanism. 2. 1 Oracle Database Security Policy Establishing System Level Security Guarantee System Level Privileges is achieved by granting user system levels, system-level rights (system privileges) include: establishment of table space, establish users, and modify users , Delete users, etc. System privileges can be granted to users, or it can be recycled at any time. Oracle system privileges have more than 80 kinds. Establish Object-level security guarantee Object-level privileges is achieved by granting users to operate (query, increasing, deleting) rights in the database (query, increasing, deleting). Establishing user-level security assurance user-level security guarantees through user passwords and role mechanisms (a set of rights). The purpose of introducing role mechanism is to simplify authorization and management of users. The practice is to packet the user according to its function, establish a role for each user, and assign the role to the user, and the same role has the same privilege. 2.2 User Management Oracle User Management The content mainly includes the establishment of the user, modify, and delete the user's establishment SQL> CREATE User Jxzy> Identified by jxzy_password> default tablespace system> quata 5m on system; // for users to use the largest spatial limit user Modify SQL> CREATE User Jxzy> Identified by JXZY_PW> Quata 10M on System; Delete Users and Its Object SQL> DROP User Jxzy Cascade; / / Clearing Established Entity 2.3 System Privilege Management and Control Oracle provides more than 80 kinds System privilege, where each system privilege allows the user to perform one or a type of database operation.
