-- Terminate all connections from user "sales" SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'sales';In my case the result was:
pg_terminate_backend ---------------------- t t (2 rows)
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
-- Terminate all connections from user "sales" SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'sales';In my case the result was:
pg_terminate_backend ---------------------- t t (2 rows)
SELECT USERNAME, COUNT(*) FROM v$session WHERE type <> 'BACKGROUND' GROUP BY username;Would be
SELECT datname, usename AS username, COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY datname, usename ORDER BY datname, usename;Example output:
datname | username | count ----------+-------------------+------- postgres | postgres | 1 postgres | postgres_exporter | 1 mydb | myser | 2 (3 rows)
echo $ORACLE_SID testdb01 echo $ORACLE_HOME=/orasw/oracle/product/current ls -la /orasw/oracle/product/current lrwxrwxrwx 1 oracle dba 12 Jan 31 08:34 /orasw/oracle/product/current -> 19.21 ls -la sqlplus / as sysdbaThe above statement will not be able to connect to your instance.
sqlplus sys@testdb01 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 31 08:38:42 2024 Version 19.21.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.21.0.0.0 SYS@testdb01>SQL>show user USER is "SYS"So in order to create a bequeath session, you need an ORACLE_HOME variable that does not use symbolic links.
CREATE USER c##auditadmin IDENTIFIED BYYou log in with your user to the root container:CONTAINER=ALL; GRANT AUDIT_ADMIN TO c##auditadmin CONTAINER=ALL; GRANT SELECT ANY TABLE TO c##auditadmin CONTAINER=ALL; GRANT CREATE SESSION TO c##auditadmin CONTAINER=ALL;
sqlplus c##auditadmin@cdb Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 C##AUDITADMIN@cdb SQL> alter session set container=pdb1; ERROR: ORA-01031: insufficient privilegesTo grant the required privilege, login as sysdba:
sqlplus / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0Grant the SET CONTAINER privilege:
SYS@cdb SQL> grant set container to c##auditadmin container=all; Grant succeeded.Connect with C##AUDITADMIN again, and switch to the PDB1 container within your session:
sqlplus c##auditadmin@cdb Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 C##AUDITADMIN@cdb SQL> show con_name CON_NAME ------------------------------ CDB$ROOT C##AUDITADMIN@cdb SQL> alter session set container=pdb1; Session altered. C##AUDITADMIN@cdb SQL> show con_name CON_NAME ------------------------------ PDB1
ALTER SESSION ENABLE PARALLEL DML | DDL | QUERY;and
ALTER SESSION FORCE PARALLEL DDL | DML | QUERY;Answer:
ALTER SESSION FORCE PARALLEL DDL | DML | QUERY PARALLEL 32;This will override any other DOP in the same session and use 32 parallel workers.
connect / as sysdba create or replace view my_sessions as select username,sid,serial#,osuser,status,program,machine from v$session where username=SYS_CONTEXT('userenv','CURRENT_SCHEMA');Create a public synonym, and grant select to public:
create public synonym my_sessions for my_sessions; grant select on my_sessions to public;To get the procedure to compile in the SYSTEM schema, it is required to grant the "alter system" privilege directly to the user - having the same privilege through a role won't do.
grant alter system to system; grant select on v_$session to system;Log in as system, and create the procedure:
CREATE OR REPLACE PROCEDURE KILL_SESSION(PN_SID NUMBER,PN_SERIAL NUMBER) AUTHID DEFINER AS LV_USER VARCHAR2(30); EXE_USER VARCHAR2(30); BEGIN SELECT S.USERNAME INTO LV_USER FROM V$SESSION S, USER_USERS U WHERE S.SID = PN_SID AND S.SERIAL# = PN_SERIAL AND S.USERNAME = S.USERNAME; SELECT USERNAME INTO EXE_USER FROM V$SESSION WHERE AUDSID=SYS_CONTEXT('userenv','SESSIONID'); IF EXE_USER = LV_USER THEN EXECUTE IMMEDIATE 'alter system kill session '''||PN_SID||','||PN_SERIAL||''''; dbms_output.put_line('Session ' || PN_SID || ',' || PN_SERIAL || ' killed.'); ELSE RAISE_APPLICATION_ERROR(-20000,'Only your own sessions may be killed.'); END IF; END; /Create a synonym for the procedure and give everyone execute privileges on it:
CREATE PUBLIC SYNONYM KILL_SESSION FOR KILL_SESSION; GRANT EXECUTE ON KILL_SESSION TO PUBLIC;Now the users have a way to check their own sessions, and to terminate them if they chose to do so:
connect scott select * from my_session; USERNAME SID SERIAL# OSUSER STATUS PROGRAM MACHINE -------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ---------------------------------------------------------------- SCOTT 1152 9510 oracle INACTIVE sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com SCOTT 1726 31531 oracle ACTIVE sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.comKill a session:
scott@mydb>SQL>exec kill_session(1152,9510);Afterwards, the view will change to show session 1152 to be in status KILLED:
USERNAME SID SERIAL# OSUSER STATUS PROGRAM MACHINE -------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ---------------------------------------------------------------- SCOTT 1152 9510 oracle KILLED sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com SCOTT 1726 31531 oracle ACTIVE sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
EXEC DBMS_SESSION.SET_IDENTIFIER (client_id=>'vegardk');
SELECT SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) "client identifier", SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) "current user" FROM DUAL;
client identifier | current user |
---|---|
vegardk | SCOTT |
exec dbms_application_info.set_client_info('Execute summary');Check v$session:
SELECT USERNAME,CLIENT_IDENTIFIER,CLIENT_INFO FROM V$SESSION WHERE USERNAME='SCOTT';
USERNAME | CLIENT_IDENTIFIER | CLIENT_INFO |
---|---|---|
SCOTT | vegardk | Execute summary |
SELECT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) FROM DUAL;If client_identifier is set, we pick up that value. If not, we use the current_user, which is always set.
CREATE TABLE T1 ( ... LAST_CHANGED_BY VARCHAR2(30 CHAR) DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL );
SELECT S.SID, S.SERIAL#, S.STATUS "status", S.LOGON_TIME "logon time", S.PREV_EXEC_START "last op started", Q.LAST_ACTIVE_TIME "last op ended", S.STATE "state", S.EVENT "event", ROUND((S.WAIT_TIME_MICRO/1000000)/60/60,1) "total wait hrs", DECODE(S.TIME_REMAINING_MICRO,'-1', 'indefinite', '0','Wait timed out',NULL,'session not waiting') "remaining wait time", DECODE(S.TIME_SINCE_LAST_WAIT_MICRO, '0','Still waiting') "current status" FROM V$SESSION S JOIN V$SQL Q ON (S.SQL_ID = Q.SQL_ID) WHERE SID=1758;
SID | SERIAL# | status | logon time | last op started | last op ended | state | event | total wait hrs | remaining wait time | current status |
---|---|---|---|---|---|---|---|---|---|---|
1758 | 35360 | INACTIVE | 14.03.2017 14:33:42 | 14.03.2017 19:10:31 | 14.03.2017 19:20:52 | WAITING | SQL*Net message from client | 13,2 | indefinite | Still waiting |
// *Cause: An attempt was made to exceed the maximum number of // concurrent sessions allowed by the SESSION_PER_USER clause // of the user profile. // *Action: End one or more concurrent sessions or ask the database // administrator to increase the SESSION_PER_USER limit of // the user profile.
alter profile general_users LIMIT SESSIONS_PER_USER <num> | default | unlimited;
# container database CDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.no)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = "container#db01") ) )
show con_name CON_NAME ------------------------------ CDB$ROOT
create user C##DBAMASTER identified by **** default tablespace USERS temporary tablespace TEMP quota unlimited on USERS container=all; SQL> user C##DBAMASTER created.
select USERNAME,CREATED,LAST_LOGIN,COMMON from dba_USERS where trunc(created) = trunc(sysdate) USERNAME CREATED LAST_LOGIN COM -------------------- --------- ---------------------------------------- --- C##DBAMASTER 18-DEC-16 18-DEC-16 03.16.16.000000000 PM +01:00 YES
grant connect to C##DBAMASTER; grant set container to C##DBAMASTER container=all; grant create user to C##DBAMASTER container=all; grant drop user to C##DBAMASTER container=all; SQL> Grant succeeded.
connect C##DBAMASTER/***@cdb Connected. show user USER is "C##DBAMASTER"Switch container:
alter session set container=vpdb01; SQL> Session altered.Create a new local user in the current container:
create user "1Z0061" identified by **** default tablespace users quota unlimited on users temporary tablespace TEMP container=current; SQL> User created.
SQL> connect C##DBAMASTER/****@vpdb01 ERROR: ORA-01045: user C##DBAMASTER lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> connect system/*****@vpdb01 Connected. SQL> show con_name CON_NAME ------------------------------ VPDB01 SQL> grant create session to C##DBAMASTER container=current; Grant succeeded. SQL> connect C##DBAMASTER/****@vpdb01 Connected. SQL> show con_name CON_NAME ------------------------------ VPDB01
grant create session to C##DBAADMIN container=all;vs
grant set container to C#DBAADMIN container=all;
Listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
proddb01_ipc = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proddb01) ) )Connect to your database locally:
sqlplus /nolog SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015 Copyright (c) 1982,2013, Oracle. All rights reserved. SQL> connect scott/tiger Connected.From another window, create another session:
sqlplus /nolog SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015 Copyright (c) 1982,2013, Oracle. All rights reserved. SQL> connect scott/tiger@proddb01_ipc Connected.
SELECT S.SID, S.OSUSER,S.PROGRAM,S.USERNAME,S.MACHINE, SCI.NETWORK_SERVICE_BANNER,S.LOGON_TIME,S.STATUS FROM V$SESSION S INNER JOIN V$SESSION_CONNECT_INFO SCI ON S.SID = SCI.SID WHERE S.USERNAME = UPPER('scott') AND SCI.NETWORK_SERVICE_BANNER LIKE '%IPC%' OR SCI.NETWORK_SERVICE_BANNER LIKE INITCAP('%BEQ%') AND S.TYPE <> 'BACKGROUND' ORDER BY LOGON_TIME;
SID | OSUSER | PROGRAM | USERNAME | MACHINE | NETWORK_SERVICE_BANNER | LOGON_TIME | STATUS |
---|---|---|---|---|---|---|---|
9 | oracle | sqlplus@myserver.mydomain.com (TNS V1-V3) | SCOTT | myserver.mydomain.com | Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production | 22.01.2015 15:35:49 | INACTIVE |
1160 | oracle | sqlplus@myserver.mydomain.com (TNS V1-V3) | SCOTT | myserver.mydomain.com | Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.4.0 - Production | 22.01.2015 15:40:44 | INACTIVE |
SET LINES 300 PAGES 300 SET TRIMSPOOL ON SPOOL SQL COL "Os pid" FORMAT A10 COL MACHINE FORMAT A30 COL "SQL Text" FORMAT A100 WRA COL PROGRAM FORMAT A20 COL USERNAME FORMAT A12 SELECT S.SID, S.SERIAL#, S.USERNAME, S.STATUS, P.SPID "Os pid", S.MACHINE, CONCAT(SUBSTR(A.SQL_TEXT,1,60), '...(output truncated)') "SQL text" FROM V$SESSION S, V$SQLAREA A, V$PROCESS P WHERE A.ADDRESS = S.SQL_ADDRESS AND S.PADDR=P.ADDR ORDER BY 1 /Example output:
SID SERIAL# USERNAME Os pid MACHINE SQL text ---------- ---------- ------------ ---------- ------------- ------------------------------------------------------------------------------------ 4 3 6357052 myserver insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtim...(output truncated) 18 7035 SYS 21496004 myserver call DBMS_AQADM_SYS.REGISTER_DRIVER ( )...(output truncated) 107 62143 SYS 23527460 myserver SELECT S.SID, S.SERIAL#, S.USERNAME, P.SPID "Os pid"...(output truncated) 303 27191 SYS 16777372 myserver analyze table scott.man_to_stage validate structure cascade o...(output truncated) 393 1 8716332 myserver insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtim...(output truncated) 402 3125 BATCHUSR 18612294 myserver call mypackage.start_dorg ( )...(output truncated) 490 1 8257548 myserver insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtim...(output truncated) 499 25461 SYS 13762730 myserver call DBMS_AQADM_SYS.REGISTER_DRIVER ( )...(output truncated) 503 2635 SYS 11272234 myserver call DBMS_AQADM_SYS.REGISTER_DRIVER ( )...(output truncated) 593 12091 BATCHUSR 19726590 myserver call myprocedure.read_queue ( )...(output truncated) 600 9193 SYS 21102724 COMP\PC1 select * from dba_locks...(output truncated) 694 44601 BATCHUSR 20840656 myserver call mypackage.check_job_consistency( )...(output truncated) 696 819 SYS 15269968 myserver call DBMS_AQADM_SYS.REGISTER_DRIVER ( )...(output truncated) 700 1135 BATCHUSR 21364890 myserver DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WIT...(output truncated)
SET TERMOUT OFF ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'; SET TERMOUT ON SET LINES 300 COL "CURRENTLY EXECUTING" FORMAT A35 COL OSUSER FORMAT A10 COL MACHINE FORMAT A20 COL USERNAME FORMAT A20 COL PROGRAM FORMAT A40 COL UNITS FORMAT A20 COL OPNAME FORMAT A10 COL START_TIME FORMAT A20 COL LAST_UPDATE_TIME FORMAT A20 COL SQL_ID FORMAT A15 SET VERIFY OFF SET FEEDBACK OFF SELECT S.SID, S.SERIAL#, S.OSUSER, S.MACHINE, S.USERNAME, S.PROGRAM, S.SQL_ID, SUBSTR(Q.SQL_TEXT,1,30) || '...' "CURRENTLY EXECUTING", L.OPNAME, L.SOFAR, L.TOTALWORK, L.UNITS, L.START_TIME, L.LAST_UPDATE_TIME, L.TIME_REMAINING "SECONDS LEFT", TO_TIMESTAMP(L.LAST_UPDATE_TIME,'DD.MM.YYYY HH24:MI:SS')-TO_TIMESTAMP(L.START_TIME,'DD.MM.YYYY HH24:MI:SS') "RUNNING FOR" FROM V$SESSION_LONGOPS L JOIN V$SESSION S ON L.SID = S.SID JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID AND S.SID = &SID AND S.SERIAL# = L.SERIAL# ORDER BY L.LAST_UPDATE_TIME DESC; EXIT
SID | SERIAL# | OSUSER | MACHINE | USERNAME | PROGRAM | SQL_ID | CURRENTLY EXECUTING | OPNAME | SOFAR | TOTALWORK | UNITS | START_TIME | LAST_UPDATE_TIME | SECONDS LEFT | RUNNING FOR |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
408 | 2624 | oracle | testserver1 | SH | sqlplus@testserver1 (TNS V1-V3) | 3w11rcbvd5a32 | UPDATE SALES_Q1_DI_MASTERS SET ... | Table Scan | 82432 | 82636 | Blocks | 06.03.2014 07:35:26 | 06.03.2014 09:44:22 | 19 | +00 02:08:56.000000 |
SESSION_ID | OWNER | NAME | MODE_HELD | MODE_REQUESTED | LAST_CONVERT | BLOCKING_OTHERS |
---|---|---|---|---|---|---|
362 | VEGARDK | FAMILY_TREE | Row-X (SX) | None | 39 | Not Blocking |
set verify off set linesize 200 col "os process" format a30 col "os process id" format a10 col "osuser" format a15 col "schemaname" format a20 col "client program" format a20 col "client name" format a20 col "session type" format a20 col status format a10 col "session type" format a15 cle scr prompt =================================================== prompt This script will print basic information about a prompt session. prompt accept SID prompt 'Pls enter SID of session: ' prompt =================================================== prompt Information about database session &&SID prompt =================================================== column host_name new_value v_hostname; set termout off select host_name from v$instance; set termout on select p.program "os process", p.spid "os process id", p.username "osuser", s.sid,s.serial# "serial num", lower(s.schemaname) "schemaname", lower(s.osuser) "client name", s.program "client program", lower(s.status) "status", lower(s.type) "session type" from v$process p, v$session s where p.program not like 'oracle@v_hostname (%' and p.addr = s.paddr and p.addr = (select paddr from v$session where sid=&&SID) order by s.username asc / set heading off prompt =================================================== prompt Kill statement for session &&SID: prompt =================================================== select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' from v$process p, v$session s where p.program not like 'oracle@v_hostname (%' and p.addr = s.paddr and p.addr = (select paddr from v$session where sid=&&SID) order by s.username asc / exit
-- Find table partitions with GLOBAL indexes: SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE GLOBAL INDEXES;' FROM dba_tab_partitions WHERE table_owner = 'USER1' AND tablespace_name = 'DATA1' AND subpartition_count = 0 AND table_name IN ( SELECT distinct table_name FROM dba_part_indexes WHERE owner = 'USER1' AND locality = 'GLOBAL' ) ORDER BY table_name DESC; -- Find table partitions with LOCAL indexes: SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE INDEXES;' FROM dba_tab_partitions WHERE table_owner = 'USER1' AND tablespace_name = 'DATA1' AND subpartition_count = 0 AND table_name IN ( SELECT distinct table_name FROM dba_part_indexes WHERE owner = 'USER1' AND locality = 'LOCAL' ) ORDER BY table_name DESC; -- Find table partitions with no index at all SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS;' FROM dba_tab_partitions WHERE table_owner = 'USER1' AND tablespace_name = 'DATA1' AND subpartition_count = 0 AND table_name NOT IN ( SELECT table_name FROM dba_part_indexes WHERE owner = 'USER1' ) ORDER BY table_name DESC;