Database management of ORACLEDBA

Here are some of the things I saved on my wheat library. Learn Oracle's learning notes today take out and share with you, reproduced please indicate the source, the following Oracle version is 10g, with the WinServer2003 operating system, may be some commands and oracle11g different , but most of them are the same, and will continue to share the management of users in Oracle, the management of tables, and the stored procedures and PL/SQL programming in Oracle. The Oracle management tools used are PL/SQL Developerl, and are welcome to criticize them.

1. Responsibilities of the database administrator:

1. Installing and upgrading the Oracle database
2. Create database, table space, table, view, index
3. Develop and implement a backup and recovery database plan
4. Database Rights Management, tuning, troubleshooting
5. For advanced DBAs to participate in project development, write SQL statements, stored procedures, triggers, rules, constraints, packages

2. The main users of the management database are SYS and system: For example: SYS is the Chairman and system is the general manager
1. Two important differences: the importance of storing data is different.
SYS: the base tables and views of all Oracle data dictionaries are stored in the SYS user, and these base tables and views are critical to the operation of Oracle
Maintained by the database itself, no user can change manually, and SYS has the dba,sysdba,sysoper role or permission is the highest user of Oracle permissions.
System: For storing internal data at the sub-level, the system has dba,sysoper roles or permissions
2. A second difference
SYS: must be logged in as SYSDBA or Sysoper and cannot be logged in as normal

3. Modifications to the initialization parameters :
1. Display initialization parameters
Show parameter

2. How to modify Parameters
Can be modified under the file Oracle\admin\orcl\pfile\init.ora;

4. Logical backup of the database:
A logical backup is the process of exporting the structure and data of a data object to a file using the tools export.
A logical reply is the process of importing a backed-up data into a database using a tool import after a database has been mistakenly manipulated.
Physical backups can be operated under the database open or under shutdown.
However, a logical backup can only operate under the open database.

Export of data:!!! --Execute commands in the bin directory of the Oracle directory when importing and exporting;
Export specific: Export table, export scheme, export database
Export is implemented with the EXP command, which is commonly used in the following options:
UserID: Used to specify the user name, password, and connection string to perform the export operation;
Tables: The 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: The increment type used to specify the export operation;
Rows: Whether the export operation exports the data in the table;
File: Used to specify the export file name;

1). Export table: Export the EMP table in DB instance ORCL under Scott User, the file path is e:/emp.dmp;
Execute under CMD's console:

 and Settings\administrator.web-a93b1e61669>exp userid=Scott/Tiger @orcl Tables=file=e:\emp.dmp;

2) export tables for other scenarios
DBA authority or EXP_FULL_DATABASE permission is required if you want to export tables for other scenarios

SQL>userid=system/system@orcl Tables=file =e:\emp1.dmp;

3) The structure of the exported table does not have its data

exp UserID=Scott/Tiger@orcl Tables=file=e:\ Emp.dmp rows=n;

4) Direct export table structure (suitable for a large number of data in the table, faster than Rows=n)

exp UserID=Scott/Tiger@orcl Tables=file= e:\emp2.dmp Direct=y;

2. Export Scenario:
1) Export Scott's scheme: Exp scott/[email protected] Owner=scott file=e:\scott.dmp;

 and Settings\administrator.web-a93b1e61669>exp userid=Scott/Tiger @orcl owner=file=e:\scott.dmp

2). Export a different scenario: if you want to export a table for another scenario that requires DBA authority or Exp_full_database permissions

exp UserID=system/system@orcl owner=file=e:\ syscott.dmp; Export database inctype=complete; Incremental backup- - only the new database is added at the time of the second backup exp userid =System/system@orclFull=y inctype=file  =e:\orcl.dmp;

5. Import of the database: Import imports the data from the file into the database, but the imported data file must be a file exported with the tool export
Import also import tables, import scenarios, import databases three ways
The commands commonly used by IMP are:
UserID: The user name, password, and connection string used to specify the import operation;
Tables: Used to perform import table operations
Fromuser: Used to specify the source user
Touser: Used to specify the target user
File: To specify the import file name
Full=y: Used to specify that the entire file is imported
Inctype: The increment type used to specify the import file
Rows: Specify whether to import table rows (data)
Ignore: Import only table data if the table exists

--Deleting a table is the same as deleting the previously created savepoint, so deleting the table is not rollback
1. Import the table under the user:

IMP userid=Scott/Tiger@orcl Tables=file=e:\orcl.dmp;

2. Import the table to other users who require DBA authority or imp_full_database permission;

IMP userid=system/system@orcl Tables=file=e:\orcl.dmp Touser=Scott;

3. Import only table structures without importing data

IMP userid=Scott/Tiger@orcl Tables=file=e:\orcl.dmp Rows=n;

4. Import data only if the table already exists

IMP userid=Scott/Tiger@orcl Tables=file=e:\orcl.dmp Ignore =y;

5. Import your own scenario

IMP userid=Scott/Tiger@orclfile=e:\scott.dmp;

6. Import other Scenarios

IMP userid=system/system@orclfile=e:\scott.dmp fromuser= System Touser=Scott;

7. Import the Database

IMP userid=system/system@orclFull=file=E : \orcl.dmp;

6. Data dictionary:
The data dictionary records the system Information of the database, which is a collection of read-only tables and views, and the owner of the data dictionary is the SYS user
The user can only perform query operations (select operations) on the table, and its maintenance and modifications are automatically completed by the system
The data dictionary includes the data base table and the Data view, the data base tables store the database basic information, the ordinary user cannot directly access the database base table information, the data dictionary view is based on
The virtual table created by the base table, the user can query the information in the view of the system information
The views in the data dictionary mainly include the user_xxx,all_xxx and the dba_xxx three kinds;

1.user_tables Displays the tables owned by the current user

SQL>Select from User_tables;

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

SQL>Select from All_tables;

3.dba_tables shows the tables owned by all scenarios, requiring the user to have a DBA role or have select_any_tables permissions, such as system users.
　　　

SQL>Select from Dba_tables;

4. Query Dba_users to display all users (views) in the database

SQL>Select from Dba_users;

5. Query the Dba_sys_privs view to query the system permissions that the user has

SQL>Select*fromwhere grantee='SYS  '

6. Query the Dba_tab_privs view to query the object permissions that the user has

SQL>Select*fromwhere grantee='SCOTT ';

7. Query the Dba_col-privs view to query the column permissions that the user has
8. Query the Dba_role_privs view to query the user's role;

SQL>Select*fromwhere grantee='SCOTT ' ; GRANTEE granted_role admin_option default_role---------------------------------------------- --------------------------------------SCOTT RESOURCE NO YES

9. Querying all roles in Oracle

SQL>Select* from Dba_roles;

10. Querying system permissions

SQL>Select* from System_privilege_map;

　　

11. Querying Object Permissions

SQL>Selectdistinct from Dba_tab_privs;

12. Query role-owned system permissions

SQL>Select*fromwhere grantee='  CONNECT';

13. Querying the object permissions owned by a role

 sql>  select  *  from  dba_tab_privs where  Grantee=   "  ;