Shulou(Shulou.com)06/01 Report--

The following things are stored in my wheat bank when learning Oracle learning notes to share with you today, reprint please indicate the source, the following version of Oracle is 10g, when using the operating system of Oracle11g 2003, some commands may be different from those of Oracle11g, but most of them are the same, and then I will gradually share the management of users and tables in Oracle, as well as stored procedures and PL/SQL programming in WinServer. The Oracle management tools used are PL/SQL Developerl and SQL PLUS. You are welcome to criticize and correct them.

1. Responsibilities of the database administrator:

1. Install and upgrade Oracle database

two。 Create databases, tablespaces, tables, views, indexes

3. Develop and implement a database backup and recovery plan

4. Database rights management, tuning, troubleshooting

5. For advanced dba can participate in project development, can write sql statements, stored procedures, triggers, rules, constraints, packages

two。 The main users who manage the database are sys and system: for example, sys is the chairman and system is the general manager.

1. The important difference between the two is that the importance of storing data is different.

Sys: the base tables and views of all oracle data dictionaries are stored in sys users, which are critical to the operation of oracle

It is maintained by the database itself and cannot be changed manually by any user. Sys has the dba,sysdba,sysoper role or the highest user with oracle privileges.

System: used to store secondary internal data. System has dba,sysoper roles or permissions.

two。 The second difference

Sys: you must log in as sysdba or sysoper, not as normal

3. Modification of initialization parameters:

1. Show initialization parameters

Show parameter

two。 How to modify parameters

You can modify it in the file oracle\ admin\ orcl\ pfile\ init.ora

4. Logical backup of the database:

Logical backup refers to the process of exporting the structure and data of a data object to a file using the tool export.

Logical reply refers to the process of importing the backed-up data into the database by using the tool import after the database has been misoperated.

Physical backup can be operated under either database open or shutdown.

However, logical backups can only be operated under the open of the database.

Data export:!!-- execute commands in the bin directory of the oracle directory when importing and exporting

Export is divided into: export table, export scheme, export database

Export is implemented with the exp command, which has the following common options:

Userid: used to specify the user name, password, and connection string to perform the export operation

Tables: table used to specify the export operation

Owner: the scheme used to specify the export operation

Full=y: used to specify the exported database

Inctype: used to specify the increment type of the export operation

Rows: whether to perform an export operation to export the data in the table

File: used to specify the export file name

1)。 Export table: export the table of emp in the database instance orcl under scott user, and the file path is e:/emp.dmp

Execute under the console of cmd:

C:\ Documents and Settings\ Administrator.WEB-A93B1E61669 > exp userid=scott/tiger@orcl tables= (emp,dept) file=e:\ emp.dmp

2) Export tables for other scenarios

Dba or exp_full_database permissions are required if you want to export tables for other scenarios

SQL > userid=system/system@orcl tables= (scott.emp) file=e:\ emp1.dmp

3) the structure of the export table has no data

Exp userid=scott/tiger@orcl tables= (emp,dept) file=e:\ emp.dmp rows=n

4) directly export the table structure (there is a lot of data in the fit table, which is much faster than rows=n)

Exp userid=scott/tiger@orcl tables= (emp.dept) file=e:\ emp2.dmp direct=y

two。 Export scheme:

1) Export scott scheme: exp scott/tiger@orcl owner=scott file=e:\ scott.dmp

C:\ Documents and Settings\ Administrator.WEB-A93B1E61669 > exp userid=scott/tiger@orcl owner=scott file=e:\ scott.dmp

2)。 Export other scenarios: dba or exp_full_database permissions are required if you want to export tables for other scenarios

Exp userid=system/system@orcl owner= (system,scott) file=e:\ syscott.dmp; exports database inctype=complete; incremental backup-only the new database exp userid=system/system@orcl full=y inctype=complete file=e:\ orcl.dmp will be added on the second backup

5. Database import: import imports the data from the file into the database, but the imported data file must be exported with the tool export

Import is also divided into three ways: import table, import scheme, and import database.

The commands commonly used in imp are:

Userid: used to specify the user name, password, and connection string of the import operation

Tables: used to perform import table operations

Fromuser: used to specify source user

Touser: used to specify the target user

File: used to specify the import file name

Full=y: used to specify that the entire file be imported

Inctype: used to specify the incremental type of the imported file

Rows: specify whether to import table rows (data)

Ignore: import only table data if the table exists

Deleting a table will also delete the previously created savepoint, so deleting a table cannot be rollback.

1. Import the table under the user:

Imp userid=scott/tiger@orcl tables= (emp) file=e:\ orcl.dmp

two。 Import tables to other users that require DBA or imp_full_database permissions

Imp userid=system/system@orcl tables= (emp) file=e:\ orcl.dmp touser=scott

3. Import only table structures, not data

Imp userid=scott/tiger@orcl tables= (emp) file=e:\ orcl.dmp rows=n

4. Import only data if the table already exists

Imp userid=scott/tiger@orcl tables= (emp) file=e:\ orcl.dmp ignore=y

5. Import your own scheme

Imp userid=scott/tiger@orcl file=e:\ scott.dmp

6. Import other scenarios

Imp userid=system/system@orcl file=e:\ scott.dmp fromuser=system touser=scott

7. Import database

Imp userid=system/system@orcl full=y file=e:\ orcl.dmp

6. Data dictionary:

The data dictionary records the system information of the database. It is a collection of read-only tables and views. The owner of the data dictionary is the sys user.

Users can only perform query operations (select operations) on the table, and their maintenance and modification are done automatically by the system.

The data dictionary includes the data base table and the data view. The basic information of the database is stored in the data base table. Ordinary users cannot directly access the information of the database base table. The view in the data dictionary is based on

For a virtual table created by a basic table, users can query the information of the system by querying the information in the view.

The views in the data dictionary mainly include three types: user_XXX,all_XXX and dba_XXX.

1.user_tables displays tables owned by the current user

Sql > select table_name from user_tables

2.all_tables displays all the tables that the current user can access, not only under this scenario, but also under other scenarios

Sql > select table_name from all_tables

3.dba_tables displays the tables owned by all scenarios, requiring that the user must have the dba role or select_any_tables permissions such as the system user.

Sql > select owner,table_name from dba_tables

4. Display all users (views) in the database by querying dba_users

Sql > select username from dba_users

5. You can query the system permissions of a user by querying the dba_sys_privs view

SQL > select * from dba_sys_privs where grantee='SYS'

6. You can query the object permissions that a user has by querying the dba_tab_privs view

SQL > select * from dba_tab_privs where grantee='SCOTT'

7. You can query the column permissions of a user by querying the dba_col-Privs view

8. You can query the role of a user by querying the dba_role_privs view

SQL > select * from dba_role_privs where grantee='SCOTT';GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE-- SCOTT RESOURCE NO YES

9. Query all roles in Oracle

SQL > select * from dba_roles

10. Query system permissions

SQL > select * from system_privilege_map

11. Query object permissions

SQL > select distinct privilege from dba_tab_privs

twelve。 Query the system permissions owned by the role

SQL > select * from dba_sys_privs where grantee='CONNECT'

13. Query the object permissions that the role has

SQL > select * from dba_tab_privs where grantee='CONNECT'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.