As per Doc ID 1580225.1 The package DBMS_UNDO_ADV is undocumented, and it is used internally by the Undo Advisor . dbms_undo_adv package gives advise based on historical information present in memory or Automatic Workload Repository. The default retention of AWR is 7 days. Function longest_query : Returns the length of the longest query for a given period . Method 1 SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual; Method 2 (using Start/End time) SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual; Method 3 (using Begin/End AWR snapshot id) SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual; Function required_retention: returns the undo_retention value required for running the longest query. Method 1 select dbms_undo_adv.required_retention from dual; Method 2 (using Start/End time) SELECT Ddbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual; Method 3 (using Begin/End AWR snapshot id) SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual; Function best_possible_retention: Returns the best possible undo retention which the current undo tablespace can support. Note:If undo tablespace autoextensible then "maxsize" setting will be taken into account to calculate best_possible_retention. Method 1 SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual; Method 2 (using Start/End time) SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual; Method 3 (using Begin/End AWR snapshot id) SELECT dbms_undo_adv.best_possible_retention(345, 768) best_retention FROM dual; Function required_undo_size: Returne the appropriate undo tablespace size to satisfy undo retention value. Method 1 SELECT dbms_undo_adv.required_undo_size(900) required_undo_size FROM dual; Method 2 (using Start/End time) SELECT dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) required_undo_size FROM dual; Method 3 (using Begin/End AWR snapshot id) SELECT dbms_undo_adv.required_undo_size(900, 345, 768) required_undo_size FROM dual;
Saturday, November 30, 2013
Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV).
Monday, November 25, 2013
Starting And Stopping OEM/OMS 12c Agent and Server.
[oracle@afr bin]$ pwd/u01/app/oracle/Middleware/agent/agent_inst/bin[oracle@afr bin]$ ./emctl stop agentOracle Enterprise Manager 12c Cloud Control 12.1.0.1.0 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. Stopping agent ..... stopped.[oracle@afr bin]$ ./emctl start agentOracle Enterprise Manager 12c Cloud Control 12.1.0.1.0 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. Starting agent ........................................... started.[oracle@afr bin]$ pwd/u01/app/oracle/Middleware/oms/bin[oracle@afr bin]$ ./emctl stop oms -allOracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. Stopping WebTier... WebTier Successfully Stopped Stopping Oracle Management Server... Oracle Management Server Successfully Stopped AdminServer Successfully Stopped Oracle Management Server is Down
Saturday, November 9, 2013
How To Find Views And What Information They Provide In Oracle Database ?
SQL> desc dba_views;Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(30) VIEW_TYPE VARCHAR2(30) SUPERVIEW_NAME VARCHAR2(30) EDITIONING_VIEW VARCHAR2(1) READ_ONLY VARCHAR2(1)SQL> select view_name from dba_views where view_name like'%ROLE%';VIEW_NAME ------------------------------ V$XS_SESSION_ROLE DBA_APPLICATION_ROLES USER_APPLICATION_ROLES EXU9NTAROLE EXU81APPROLE DBA_ROLES DBA_ROLE_PRIVS USER_ROLE_PRIVS ROLE_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS VIEW_NAME ------------------------------ EXU10NTAROLE SESSION_ROLES XS_SESSION_ROLES DBA_SCHEDULER_JOB_ROLES KU$_DEFROLE_LIST_VIEW KU$_DEFROLE_VIEW KU$_DUMMY_ROLE_VIEW KU$_PROXY_ROLE_LIST_VIEW KU$_ROLE_VIEW DBA_CONNECT_ROLE_GRANTEES PROXY_ROLES VIEW_NAME ------------------------------ PROXY_USERS_AND_ROLES ROLE_WM_PRIVS MGMT$ESA_DBA_ROLE_REPORT 25 rows selected.SQL> desc dict;Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME VARCHAR2(30) COMMENTS VARCHAR2(4000)SQL> select * from dict where table_name like '%ROLE%';TABLE_NAME ------------------------------ COMMENTS -------------------------------------------------------------------------------- DBA_ROLES All Roles which exist in the database DBA_ROLE_PRIVS Roles granted to users and roles USER_ROLE_PRIVS Roles granted to current user TABLE_NAME ------------------------------ COMMENTS -------------------------------------------------------------------------------- DBA_SCHEDULER_JOB_ROLES All scheduler jobs in the database by database role DBA_CONNECT_ROLE_GRANTEES Information regarding which users are granted CONNECT ROLE_ROLE_PRIVS Roles which are granted to roles TABLE_NAME ------------------------------ COMMENTS -------------------------------------------------------------------------------- ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles SESSION_ROLES Roles which the user currently has enabled. 9 rows selected.
Subscribe to:
Posts (Atom)