Oracle BASICS (iii) database management, oracle Basics

Oracle BASICS (iii) database management, oracle Basics

This article introduces the basic operation commands for Oracle databases: add, delete, modify, and query. The following describes how to manage databases.

Database role Introduction

Data Administrator: at least one Database Administrator dba,

Responsibilities: Install and upgrade the replicel database.

Database creation, tablespace, table, view, Index

Develop and implement backup and recovery plans

Database permission management, tuning, and troubleshooting

For senior DBAs, they are required to participate in project development. They write SQL statements, stored procedures, triggers, rules, constraints, and packages.

Differences between Sys and system

1. The most important thing is that the importance of stored data is different.

Sys: The base tables and views of all oracle data dictionaries are stored in sys users. These base tables and views are crucial for oracle operation and are maintained by the database, no user can change it manually. sys has the role or permission of dba, sysdba, and sysoper, and is the highest user with oracle permissions.

System: the user stores the second-level internal data, such as the Management Information of some features or tools of oracle, and has dba, sysdba role, or System permissions.

2. Second difference

Sys must be logged on in the form of as sysdba or as sysyphon. You cannot log on to the database in the normal mode.

If the System is logged on normally, it is actually a common dba, but if it is logged on as sysdba, the result is actually logged on as a sys user.

Logical backup and recovery of databases

Export tables, export solutions, and export Databases

Use the exp command to complete

Userid: Specifies the username, password, and link string of the operator to be exported.

Tables: export operation table

Owner: Export Operation Method

Full = y: database for Export Operation

Inctype: incremental type of the Export Operation

Rows:

Exp userid = scott/tiger @ myoraltables = (emo, dept) file = d: \ e1.dmp

Export your own solution

Exp scott/tiger @ myoral owner = scottfile = d: \ scott. dmp

Export other solutions

Exp system/manager @ myoralowner = (system, scott) file = d: \ system. dmp

Export Database

Exp userid = system/manager @ myyoral full = yinctype = complete file = x. dmp

Import your own table

Imp userid = scott. tiger @ myortables = (emp) file = d: \ xx. dmp

Import tables to other users

Imp userid = system/manager @ myortables = (emp) file = d: \ xx. demp rows = n

Import Data

Imp userid = scott/tiger @ myor tables = (emp) file = d: \ xx. dmp igore = y

Data Dictionary and dynamic performance View

Db provides some system information of the database, including the data dictionary base table and data dictionary view,

Dynamic Performance view: records information related to the routine after it is started.

Manage tablespaces and data files

SQL> select table_name from user_tables

All tables that can be accessed by All_tables

Dba_tables displays the database tables owned by all solutions

Query a table

Desc dba_users

How many roles are there in the query?

Select * from dba_roles

Queries the roles of a user.

Select * from dba_role_privs wheregrantee = 'username'

Full name of the current database: Select * from global_name

Manage tablespaces and data files

Tablespace: logical component of the database. Physically, database data is stored in data files. Logically, the database is stored in the tablespace, And the tablespace is composed of one or more data files.

The logical structure of a database includes tablespaces, segments, partitions, and blocks.

Tablespace: controls the disk space occupied by the database.

Dba can deploy different data types to different locations, which improves I/O performance and facilitates management operations such as backup and recovery.

Create tablespace

Create Database tablespace

Create tablespace data01 datafile 'd: \ test \ data01.dbf 'size 20 m uniform size 128 k

Offline tablespace: Alter tablespace users offline

Online: Alter tablespace users online

Read-only

Constraints

Including not null, unique, primay key, foreign key, check

Table-Table relationship references

Delete constraint: alter table Name drop primary key cascade.

Maintain data integrity management indexes

The data object used to accelerate data access.

Single-Column index: Create index name on table name (column name)

Composite index: Create index emp_indes on emp (ename, job );

Disadvantages:

1.2 times of hard disk and memory usage

Additional Index Update

Show Index

Select index_name, index_type formuser_indexes where table_name = 'table name'

Manage permissions and Roles

Query permission

Select * from system_privilege_map order byname

Queries the permissions of a role.

Select * from dba_sys_privs wheregrantee = 'dba'

The preceding section describes the duties and permissions of the database administrator and the advantages and disadvantages of adding indexes to the database integrity. Although each developer has a common function, as a database management, the project development management personnel must consider and do a good job.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.