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.
- 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';
Could you Please show how to create user with READ_ONLY Role?
ReplyDelete