The structure of the memory used to access the instance is constantly changingstatus information. In Oracle DB, you also maintain a dynamic dataset of database instance operations and performance. These dynamic performance is consideredThe diagram is based on a virtual table built on the internal memory structure of the database server. In other words, these views are not residentA table in the traditional sense that remains in the database. That's why partial views can be loaded or opened before the databasethe reason for the use. The dynamic performance view contains the following information:? Session? File status? Progress of jobs and tasks? Lock? Backup status? Memory utilization and memory allocation? System parameters and session parameters? SQL execution? Statistics and Metrics Note: The DICT and Dict_columns views also contain the names of these dynamic performance views. Dynamic Performance View toThe prefix "v$" begins with the number exceeding 590.
- Dynamic Performance Views: usage examples
These views are often used by enterprise Manager, but other users can also query these views as needed. 1. What SQL statements (and their associated execution numbers) consume more than 200,000 microseconds of CPU time? Sql> SELECT sql_text, executions from
v$sql WHERE cpu_time > 200000;2. In the previous day, which current sessions are logged on from the EDRSR9P1 computer? Sql> SELECT * FROM
v$sessionWHERE machine =' edrsr9p1 ' and Logon_time > SYSDATE-1;3. What is the session ID of those sessions that currently hold locks that block other users? How long have these locks been held? sql> SELECT SID, CTime from
V$lockWHERE block > 0;
- Dynamic Performance View: Considerations
? These views are owned by the SYS user. Different views can be used in different situations: – The instance has been started. – The database is already loaded. – The database is open.? You can view all view names by querying v$fixed_table. These views are often referred to as "v$ views". Because their data is dynamic, it is not guaranteed to read on these viewsconsistency. Some dynamic views contain data that does not apply to all States of an instance or database. For example, if an instance has just started,but no database is mounted, you can view the columns of a running background process by querying v$bgprocesstable. However, you cannot view the status of a database data file by querying V$datafile because you want to load the databaseto read the control file before it can find the data file associated with the database. Some v$ views contain information similar to the information in the corresponding Dba_ view. For example, V$datafile is similar toDba_data_files. Also note that the v$ view name is usually singular, and the Dba_ view name is a complex numberform.
- Data dictionary: Overview
An Oracle data dictionary is the metadata for a database that contains the names and attributes of all the objects in the database. Create ormodifying any object causes the data dictionary to be updated to reflect those changes. This information is stored in the Oracle DBmaintained base tables, however, these tables can be accessed using predefined views (rather than directly reading the table). Data dictionary:? Used by the Oracle DB server to find information about users, objects, constraints, and storage? When the object structure or definition is modified, will Oracle DB be maintained accordingly? Can be used by any user to query information about the database? Owned by the SYS user? Do not modify directly with SQL Note: The Dictionary data Dictionary view (or its dict synonym) contains the names of the data dictionary tables and views anddescription. Use the Dict_columns view to view the view columns and their definitions.
The view prefix indicates the data (and the amount of data) that a given user can see. Only users with DBA authority can access the global view (with the Dba_ prefix) that contains all objects. The next permission level is the All_ prefix level, which represents all objects that a query user has permission to view, regardless of whether the userown these objects. For example, if USER_A is authorized to access a table that belongs to User_b, then user_a will seeThe table is listed in all All_ views related to the table name. The prefix user_ represents the smallest visible range. This type of view displays only those objects that belong to the queried user (that is,objects that exist in the user's own scenario). In general, each view set is a subset of the view set with higher permissions, whether by row or column. Notall views in a given view set have corresponding views in other view sets. This depends on the nature of the information in the view. For example, there is a dba_lock view, but there is no all_lock view. This is because only the DBAfocus on the data about the lock. To meet your needs, be sure to select the appropriate set of views. Even if you have access to the DBA view,you may also want to query only the user version of the view, because the results will show you information about the objects you own, and you canyou do not want the result set to contain other objects. The Dba_ view can only be queried by users who have SYSDBA or select any DICTIONARY permissions. Not all dictionary views begin with the prefix Dba_, All_, and User_. The following view or view is synonymous with the exception:? Audit_actions? CAT? Change_propagations? Change_propagation_sets? Change_sets? Change_sources? Change_tables? Client_result_cache_stats$? CLU? COLS? Column_privileges? Database_compatible_level? Dbms_alert_info? Dbms_lock_allocated? DICT? DICTIONARY? Dict_columns? DUAL? Global_name? IND? Index_histogram? Index_stats? Logstdby_unsupported_tables? Nls_database_parameters? Nls_instance_parameters? Nls_session_parameters? Obj? RecycleBin? Resource_cost? Role_role_privs? Role_sys_privs? Role_tab_privs? SEQ? Session_privs? Session_roles? Sm$version? Syn? Table_privileges? TABS
- Data dictionaries: Usage examples
1. What is the name of the table created in the scenario (and the name of the table space where the table is located)? SELECT table_name, T Ablespace_namefrom
user_tables;2. What are the important information about the sequence in the database you have access to? SELECT Sequence_name, Min_value, Max_value,increment_byfrom
all_sequences WHERE Sequence_owner in (' Mdsys ', ' XDB ');3. What are the current users in this database who can log in? SELECT USERNAME, Account_statusfrom
dba_users WHERE account_status = ' OPEN ';4. What are the columns of the dba_indexes view? This shows what information you can look at all the indexes in the database. DESCRIBE
dba_indexesThe partial output of this command is listed below:sql> DESCRIBE dba_indexesname Null? Type------------------------------------OWNER NOT NULL VARCHAR2 ($) index_name NOT null VARCHAR2 (in) index_ TYPE VARCHAR2 (table_owner) NOT null VARCHAR2 (+) table_name NOT NULL VARCHAR2 (30)
- Enable data dictionary protection :
You can protect the data dictionary by setting the initialization parameter o7_dictionary_accessibility to False.
This parameter prevents users (who have any system permissions, that is, users who have permissions on the SYS mode object) to use these data dictionaries.
The Oracle database provides highly granular permissions.
One such privilege, usually referred to as any privilege, is usually granted only to the owners and personal database administrator of the application.
For example, you can grant the DROP any TABLE permission to an application owner, by opening or closing the 07_dictionary_accessibility initialization parameter, against any privileges that are accidentally or maliciously used, Can protect Oracle data dictionaries.
Enable Data Dictionary protection workflow
Use EM to log in to Oracle Enterprise Manager database control.
Click server "Servers" and click initialization Parameters "Initialize parameters".
Search o7_dictionary_accessibility This parameter.
will be O7_DICTIONARY_ACCESSIBILTY set toFALSE。
5.Click Apply, and then restart the database.
O7_dictionary_accessibility Parameters:
O7_dictionary_accessibility controls restrictions on system permissions. If the parameter is set to True, the data dictionary is accessible to objects in the SYS schema. The default setting is False, which ensures that system permissions are allowed to access any other mode without accessing the SYS mode.
For example,if O7_dictionary_accessibility is set to false, then SELECT ANY TABLE the permission allows access to any view or table in addition to the SYS mode (the data dictionary cannot be accessed). system Rights EXECUTE any PROCEDURE , which can access any mode except the SYS mode.
If this parameter is set to Falise, you need to access the object in the SYS mode, then you must explicitly grant the object permission. The following roles can be granted to the database administrator and also to access the Dictionary objects:
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
Source: http://blog.csdn.net/rlhua/article/details/12710613
