recent

Titulo

DBA Frequently Used Queries

DATABASE DETAIL:
  • Database Name:
  • SELECT name from v$database;
  • Instance Name:
  • SELECT instance_name from v$instance;
  • Host Name:
  • SELECT hostname from v$version;
  • Version:
  • SELECT * from v$version;
  • Status:
  • SELECT status from v$instance;
  • Start Up Time:
  • SELECT startup_time from v$instance;
LISTENER:
  • Listener.ora Location: 
  • $ORACLE_HOME/network/admin
  • Listener Parameter File:
  • /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  • Listener Log File:
  • /u01/app/oracle/diag/tnslsnr/LocalDB/listener/alert/log.xml
  • Listener Utility:
  • lsnrctl
  • Available operations:
  • lsnrctl show
  • Example:
  • lsnrclt > show oracle_home
  • lsnrclt > show pid
  • lsnrctl> current _listener
  • Status:
  • lsnrctl> status listener_name
  • Start:
  • lsnrctl> start listener_name
  • Stop:
  • lsnrctl> stop listener_name
  • Reload:
  • lsnrctl> reload listener_name
  • Registered Service:
  • lsnrctl> services listener_name
  • Help:
  • lsnrctl help
CONNECTION:
  • Command Line:
  • sqlplus username/password@hostname
  • sqlplus username/password@hostname: port/SID
  • To find Port: 
  • Check Listener status
  • To Find SID: 
  • select * from global_name;
  • ps -ef | grep pmon
  • ora_pmon_LocalDB
  • SID = LocalDB
SID:
  • WINDOWS:
  • SETTING SID
  • set ORACLE_SID=yoursid
  • DISPLAY SID:
  • set ORACLE_SID
  • DISPLAYING ALL ENV VARIABLES:
  • set
  • UNIX/LINUX:
  • SETTING SID
  • EXPORT ORACLE_SID=yoursid
  • DISPLAY SID:
  • echo $ORACLE_SID
  • DISPLAYING ALL ENV VARIABLEs:
  • printenv
MATERIALIZED VIEW:
  • MView Detail:
  • SELECT * From dba_mviews where mview_name ='MVIEW_NAME';
  • Mview Log: MLOG$_MASTER_TABLE_NAME
  • SELECT log_owner, master, log_table FROM dba_mview_logs;
  • MView Refresh:
  • Fast Refresh:
  • BEGIN
  • DBMS_MVIEW.REFRESH('SCHEMA.MVIEW_NAME','F');
  • END;
  • /
  • Force Refresh:
  • BEGIN
  • DBMS_MVIEW.REFRESH('SCHEMA.MVIEW_NAME','?');
  • END;
  • /
  • Complete Refresh:
  • BEGIN
  • DBMS_MVIEW.REFRESH('SCHEMA.MVIEW_NAME','C');
  • END;
  • /
  • REFRESH GROUP:
  • List all MViews from Refresh Group:
  • SELECT r.owner,
  • r.name gp_name,
  • c.name mv_name
  • FROM DBA_RCHILD c,
  • DBA_RGROUP r
  • WHERE c.owner = r.owner AND c.REFGROUP = r.REFGROUP
  • ORDER BY r.owner,
  • r.name,
  • c.name;
  • select * from dba_rchild;
  • select * from dba_rgroup;
FLASHBACK:
  • --Get Timestamp and SCN
  • SELECT TO_CHAR(sysdate, 'dd-mm-yyyy  hh24:mi:ss') DATE,
  • dbms_flashback.get_system_change_number() SCN
  • FROM dual; --DATE:15-08-2016  10:13:37 SCN:14265069831725
  • DELETE FROM scott.employee; -- deleted 107 rows
  • SELECT * FROM scott.employee; -- no records
  • --Flashback Using SCN:
  • SELECT COUNT(*) FROM scott.employee AS OF SCN 14265069831725; --107 records
  • --Flashback Using Timestamp:
  • SELECT COUNT(*)
  • FROM scott.employee
  • AS OF TIMESTAMP to_timestamp('15-08-2016 10:13:37', 'dd-mm-yyyy hh24:mi:ss'); --107
  • --Data from 20 min back:
  • SELECT COUNT(*)
  • FROM scott.employee
  • AS OF TIMESTAMP(systimestamp -interval '15' minute); --107 records
  • --Data from 1 hour back:
  • SELECT COUNT(*)
  • FROM scott.employee
  • AS OF TIMESTAMP(systimestamp -interval '1' hour); --107 records
  • --Interval Example:
  • (systimestamp - interval '1' minute) -- Minute back
  • (systimestamp - interval '1' hour) -- An hour back
  • (systimestamp - interval '1' day) -- Yesterday
  • (sysdatimestamp -1) --Yesterday
  • SELECT (sysdate -1) FROM dual; --Yesterday 
  • Converting SCN to Data and Time:
  • SELECT scn_to_timestamp(14265069831725) FROM dual; --15-AUG-16 10.13.33.000000000 AM
  • Date and Time to SCN:
  • SELECT timestamp_to_scn(to_date('15-AUG-16 10:13:33', 'DD-MON-YY HH:MI:SS')) AS SCN FROM dual; --SCN: 14265069831725
FLASHBACK USING RECYCLE BIN
  • Flashback System Views:
  • DBA_RECYCLEBIN, RECYCLEBIN
  • Flashback Dropped Table:
  • DROP TABLE SCOTT.EMP; --drop table.
  • Flashback Dropped Table:
  • FLASHBACK TABLE emp TO BEFORE DROP; 
  • SYNONYM FLASBACK:
  • create or replace public synonym emp for scott.employee;
  • drop public synonym emp; -- dropped after 10 min.
  • select * from dba_synonym where synonym_name ='EMP'; -- no result found
  • FLASHBACK SYNONYM:
  • SELECT *
  • FROM
  •                 ( SELECT * FROM dba_synonyms WHERE synonym_name = 'EMP'
  •                 ) AS OF TIMESTAMP(systimestamp - interval '5' minute) ; --Displays synonym before dropped.
  • Flashback procedures, functions, & package:
  • create procedures:
  • CREATE OR REPLACE PROCEDURE scott.test_proc
  • AS
  • BEGIN
  •                 NULL;
  • END test_proc;
  • drop procedure:
  • drop procedure scott.test_proc;
  • viewing dropped procedure:
  • SELECT *
  • FROM
  •    ( SELECT * FROM dba_source WHERE NAME = 'TEST_PROC'
  •    ) AS OF TIMESTAMP(systimestamp - interval '2' minute) ;
  • You will be able to view the procedure.
  • Similary, you can see the changed code for procedure, functions, trigger and package.
USER ROLES & PRIVILEGES:
  • Roles & Priviliges
  • Display Roles
  • select * from dba_roles;
  • Roles assigned to a user
  • select  * from dba_role_privs where grantee ='APP_ADMIN';
  • All the roles under a role
  • select  * from dba_role_privs where grantee ='READ_ONLY';
  • Table Privs on a user/schema
  • select  * from dba_tab_privs where grantee ='ADD_ADMIN';
  • Table Privs on a roles
  • select * from dba_tab_privs where grantee ='READ_ONLY';
  • System Privs on a User
  • select * from role_sys_privs where role IN (select granted_role from dba_role_privs where grantee ='APP_ADMIN');
  • System Privs on a Role
  • select * from dba_sys_privs where grantee ='DBA'; 

Work in progress......







1 comment

  1. Could you Please show how to create user with READ_ONLY Role?

    ReplyDelete

Powered by Blogger.