Also learn about Oracle

Oracle system functions
Overview:Sys_context ('userenv', 'param') function: this function can be used to query important information.For example, which database are you using?
Note: 1)Host: Returns the name of the host where the database is located.
2)Lanuage: Language
3)Db_name: Current Database Name
4)Terminal: Terminal identifier corresponding to the current session customer
5)Session_user: Database username corresponding to the current session customer
6)Current_schema: Default solution name for the current session customer
7)Nls_date_format: Date Format corresponding to the current session customer

Select sys_context ('userenv', 'db _ name') from dual; -- query the currently used database name select sys_context ('userenv', 'session _ user') from dual; -- query the current database operation user select sys_context ('userenv', 'current _ scheme') from dual; -- query the current database solution

Dynamic Performance View
Used to record the activity information of the current routine. Its owner is sys. Generally, DBA or privileged users can query the dynamic performance view.
When ORACLE Server is started, the system creates a dynamic performance view. When ORACLE Server is stopped, the system deletes the dynamic performance view.
All the dynamic performance views of Oracle start with V _ $, and Oracle provides synonyms starting with V $ for each dynamic performance view.
For example, the synonym for V _ $ datafile is V $ datafile.

Data Dictionary
Overview: This is the most important component of the Oracle database. It records the system information of the database. It is a collection of read-only tables and views. Its owner is a sys user.
The system information can be obtained through data dictionary query: object definition, space occupied by objects, column information, and constraint information ......
The user can only perform the Select Operation on the data dictionary, and the maintenance and modification are automatically completed by the system.
Category: it includes the data dictionary base table and the data dictionary dynamic view, which can be roughly understood as the base table for storing static data, while the dynamic view for storing Dynamic Data
The data dictionary base table stores the basic information of the database. Common users cannot directly access the base table of the data dictionary.
The data dictionary dynamic view is a view created based on the data dictionary base table. Common users can select the data dictionary dynamic view to obtain system information.
Data Dictionary Dynamic View: It mainly includes three types: user_xxx, all_xxx, and dba_xxx
When you create a user and grant permissions or roles to the user, Oracle stores the corresponding information in the data dictionary.
You can query dba_users to display detailed information of all database users.
You can query dba_sys_privs to view the system permissions of a user.
You can query dba_tab_privs to view the object permissions of a user.
You can query dba_col_privs to display the column permissions of the user.
You can query dba_role_privs to display the role of the user.

-- User_tablesselect table_name from user_tables; -- used to display all tables owned by the current user. It returns only all tables of the user's corresponding solution -- all_tablesselect table_name from all_tables; -- displays all tables accessible to the current user. It returns all tables of the current user's solution, dba_tables, a table of other schemes that the user can access, displays the database tables owned by all schemes. However, to query the data dictionary view, the user must be a DBA role or have the select any table system permission. For example, when the system user queries the data dictionary view dba_tables, system, sys, Scott .... select * From dict where comments like '% grant %'; -- select * From global_name; -- display the current database name select tablespace_name from dba_tablespaces; -- display the tablespace Information Select file_name, bytes from dba_data_files where tablespace_name = 'tablespace name '; -- display the data files in the tablespace -- roles and permissions: a role can contain multiple permissions select * From dba_roles; -- Query all roles in Oracle, generally dbaselect distinct privilege from dba_tab_privs; -- Query all object permissions in Oracle, generally dbaselect * From system_privilege_map order by name; -- Query all system permissions in Oracle, generally dbaselect tablespace_name from dba_tablespaces; -- query the tablespace select * From role_sys_privs where role = 'connect '; -- query the system permission of the Connect role select * From dba_sys_privs where grantee = 'connect '; -- query the system permissions of the Connect role select * From dba_tab_privs where grantee = 'connection'; -- query the object permissions of the Connect role

Permission
Overview: the right to execute specific types of SQL commands or access other solution objects. Permissions include system permissions and object permissions.
System permission: used to control one or more database operations that a user can perform.
Oracle9i provides more than 100 system permissions. The higher the Oracle version, the more system permissions it provides.
The new user does not have any permissions or even has no permissions to log on to the database. Therefore, you need to specify the corresponding permissions for it.
It is usually granted by DBA or other users with the grant any privilege permission.
When granting system permission, you can include the with admin option to indicate that the user or role can also grant this system permission to other users or roles.
Common system permissions include the following:
Create session ----------- connect to the database
Create Table ------------- you can create a table in your own solution
Create any table --------- you can create a table in any solution
Create view -------------- create View
Create procedure -------- creation process, function, package
Create trigger ------------ create a trigger
Create cluster ------------ create a cluster
Create public synonym -- Synonym

-- Revoke system permissions -- generally, DBA is used to revoke permissions. If other users are used to revoke permissions, it is required to have the corresponding system permissions and to grant system permissions (with admin option) grant create session, create table to Michael with admin option; -- Grant the crate session to Mahone permission to Michael using the System user; -- use the Michael user to grant the database logon permission to Mahone to revoke create Session from Michael; -- the system permission will not be recycled in cascade mode, in this case, Mahone can still log on to the database's revoke select on EMP from myjava; -- revoke the permission of myjava users to query Scott's EMP table. Note: Who will revoke the permissions assigned to them?

Object permission: the right to access other solution objects. Users can directly access the objects in their own solutions. However, to access objects in other solutions, they must have the object permission.
Commonly used include alter, insert, delete, update, select, index, references, and execute)

-- Grant object permissions -- before Oracle9i, granting object permissions is performed by the object owner or a user with the corresponding (with grant option) permission -- Starting from Oracle9i, DBA users (sys and System) can grant object permissions on any object to other users-object permissions can be granted to users, roles, and public. If the with grant option is included during permission granting, you can grant the permission to other users. However, the with grant option cannot be granted to the role, it can only be granted to the user grant update on EMP (SAL) to Michael; -- Grant Michael the user can only modify Scott. grant alter on EMP to Michael; -- Grant Michael a user to modify Scott. grant execute on dbms_transaction to Michael; -- Grant Michael the permission to run the dbms_transaction package to grant index on Scott. EMP to Michael; -- Grant Michael users the permission to create indexes on the EMP table in the Scott solution -- revoke object permissions is the grant connect to myjava with admin option that will be recycled by cascade; -- if it is a system permission, add with admin optiongrant select on EMP to myjava with grant option; -- if it is an object permission, add with Grant optiongrant select on Scott. EMP to myjava22; -- cascade collection mechanism: if Scott revokes the permission of myjava to query the EMP table, myjava22 authorized by myjava also loses the select permission.

Role
Overview: a set of commands for related permissions. The main purpose of a role is to simplify permission management.
Category: Roles are classified into pre-defined roles and custom roles.
Predefined role: a role provided by Oracle. Each role is used to perform certain management tasks.Common predefined roles include connect, resource, and DBA.
Connect: has most of the permissions required by application developers. In most cases, you only need to assign the connect and resource roles to new users.
It includes the following system Permissions
Alter session, create cluster, create database link, create session, create table, create view, create Sequence
Resource: Other permissions required by application developers. For example, create a stored procedure and trigger. Note that this role has the unlimited tablespace system permission.
It includes the following system Permissions
Create cluster, create indextype, create table, create sequence, create type, create procedure, create trigger
DBA: Has all system permissions and the with admin option. The default DBA users are sys and system. They can grant any system permission to other users.
However, it should be noted that the DBA role does not have the privileges of sysdba and sysoper (START and close the database)
Custom roles: Generally, they are created by DBA or other users with the create role system permission. When creating a role, you can specify the authentication method, that is, not verification and database verification.
1) not verified: If the role is a public role, you can create a role without verification.
SQL> Create role name not identified;
2) database verification: in this case, you need to provide a password for the role to be created. The role name and password are stored in the database. When activating this role, you must provide a password
SQL> Create role name identified by password;
Fine-grained access control: users can use functions and policies to implement more refined security access control.
If fine-grained access control is used, when an SQL statement is issued on the client, Oracle automatically appends the predicate (where clause) after the SQL statement and runs the new SQL statement.
This control allows different database users to return different data information when accessing the same table.

Role authorization-when a new role is created, the role does not have any permissions. In order for a role to complete a specific task, it must be granted the corresponding system and object permissions-1) there is no much difference between authorizing a role and authorizing a user, the with grant option for the unlimited tablesapce and object permissions of the system permission cannot be granted to the role's grant create session to role name with admin option; grant select on Scott. EMP to role name; grant insert, update, delete on Scott. EMP to role name; grant select on EMP to myjava; -- authorize myjava users to query Scott's EMP table. In this case, you must have Scott or higher permissions, such as dBA, before performing this operation-select * from Scott. EMP to query, otherwise the data still cannot be found. Grant all on EMP to myjava; -- authorize myjava users to have all permissions of Scott relative to the EMP table, its permission to query, modify, and delete table data -- 2) assign a role to a user. Generally, DBA or other users with the grant any role system permission can complete the grant role name to Michael with admin option; -- Grant Michael a role, in addition, Michael can grant this role to other users grant resource to myjava; -- authorize myjava users to create tables in any tablespace grant connect to myjava; -- authorize myjava users to have logon permissions grant DBA to myjava; -- authorize myjava users to have DBA permissions; Revoke Delete roles: Use the drop role command. Generally, the drop role name is executed by DBA or other users who have the permission to drop any role system. -- at this time, the role is granted to the user, at the same time, the role will lose the permissions contained by this role. When the role is connected to the database as a user, oracle will automatically activate the default role-dba_role_privs can be used to display all roles of a user and the current default role select * From dba_role_privs where grantee = 'username '; -- show all roles of a user and the current default roles select * From dba_roles; -- show all roles, including the predefined and custom role select * From role_sys_privs where role = 'Role name' -- display the system permission of the role select * From dba_tab_privs where grantee = 'Role name '; -- display the role's object permission token ---------------------------------------------------------------------------------------------------------------------

Logical backup and recovery of database tables
Logical backup: Use the export tool to export the structure and data of the data object to a file.
Logical recovery: When the database object is damaged due to misoperation, use the import tool to import the data object to the database using the backup file.
Physical backup can be performed in the open or closed database state, but logical backup and recovery can only be performed in the open state.
Export:It can be divided into three methods: export table, export solution, and export database.Export and useExpCommand to complete. The common options of this command are as follows:
Userid --- used to specify the user name, password, and connection string used to perform 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 which the export operation is performed.
Inctype -- specifies the incremental type of the export operation.
Rows ----- specifies whether to export data in the table during the export operation.
File ----- used to specify the exported file name

Export tables -- export your own table exp userid = Scott/Hongyu @ jadyer tables = (EMP, Dept) file = D: \ table_emp_dept.dmp -- export tables in other schemes: in this case, the user needs the DBA permission or the exp_full_database permission. For example, the System user can export Scott's table exp userid = system/manager @ jadyer tables = (Scott. EMP) file = D: \ table_emp.dmp -- export the table structure exp userid = Scott/Hongyu @ jadyer tables = (EMP) file = D: \ structure_emp.dmp rows = n -- direct export Method -- this method is faster than the default normal method. When the data size 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 will be reported... Exp userid = Scott/Hongyu @ jadyer tables = (EMP) file = D: \ data_emp.dmp direct = y release export scheme-refers to the use of export tool to export all objects (tables, indexes, constraints, and so on) and data in a scheme or multiple schemes, save and put it in the file -- export your own scheme exp userid = Scott/Hongyu @ jadyer owner = Scott file = D: \ schema_scott.dmp -- export other schemes: in this case, the user needs the DBA permission or the exp_full_database permission. For example, the System user can export any scheme exp userid = system/manager @ jadyer owner = (system, Scott) file = D: \ schema_system_scott.dmp export database -- use the export tool to export objects and data in the database, this user is required to have DBA or exp_full_database permissions exp userid = system/manager @ jadyer full = y inctype = complete file = D: \ database_bak.dmp privileges ---------------------------------------------------------------------------------------------------------------------

Import: use the tool import to import objects and data in the file to the database.However, the imported file must be the file exported by the export.
Similar to export,There are also three import methods: Import table, import solution, and import database,The common options for this command are as follows:
Userid --- used to specify the user name, password, and connection string used to perform the Export Operation
Tables --- used to specify the table for the Import Operation
Fromuser-specifies the source user
Touser --- used to specify the target user
File ----- used to specify the imported file name
Full = Y --- used to specify to import the entire file
Inctype -- specifies the incremental type of the import operation.
Rows ----- specifies whether to import table rows (data)
Ignore --- if the table exists, only data is imported.

Export Import table -- import your table imp userid = Scott/Hongyu @ jadyer tables = (EMP) file = D: \ table_emp.dmp -- import the table to other users: in this case, the user is required to have the DBA or imp_full_database permission imp userid = system/manager @ jadyer tables = (EMP) file = D: \ table_emp touser = Scott -- Structure of the import table: only import the table structure without importing data imp userid = Scott/Hongyu @ jadyer tables = (EMP) file = D: \ table_emp R Ows = n -- import data: if an object (such as a table) already exists, you can import only the table data imp userid = Scott/Hongyu @ jadyer tables = (EMP) file = D: \ table_emp ignore = y release import scheme -- use the import tool to import objects and data in the file to one or more schemes -- to import other schemes, this user is required to have DBA or imp_full_database permissions -- import your own scheme imp userid = Scott/Hongyu file = D: \ schema_scott.dmp -- import other schemes imp userid = system/manager fil E = D: \ schema_system.dmp fromuser = system touser = Scott login import database -- by default, all object structures and data are imported. In this case, you do not even need to specify an instance. It will automatically create an instance named imp userid = system/manager full = y file = D: \ database_bak.dmp restart ---------------------------------------------------------------------------------------------------------------------