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.
  
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)
Related Article

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.