Oracle 11g Study Notes 10_30

Data Administrator
Each Oracle database should have at least one Database Administrator (DBA). For a small database, one DBA is enough, but for a large database, multiple DBAs may be required to assume different management responsibilities.
* Administrator's main work:
1) install and upgrade the Oracle database
2) database creation, tablespace, table, view, index ....
3) Develop and implement backup and recovery plans
4) database permission management, tuning, and troubleshooting
5) for senior DBAs, they must be able to participate in project development and write SQL statements, stored procedures, triggers, planning, constraints, and packages.

1. Database Administrator

* The relationship between the database administrator system and system is as follows: SYS is the chairman of the board and system is the general manager. Both databases have considerable permissions.
Their main differences are:
1) The most important difference 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 users have the DBA, sysdba, and sysoper roles or permissions, and are the users with the highest Oracle permissions.
System:It is used to store level-1 Internal data, such as the Management Information of some features or tools of oracle. System users have dBA, sysdba role, or system permissions.
2) The second difference is that permissions are different.
Sys userYou must Log On As sysdba or as sysoper,It cannot be in the normal mode.Log on to the database.
SystemIf you log on normally, it is actually a common DBA user. However, if you log on as sysdba, it is actually logged on as a sys user. We can see from the logon information.
* Users with DBA permissions,
A dba user is a database user with a DBA role. Privileged users can perform special operations such as starting an instance and shutting down an instance. However, a DBA user can perform various management tasks only after starting the database.
Ii. Logical backup and recovery of databases (tables)

Logical backupIt refers to the process of exporting the structure and data of the data object to a file using the export tool. Logical recovery refers to the process of using the import tool to import data objects to the database when database objects are damaged due to misoperation. Physical backup can be performed either in the open or closed database state, but logical backup and recovery can only be performed in the open state.
1. Export
Export is dividedExport tables, export solutions, and export Databases..
Export is completed using the exp command. Common options of this command are:
Userid: Specifies the username, password, and connection string used to execute the export operation.
Tables: used to specify the table for the Export Operation
Owner: used to specify the scheme for executing the Export Operation
Full = Y: Specifies the database for the export operation.
Inctype: Specifies the incremental type of the export operation.
Rows: used to specify whether to export data from the table during the export operation.
File: Specifies the exported file name.

* Export a table
(1) Export your own table

exp userid=scott/scott@orcl tables=(emp,dept) file=e:\oracle\scott.dnp

Here, the orcl of '@ orcl' is the Instance name. Multiple exported tables are separated by commas. If there is a semicolon, the suffix of the exported table will appear.
(2) Export tables of other solutions
If you want to export tables of other schemes, you need DBA permission or exp. Full. Database permission. For example, system can export Scott tables.

exp userid=system/TGXgt1314@orcl tables=(scott.emp) file=e:\oracle\scott_emp.dnp

Here, tables specifies the table to be exported.
Tip:
If the import and export commands are executed in PL/SQL developer, an error message "invalid SQL statement" appears. To successfully execute this statement, go to the bin directory under the Oracle installation directory.
Directory. You can use the command line to locate the Directory and then execute the SQL statement.

* Export the table structure

exp userid=scott/scott@orcl tables=(emp,dept) file=e:\oracle\scott2.dnp rows=n

* Use direct export

exp userid=scott/scott@orcl tables=(emp,dept) file=e:\oracle\scott3.dnp direct=y

This method is faster than the conventional method by default. When the data volume is large, you can consider using this method.
In this case, the character set of the database must be exactly the same as that of the client. Otherwise, an error is returned.

* Export Scheme
An export scheme is to use the export tool to export all objects (tables, indexes, constraints, etc.) and data in one or more schemes and store them in one file.
1) Export your own solution

exp userid=scott/scott@arcl owner=scott file=e:\oracle\scott_fangan.dnp

2) Export other user Solutions
If you want to export other schemes, you need DBA or exp. Full. Database permissions. For example, system can export Scott's scheme.

exp userid=system/TGXgt1314@orcl owner=scott file=e:\oracle\scott_fangan2.dnp

* Export a database
Exporting a database is to use export to export objects and data in all databases.
DBA permission or exp. Full. Database permission is required

exp userid=system/TGXgt1314@orcl full=y inctype=complete file=e:\oracle\database.dnp

Among them, full = y inctype = complete is Incremental backup, the advantage: the second backup only requires the backup to change, the speed is faster.

2. Import
Import is to use the tool import to import objects and data in the file into the database,The file to be imported must be the file exported by the export.,The import can also be divided into three types: Import table, import solution, and import database.
Common options of IMP include:
Userid: Specifies the user name, password, and connection string used to perform the import operation.
Tables: used to specify the table for the Import Operation
Formuser: used to specify the source user
Touser: used to specify the target user
Full = Y: used to import the entire file
Inctype: Specifies the incremental type of the import operation.
Rows: used to specify the rows (data) for table import)
File: Specifies the import file name.
Ignore: If a table exists, only data is imported.

1) import your own table

imp userid=scott/scott@orcl tables=(emp,dept) file=e:\oracle\scott.dnp

2) import the table to other users
This user is required to have DBA permissions or imp. Full. Database

imp userid=system/TGXgt1314 tables(emp,dept) file=e:\oracle\scott.dnp touser=scott

'Touser = Scott 'indicates that the data is imported to the Scott user.
3) Structure of the import table
Only import the table structure but not the data

imp userid=scott/scott@orcl tables=(emp,dept) file=e:\oracle\scott.dnp rows=n

4) import data

If an object (such as a table) already exists, you can only import table data.

imp userid=scott/scott@orcl tables=(emp,dept) file=e:\oracle\scott.dnp ignore=y