Management and Maintenance of schema objects in Oracle databases

I. Oracle DatabaseManagement and Maintenance of pattern objects
　　
This section focuses on Oracle DatabaseManagement and Maintenance of schema objects. These schema objects include tablespaces, tables, views, indexes, sequences, synonyms, aggregation, and integrity constraints. For each mode object, we first describe its definition, describe its function, and finally describe its function based on SQLLanguage instances describe how to manage and maintain them.
　　
1.1 tablespace
　　
Because the tablespace is the logical space that contains these schema objects, it is necessary to maintain it first.
　　
Create a tablespace
　　 SQL> Create tablespace jxzy
　　
> Datafile '/usr/Oracle/dbs/jxzy. dbf'
　　
> Online;
　　
Modify tablespace
　　 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 a tablespace
　　 SQL> Drop tablespace jxzy
　　
> Including contents
　　
1. 2 Table maintenance
　　
Table is DatabaseThe basic unit of data storage. A table contains several columns, each of which has a column name, type, length, and so on.
　　
Table Creation
　　 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 Modification
　　 SQL> Alter table jxzy. Switch
　　
> Add (DESC varchar2 (30 ));
　　
Table Deletion
　　 SQL> Drop table jxzy. Switch play.bitscn.com tired? Have a try.
　　
> Cascade Constraints
　　
// Delete the integrity constraints of other tables that reference the table
　　
1. 3 view maintenance
　　
A view is a set of data generated by one or more base tables, but does not occupy storage space. Creating a view can protect data security (only allowing users to query and modify rows and columns that can be seen), simplify query operations, and protect data independence.
　　
View Creation
　　 SQL> Create view jxzy. pole_well_view
　　
> (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 );
　　
View replacement
　　 SQL> Replace view jxzy. pole_well_view
　　
> (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 );
View Deletion
　　 SQL> Drop view jxzy. pole_well_view; play.bitscn.com tired?
　　
1.4 sequence Maintenance
　　
A sequence is a unique integer generated by a sequence generator.
　　
Sequence Creation
　　 SQL> Create sequence jxzy. sequence_cable
　　
> Start with 1
　　
> Increment by 1
　　
> No_maxvalue;
　　
After a sequence is created, jxzy. sequence_cable.currval returns the current value. jxzy. sequence_cable.nextval returns the new value after the current value is 1.
　　
Sequence Modification
　　 SQL> Alter sequence jxzy. sequence_cable
　　
> Start with 1 // the start point cannot be modified. If it is modified, it should be deleted first and then redefined.
　　
> Inctement by 2
　　
> Maxvalue 1000;
　　
Sequence Deletion
　　 SQL> Drop sequence jxzy. sequence_cable
　　
1. 5 index Maintenance
　　
An index is a table-related structure. It is created to improve the data retrieval speed. Therefore, to increase the index speed of a table, one or more indexes can be created on the table. One index can be created on one or more columns.
　　
Creating Multiple indexes for a query-type table greatly increases the query speed. If there are too many indexes for the updated table, the overhead will be increased.
　　
Unique and non-unique Indexes
　　
Index creation
　　 SQL> Create index jxzy. idx_switch
　　
> On switch (switch_name)
　　
> Tablespace jxzy;
　　
Index Modification
　　 SQL> Alter index jxzy. idx_switch
　　
> On switch (office_num, switch_name)
　　
> Tablespace jxzy;
　　
Index Deletion
　　 SQL> Drop index jxzy. idx_switch;
　　
1. 6 integrity constraints Management
　　
　　 DatabaseThe correctness and compatibility of data integrity indexes. Data Integrity check prevention DatabaseContains non-semantic data.
　　
Integrity constraints define a set of rules for the table columns. Oracle provides the following integrity constraints.
　　
A. Not null non-empty
　　
B. Unique keyword
　　
C. primaty key one table can only have one primary key, not empty
　　
D. foreiga key foreign key feedom.net China's earliest website administrator
　　
E. Check that each row of the table must have true or unknown conditions (for null values)
　　
For example:
　　
Non-empty constraint defined by a column
　　 SQL> Alter table office_organization
　　
> Modify (DESC varchar2 (20)
　　
> Constraint nn_desc not null)
　　
A column defines unique keywords.
　　 SQL> Alter table office_organization
　　
> Modify (office_name vatchar2 (20)
　　
> Constraint uq_officename unique)
　　
Define primary key constraints. The primary key must not be empty.
　　 SQL> Create Table switch (switch_code number (8)
　　
> Constraint pk_switchcode primary key ,)
　　
Invalid primary key constraint
　　 SQL> Alter table switch disable primary key
　　
Define Foreign keys
　　 SQL> Create Table pole (pole_code number (8 ),
　　
> Office_num number (3) bbs.bitscn.com the earliest Network Management Forum in China
　　
> Constraint fk_officenum
　　
> References office_organization (office_num)
　　
> On Delete cascade );
　　
Definition check
　　 SQL> Create Table office_organization (
　　
> Office_num number (3 ),
　　
> Constraint check_officenum
　　
> Check (office_num between 10 and 99 );
　　
Ii. Oracle DatabaseUser and permission management
　　
Oracle is a multi-user system that allows many users to share system resources. To ensure DatabaseSystem Security, DatabaseThe management system is configured with a sound security mechanism.
　　
2. 1 Oracle DatabaseSecurity Policy
　　
Establish system-level security assurance
System-level privileges are implemented by granting users system-level rights. System-level rights (SYSTEM privileges) include creating tablespaces, creating users, modifying users' rights, and deleting users. System privileges can be granted to users or recycled at any time. The Oracle system has over 80 privileges.
　　
Establish object-level security assurance
Object-level privileges DatabaseSpecific tables, views, sequences, and other operations (query, add, and delete) are implemented.
　　
Establish user-level security assurance
User-level security assurance is implemented through user passwords and role mechanisms (a set of rights. The role mechanism is introduced to simplify user authorization and management. The practice is to group users by their functions, create roles for each user, and assign roles to users. users with the same roles have the same privileges.
　　
2.2 user management
　　
The content of Oracle user management mainly includes User Creation, modification and deletion.
　　
User Creation
　　 SQL> Create user jxzy
　　
> Identified by jxzy_password
　　
> Default tablespace System
　　
> Quata 5 m on system; // maximum space limit for users
　　
User Modification
　　 SQL> Create user jxzy
　　
> Identified by jxzy_pw
　　
> Quata 10 m on system;
　　
Delete users and their created objects
　　 SQL> Drop user jxzy cascade; // Delete the created object blog.bitscn.com.
　　
2.3 system privilege management and control
　　
Oracle provides over 80 SYSTEM privileges, each of which allows users to execute one or more DatabaseOperation.
　　
Grant System privileges
　　 SQL> Grant create user, alter user, drop user
　　
> To jxzy_new
　　
> With admin option;
　　
Revoke SYSTEM privileges
　　 SQL> Revoke create user, alter user, drop user
　　
> From jxzy_new
　　
// But no Cascade recovery function
　　
Displays System privileges granted (SYSTEM privileges of a user)
　　 SQL> Select * From SYS. dba_sys_privs
　　
2.4 Object Privilege Management and Control
　　
Oracle Object Privilege refers to the user's right to perform special operations on the specified table. These special operations include addition, deletion, modification, viewing, execution (stored procedure), reference (other table fields as foreign keys), and indexing.
　　
Grant object privileges
　　 SQL> Grant select, insert (office_num, office_name), feedom.net, the earliest website administrator in China
　　
> Update (DESC) on office_organization
　　
> To new_adminidtrator
　　
> With grant option;
　　
// Cascade authorization
　　
　　 SQL> Grant all on office_organization
　　
> To new_administrator
　　
Revoke object privileges
　　 SQL> Revoke update on office_orgaization
　　
> From new_administrator
　　
// Cascade recovery
　　
　　 SQL> Revoke all on office_organization
　　
> From new_administrator
　　
Show all granted object privileges
　　 SQL> Select * From SYS. dba_tab_privs
　　
2.5 role management
　　
Oracle roles are named privileged groups (including SYSTEM privileges and object privileges)