Oracle database role – select from table across schemas without schema identifier

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

• Oracle
• MS SQL Server
• MySQL
• Postgres
• SQLite v3
• Teradata
• DB2
• Sybase
• Vertica
• HSQLDB
• H2
• AWS RedShift
• SAP HANA
• Google Spanner

To find the sessions, as a DBA use

select sid,serial# from v$session where username = '<your_schema>'

If you want to be sure only to get the sessions that use SQL Developer, you can add and program = 'SQL Developer'. If you only want to kill sessions belonging to a specific developer, you can add a restriction on os_user

Then kill them with

alter system kill session '<sid>,<serial#>'

(e.g. alter system kill session '39,1232')

A query that produces ready-built kill-statements could be

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

This will return one kill statement per session for that user - something like:

alter system kill session '375,64855';

alter system kill session '346,53146';