Tian Yi 11 -- Summary of Han shunping's Oracle video (04)

System Function: sys_context

Terminal: The identifier of the terminal corresponding to the current session customer

Language: Language

Db_name: Current Database Name

Nls_date_formal: Date Format corresponding to the current session customer

Session_user: the user name of the database corresponding to the current session.

Current_schema: default solution name for the current session customer

HOST: returns the name of the host where the database is located.

You can use this function to query important information, such as the database you are using:

Select sys_context ('Userenv', 'Db _ name') from dual;(Userenv fixed format)

 

 

 

How to connect to the Sys database:

Connsys/change_on_install as sysdba;

Start Database: startup

Close Database: Shutdown

 

 

Manage initialization parameters:

Initialization parameters are used to set instance or database features. Oracle9i provides more than 200 initialization parameters, and each initialization parameter has a default value.

 

Display initialization parameters

Show parameter command

To modify these initialization parameters, go to the file:

D: \ oracle \ admin \ myoral \ pfile \ init. ora file to modify

 

Export:

There are three export Methods: Export Scheme, export table, and export database.

(2) Export is completed using the exp command. Common options of this command are:

Userid: Specifies the username, password, and link 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

Inctype: Specifies the incremental type of the export operation.

Rows: used to specify whether to export data in the table during the export operation.

File: Specifies the exported file name.

 

 Export your table: (Note that the exp letter e is in lowercase)

Expuserid = wangzihu/wangzihu @ sjjz tables = (BBS, book) file = c: \ mysql. dmp;(Import and export data to the bin/exp.exe directory in the Oracle directory or run the CMD command)

(3) export the table structure

 Expuserid = wangzihu/wangzihu @ sjjz tables = (BBS) file = D: \ e3.dmpRows = N;

(4) use direct export

Exp userid = wangzihu/wangzihu @ sjjz tables = (BBS) file = D: \ e3.dmpDirect = y;

This method is faster than the conventional method by default. When the data volume is large, you can consider using this method.

 

Export Scheme:

Export your own scheme (export all objects, including all tables, views, triggers, stored procedures, etc)

Expwangzihu/wangzihu @ sjjz owner=WangzihuFile = D: \ wangzihu. dmp;

 

 

Import

Import table:

Import your own table

IMP userid = wangzihu/wangzihu @ sjjz tables = (BBS) file = D: \ e3.dmp;

2) import the table to other users

This user is required to have DBA permissions or imp_full_database

IMP userid = system/manager @ myortables = (EMP) file = D: \ XX. dmp;

3) Structure of the import table

Only import the table structure without importing data

IMP userid = wangzihu/wangzihu @ sjjztables = (BBS) file = D: \ e3.dmp rows = N;

4) import data

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

IMP userid = wangzihu/wangzihu @ sjjztables (BBS) file = D: \ e3.dmp ignore = y;

 

 

Import solution:

Import your own solution

IMP userid = wangzihu/wangzihufile = D: \ wangzihu. dmp

Import database:

By default, all object structures and data are imported when the database is imported. The example is as follows:

Impuserid = system/manager full = y file = D: \ XXX. dmp

 

 

Data Dictionary: a data dictionary is the most important component of Oracle databases. It provides some system information about data.

The dynamic performance view records information after the routine is started.

 

 

User_tables:

Displays all tables owned by the current user. It returns only all tables of the user's corresponding solution.

For example, selecttable_name from user_tables;

 All_tables:

This interface is used to display all tables accessible to the current user. It not only returns all tables of the current user's solution, but also returns tables of other schemes accessible to the current user:

For example: selecttable_name from all_tables;

Dba_tables:

It displays the database tables owned by all solutions. However, to query this database dictionary view, you must

The user must be a DBA role or a select anytable system permission.

For example, if you use the System user to query DBA. Tables in the data dictionary view, system, sys, Scott… will be returned... The database table corresponding to the solution.

For example: selecttable_name from dba_tables;

 

 

User, permission, role

When a user is created, Oracle stores the user information in the data dictionary. When a user is granted permissions or roles, Oracle stores the permissions and role information in the data dictionary.

By querying DBA. Users, you can view detailed information of database users;

By querying the data dictionary view dba_sys_privs, you can view your system permissions;

You can query the data dictionary view dba_tab_privs to display the object permissions that the user has;

You can query the data dictionary dba_col_privs to display the column permissions that the user has;

You can query the data dictionary view dba_role_privs to display the role of the user.

 

Example: 1) selectusername from dba_users(Fixed username format)

2) Select * From dba_role_privs wheregrantee = 'hangzihu ';(Wangzihu must be capitalized)

View All roles in Oracle:

Select * From dba_roles;

Query all the system permissions in Oracle, generally DBA:

Select * From system_privilege_maporder by name;

 

Query all the object permissions in Oracle, generally DBA:

Select distinct privilege fromdba_tab_privs;

 

 

System permissions contained by a role

Select * From dba_sys_privs where grantee = 'dba ';

Alternatively

Select * From role_sys_privs where role = 'dba ';

Object permissions of a role

Select * From dba_tab_privs where grantee = 'dba ';

 

How can I view the roles of a user?

Select * From dba_role_privs where grantee = 'wangzihu ';

 

 

Display All data dictionary views accessible to the current user

Select * From dict where commentslike '% grant % ';

Display the full name of the current database

Select * From global_name;