Hello Reader
In this article , we will cover SecureCRT Buttons for Default.
############Setting up Format (format):
To convert invalid timestamp value and to check current date.
|
1 2 |
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; Session altered. |
######### Instance Information (inst_info):
This SQL outputs the information of instance that is up and running like whether it is a read only, read/write, primary or standby like below.
se
|
1 2 3 4 5 |
lect name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORCL - ORCL11@awsmachine1 - ORCL - 12.1.0.2.0 - READ WRITE - 31-MAR-2020 00:38:07 - PRIMARY |
### Sessions Count USERNAME (sessions count username):
Output of this SQL describes number of sessions created per username.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
set linesize 300 col machine for a40 select inst_id, username,count(*),machine from gv$session group by username,inst_id,machine order by inst_id; Example : INST_ID USERNAME COUNT(*) MACHINE ---------- ------------------------------ ---------- ---------------------------------------- 1 DBSNMP 2 awsmachine1.ktexperts.com 1 SYS 8 awsmachine1 1 72 awsmachine1 2 DBSNMP 3 awsmachine2.ktexperts.com 2 SYS 1 awsmachine1 2 SYS 6 awsmachine2 2 73 awsmachine2 |
### smon|tns:
This outputs the list of database instances which are up and running in a server.
|
1 |
#ps -eaf |egrep 'smon|lmon|tns|mrp'; env | grep ORA |
####### Services:
This outputs the list of services which are active in the databases. Here, services means tns service.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
set linesize 300 set pagesize 30 select inst_id,service_id,name from gv$active_services order by 1,2; INST_ID SERVICE_ID NAME ------- ---------- ---------------------------------------------------------------- 1 1 SYS$BACKGROUND 1 2 SYS$USERS 1 3 ORCL1 1 4 orcl1_app_svc 1 5 orcl1XDB 1 7 orcl_int_svc 1 8 SYS.KUPC$C_1_20200311143024 1 9 SYS.KUPC$S_1_20200311143024 2 1 SYS$BACKGROUND 2 2 SYS$USERS 2 3 ORCL1 2 4 orcl1_app_svc 2 5 orcl1XDB 2 7 orcl_int_svc |
################ gv_inst_info
This outputs since when the instance is up and what is the role of the instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
set linesize 300 col host_name for a40 col instance_role for a20 col startup_time for a25 select INST_ID,INSTANCE_NAME,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') startup_Time, INSTANCE_ROLE from gv$instance order by inst_id; Example : INST_ID INSTANCE_NAME HOST_NAME STARTUP_TIME INSTANCE_ROLE -------- ------------ ----------- ------------------------ ------------------------- -------------------- 1 orcl11 awsmachine1 31-MAR-2020 00:38:07 PRIMARY_INSTANCE 2 orcl12 awsmachine2 31-MAR-2020 00:38:06 PRIMARY_INSTANCE |
######### My_SID
This SQL outputs session ID, status and login time of my user account.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
select sys_context('USERENV','SID') from dual; set linesize 132 set pagesize 1000 set echo on col rbs format a6 col oracle_user format a12 heading "Oracle|Username" col sid format 99999 heading "Oracle|SID" col serial# format 99999 heading "Oracle|Serial#" col unix_pid format a6 Heading "Unix|PID" col Client_User format a12 Heading "Client|Username" col Unix_user format a12 Heading "Unix|Username" col Logon_time format a11 Heading "Login|Time" col lock_wait format a4 heading "Lock|Wait" select s.username oracle_user, s.osuser client_user, p.username unix_user, s.status status, s.sid sid, s.serial# serial#, p.spid unix_pid, to_char(s.logon_time,'MM-DD HH24:MI') logon_time, decode(s.lockwait,NULL,'No','Yes') lock_wait from v$process p, v$session s where s.paddr = p.addr(+) and s.sid = (select sys_context('USERENV','SID') from dual) order by 1; Example : SQL> select sys_context('USERENV','SID') from dual; SYS_CONTEXT('USERENV','SID') ------------------------------------------------------- 609 set linesize 132 set pagesize 1000 set echo on col rbs format a6 col oracle_user format a12 heading "Oracle|Username" col sid format 99999 heading "Oracle|SID" col serial# format 99999 heading "Oracle|Serial#" col unix_pid format a6 Heading "Unix|PID" col Client_User format a12 Heading "Client|Username" col Unix_user format a12 Heading "Unix|Username" col Logon_time format a11 Heading "Login|Time" col lock_wait format a4 heading "Lock|Wait" select s.username oracle_user, s.osuser client_user, p.username unix_user, s.status status, s.sid sid, s.serial# serial#, p.spid unix_pid, to_char(s.logon_time,'MM-DD HH24:MI') logon_time, decode(s.lockwait,NULL,'No','Yes') lock_wait from v$process p, v$session s where s.paddr = p.addr(+) and s.sid = (select sys_context('USERENV','SID') from dual) order by 1; Oracle Client Unix Oracle Oracle Unix Login Lock Username Username Username STATUS SID Serial# PID Time Wait ------------ ------------ ------------ -------- ------ ------- ------ ----------- ---- SYS oracle oracle ACTIVE 609 19416 14620 03-31 06:58 No |
########## No.of Connections per service (No.of_Conn’s_for_Service)
This displays the number of active connections created per tns service/schema.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
set linesize 300 col machine for a50 col service_name for a25 select inst_id,service_name,count(*) from gv$session group by inst_id,service_name order by 1,3; select inst_id,service_name,count(*) from gv$session where username is not null and SERVICE_NAME not like 'SYS$%' group by inst_id,service_name order by 1,3; Example : select inst_id,service_name,count(*) from gv$session group by inst_id,service_name order by 1,3; INST_ID SERVICE_NAME COUNT(*) ---------- ------------------------- ---------- 1 SYS$USERS 10 1 SYS$BACKGROUND 72 2 SYS$USERS 10 2 SYS$BACKGROUND 73 select inst_id,service_name,count(*) from gv$session where username is not null and SERVICE_NAME not like 'SYS$%' group by inst_id,service_name order by 1,3; no rows selected |
############ Check connections for Service Name (Chk_connections_for_ServiceName)
It shows number of connections created, instance ID, and machine name per service name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set linesize 300 col machine for a50 col service_name for a25 select inst_id, username,count(*),machine,service_name from gv$session where service_name='&service_name' group by username,inst_id,machine,service_name order by inst_id; Example : select inst_id, username,count(*),machine,service_name from gv$session where service_name='&service_name' group by username,inst_id,machine,service_name order by inst_id; Enter value for service_name: SYS$BACKGROUND old 1: select inst_id, username,count(*),machine,service_name from gv$session where service_name='&service_name' group by username,inst_id,machine,service_name order by inst_id new 1: select inst_id, username,count(*),machine,service_name from gv$session where service_name='SYS$BACKGROUND' group by username,inst_id,machine,service_name order by inst_id INST_ID USERNAME COUNT(*) MACHINE SERVICE_NAME ---------- ------------------------------ ---------- -------------------------------------------------- ------------------------- 1 72 awsmachine1 SYS$BACKGROUND 2 73 awsmachine2 SYS$BACKGROUND |
########### tnsconn
How to connect to SQL using Service name and hostname
|
1 |
#sqlplus system/PW@GMR-scan-r5:1521/ORCL_SRV |
######## Sudo_to_root
To connect as root user.
|
1 |
sudo su - root |
######## GV_Active_Sess
This shows number of active sessions per instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
select (select 'Active Sessions in Inst 1 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=1 and sql_id is not null and username not in ('SYS','SYSTEM')) as c2 from dual union select (select 'Active Sessions in Inst 2 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=2 and sql_id is not null and username not in ('SYS','SYSTEM')) as c2 from dual union select (select 'Active Sessions in Inst 3 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=3 and sql_id is not null and username not in ('SYS','SYSTEM')) as c2 from dual union select (select 'Active Sessions in Inst 4 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=5 and sql_id is not null and username not in ('SYS','SYSTEM')) as c2 from dual; Active Sessions in Inst 1 : 4 Active Sessions in Inst 2 : 6 Active Sessions in Inst 3 : 4 Active Sessions in Inst 4 : 8 |
########## set_user
To switch from one schema to another.
|
1 2 3 4 5 6 7 8 |
alter session set current_schema=&User; alter session set current_schema=&User; Enter value for user: VINOD old 1: alter session set current_schema=&User new 1: alter session set current_schema=VINOD Session altered. |
############ export_SID
To login to a database, we need to export its SID first, using below command.
|
1 |
export ORACLE_SID=$1 |
I hope the above information is useful and helpful.
Follow us on :
Website : https://www.ktexperts.com/
Facebook Page: https://www.facebook.com/ktexperts/
Facebook Group : https://www.facebook.com/groups/ktexperts/
Linkedin : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
Youtube : https://www.youtube.com/channel/UCJ-gDTLfNXSY3QoV_fnKtOg
Note: Please test scripts in Non Prod before trying in Production.
Loading...
