Management and Maintenance of schema objects in Oracle databases
Source: Internet
Author: User
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.
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)
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)
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.