Toad World® Forums

Toad v13 - parallel queries


#1

When I have a running parallel query and click on one of the slaves, I now get a “ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA” even though I’m still logged on.

The oracle version is 12.2

I have a hardcopy print of the message if you need it

best reagard Mette


#2

I click on it from the session browser !


#3

Will you turn on spool SQL and then try it again, then post the spool sql output?


#4

This was all I got


– Session: SYS@PTIA1000

– Timestamp: 23:32:12.106

Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,

decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address

, decode(sql_id, null, prev_sql_id, sql_id) sql_id

, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number

from v$session

where sid = :sid;

:sid(VARCHAR[2],IN/OUT)=‘10’


– Session: SYS@PTIA1000

– Timestamp: 23:32:12.119

Select instance_name

from gv$instance

where inst_id = :id;

:id(INTEGER)=-1


– Timestamp: 23:32:12.132

Connect: SYS@LISTENER_ORCL


– Session: SYS@LISTENER_ORCL

– Timestamp: 23:32:12.151

Error: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

2018-05-11 23:28 GMT+02:00 John Dorlon bounce-jdorlon@toadworld.com:

RE: Toad v13 - parallel queries

Reply by John Dorlon
Will you turn on spool SQL and then try it again, then post the spool sql output?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Mette Stephansen

Miracle Expert ServicesMiracle A/S
Borupvang 2C, 2750 Ballerup

53 74 72 26


#5

Something is off. It looks like you are connected to a RAC instance. When you run the threaded query, Toad wants to be sure to connect to the same instance… and this query:

Select instance_name

from gv$instance

where inst_id = :id;

:id(INTEGER)=-1

is supposed to do that, but it thinks your instance ID is -1, which is clearly wrong. Were there any errors when you made your initial connection? That big pl/sql block that we run on connection should have retrieved the instance ID.


#6

Oops, what I meant was that the query that I mentioned helps us look up a TNSNames entry to connect to, so we are connected to the right instance. But since that query has -1 as the instance id, well, everything goes downhill from there.


#7

This is not a RAC instance - it a “normal flat” 12.2 Enterprise Ed :slight_smile:

NO problems during logon - and not afterwards either - I’m still logged on, and can see all info on the other sessions without logging on again

2018-05-11 23:48 GMT+02:00 John Dorlon bounce-jdorlon@toadworld.com:

RE: Toad v13 - parallel queries

Reply by John Dorlon
Oops, what I meant was that the query that I mentioned helps us look up a TNSNames entry to connect to, so we are connected to the right instance. But since that query has -1 as the instance id, well, everything goes downhill from there.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Mette Stephansen

Miracle Expert ServicesMiracle A/S
Borupvang 2C, 2750 Ballerup

53 74 72 26


#8

This is spool when I connect: - I connect to SYS as SYSDBA


– Timestamp: 00:01:55.811

Connect: SYS@PTIA1000


– Session: SYS@PTIA1000

– Timestamp: 00:01:56.047

SELECT version, product, sysdate FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE ‘%ORACLE%’;


– Session: SYS@PTIA1000

– Timestamp: 00:01:56.062

Declare

debug_connect_session number;

alter_any_table number;

alter_session number;

alter_system number;

alter_database number;

alter_user number;

create_table number;

create_role number;

create_trigger number;

create_sequence number;

administer_database_trigger number;

administer_resource_manager number;

create_any_index number;

create_any_sequence number;

create_any_table number;

create_any_trigger number;

debug_any_procedure number;

execute_any_procedure number;

select_any_table number;

delete_any_table number;

insert_any_table number;

grant_any_object_priv number;

grant_any_role number;

grant_any_privilege number;

select_any_dictionary number;

update_any_table number;

create_public_syn number;

has_dba_synonyms number;

has_dba_objects number;

select_catalog_role number;

db_charset nls_database_parameters.value%type;

compatible_version varchar2(60);

sid number;

local_trans_id varchar2(60);

qry_str varchar2(4000);

has_dbms_java number;

has_dbms_debug number;

qu_version_owner user_objects.object_name%type;

CTRepositoryVersionMajor number;

CTRepositoryVersionMinor number;

CTRepositoryVersionRevision number;

CTRepositoryVersionBuild number;

is_RAC number;

instance_id number;

instance_name varchar2(100);

server_name varchar2(100);

team_coding_schema user_objects.object_name%type;

team_coding_table user_objects.object_name%type;

team_coding_records number;

team_coding_table_selectable number;

OS_USERNAME varchar2(60);

radix varchar2(1);

dbid varchar2(30);

authentication_type varchar2(30);

AmazonRDSPackage user_objects.object_name%type;

OraDBType number;

ContainerID number;

ContainerName varchar2(128);

AppRoot varchar2(3);

AppPDB varchar2(3);

Begin

– Put Toad data into v$session columns

Begin

SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, :IN_ID);

Exception

When others then null; – we can live without this.

End;

– Retrieve specific system privileges

select

sum(decode(Privilege, ‘ADMINISTER DATABASE TRIGGER’, 1, 0)) administer_database_trigger,

sum(decode(Privilege, ‘ADMINISTER RESOURCE MANAGER’, 1, 0)) administer_resource_manager,

sum(decode(Privilege, ‘ALTER ANY TABLE’, 1, 0)) alter_any_table,

sum(decode(Privilege, ‘ALTER DATABASE’, 1, 0)) alter_database,

sum(decode(Privilege, ‘ALTER SESSION’, 1, 0)) alter_session,

sum(decode(Privilege, ‘ALTER SYSTEM’, 1, 0)) alter_system,

sum(decode(Privilege, ‘ALTER USER’, 1, 0)) alter_user,

sum(decode(Privilege, ‘CREATE ANY INDEX’, 1, 0)) create_any_ind,

sum(decode(Privilege, ‘CREATE ANY SEQUENCE’, 1, 0)) create_any_seq,

sum(decode(Privilege, ‘CREATE ANY TABLE’, 1, 0)) create_any_table,

sum(decode(Privilege, ‘CREATE ANY TRIGGER’, 1, 0)) create_any_trig,

sum(decode(Privilege, ‘CREATE PUBLIC SYNONYM’, 1, 0)) create_public_syn,

sum(decode(Privilege, ‘CREATE ROLE’, 1, 0)) create_role,

sum(decode(Privilege, ‘CREATE SEQUENCE’, 1, 0)) create_sequence,

sum(decode(Privilege, ‘CREATE TABLE’, 1, 0)) create_table,

sum(decode(Privilege, ‘CREATE TRIGGER’, 1, 0)) create_trigger,

sum(decode(Privilege, ‘DEBUG ANY PROCEDURE’, 1, 0)) debug_any_proc,

sum(decode(Privilege, ‘DEBUG CONNECT SESSION’, 1, 0)) debug_connect_session,

sum(decode(Privilege, ‘EXECUTE ANY PROCEDURE’, 1, 0)) execute_any_procedure,

sum(decode(Privilege, ‘GRANT ANY OBJECT PRIVILEGE’, 1, 0)) grant_any_object_priv,

sum(decode(Privilege, ‘GRANT ANY ROLE’, 1, 0)) grant_any_role,

sum(decode(Privilege, ‘GRANT ANY PRIVILEGE’, 1, 0)) grant_any_privilege,

sum(decode(Privilege, ‘SELECT ANY DICTIONARY’, 1, 0)) select_any_dictionary,

sum(decode(Privilege, ‘SELECT ANY TABLE’, 1, 0)) select_any_table,

sum(decode(Privilege, ‘DELETE ANY TABLE’, 1, 0)) delete_any_table,

sum(decode(Privilege, ‘INSERT ANY TABLE’, 1, 0)) insert_table,

sum(decode(Privilege, ‘UPDATE ANY TABLE’, 1, 0)) update_any_table

into administer_database_trigger, administer_resource_manager, alter_any_table, alter_database,

alter_session, alter_system, alter_user, create_any_index, create_any_sequence, create_any_table,

create_any_trigger, create_public_syn, create_role, create_sequence, create_table, create_trigger,

debug_any_procedure, debug_connect_session, execute_any_procedure, grant_any_object_priv,

grant_any_role, grant_any_privilege, select_any_dictionary, select_any_table, delete_any_table,

insert_any_table, update_any_table

from sys.session_privs

where privilege in (‘DEBUG CONNECT SESSION’,

‘ALTER ANY TABLE’,

‘ALTER SESSION’,

‘ALTER SYSTEM’,

‘ALTER DATABASE’,

‘ALTER USER’,

‘ADMINISTER DATABASE TRIGGER’,

‘ADMINISTER RESOURCE MANAGER’,

‘CREATE TABLE’,

‘CREATE ROLE’,

‘CREATE TRIGGER’,

‘CREATE SEQUENCE’,

‘EXECUTE ANY PROCEDURE’,

‘SELECT ANY TABLE’,

‘DELETE ANY TABLE’,

‘INSERT ANY TABLE’,

‘UPDATE ANY TABLE’,

‘GRANT ANY OBJECT PRIVILEGE’,

‘GRANT ANY ROLE’,

‘GRANT ANY PRIVILEGE’,

‘SELECT ANY DICTIONARY’,

‘CREATE ANY INDEX’,

‘CREATE ANY SEQUENCE’,

‘CREATE ANY TABLE’,

‘CREATE ANY TRIGGER’,

‘DEBUG ANY PROCEDURE’,

‘CREATE PUBLIC SYNONYM’);

– Check for SELECT_CATALOG_ROLE. It must be a default role to appear in the result of this query.

Select

sum(decode(role, ‘SELECT_CATALOG_ROLE’, 1, 0)) select_catalog_role

into select_catalog_role

from sys.session_roles

where role = ‘SELECT_CATALOG_ROLE’;

– determine database character set

select value into db_charset from sys.nls_database_parameters where parameter = ‘NLS_CHARACTERSET’;

– check for other ways dba_objects and dba_synonyms could be granted

if (select_any_dictionary = 1) or (select_catalog_role = 1) then

has_dba_synonyms := 1;

has_dba_objects := 1;

else

– just try to select from dba_synonyms and dba_objects as a privilege check

begin

qry_str := ‘select 1 from DBA_SYNONYMS where rownum = 1’;

execute immediate qry_str into has_dba_synonyms;

exception

when others then

has_dba_synonyms := 0;

end;

begin

qry_str := ‘select 1 from DBA_OBJECTS where rownum = 1’;

execute immediate qry_str into has_dba_objects;

exception

when others then

has_dba_objects := 0;

end;

end if;

– retrieve OS Username

begin

qry_str := ‘select SYS_CONTEXT(’‘USERENV’’,’‘OS_USER’’) from dual’;

execute immediate qry_str into OS_USERNAME;

exception

when others then

OS_USERNAME := null;

end;

– retrieve SID if possible

begin

qry_str := ‘select SYS_CONTEXT(’‘USERENV’’,’‘SID’’) from dual’;

execute immediate qry_str into sid;

exception

when others then

sid := -1;

end;

– get compatible version

begin

qry_str := ‘select value from v$parameter where name = ‘‘compatible’’’;

execute immediate qry_str into compatible_version;

exception

when others then

compatible_version := null;

end;

– check for privileges on sys.dbms_transaction

begin

execute immediate ‘select sys.dbms_transaction.local_transaction_id from dual’ into local_trans_id;

exception

when others then

local_trans_id := ‘’;

end;

– determine if certain packages are installed

qry_str := ‘Select sum(decode(object_name, ‘‘DBMS_JAVA’’, 1, 0)) jv,’ ||

’ sum(decode(object_name, ‘‘DBMS_DEBUG’’, 1, 0)) dbg ';

if has_dba_objects > 0 then

qry_str := qry_str || 'from sys.dba_objects ';

else

qry_str := qry_str || 'from sys.all_objects ';

end if;

qry_str := qry_str || 'where owner = ‘‘SYS’’ ’ ||

'and object_type = ‘‘PACKAGE’’ ’ ||

‘and object_name in (’‘DBMS_JAVA’’, ‘‘DBMS_DEBUG’’)’;

execute immediate qry_str into has_dbms_java, has_dbms_debug;

– determine if code tester repository is installed

begin

Select user into qu_version_owner

FROM sys.user_objects

WHERE object_name = ‘QU_VERSION’

AND object_type in (‘PACKAGE’, ‘SYNONYM’)

AND rownum = 1;

exception

when no_data_found then

qu_version_owner := null;

end;

– if not found then look in other schemas

if qu_version_owner is null then

begin

qry_str := 'Select owner ';

if has_dba_objects > 0 then

qry_str := qry_str || 'from sys.dba_objects ';

else

qry_str := qry_str || 'from sys.all_objects ';

end if;

qry_str := qry_str || 'where object_name = ‘‘QU_VERSION’’ ’ ||

‘and object_type in (’‘PACKAGE’’,’‘SYNONYM’’) and rownum = 1’;

execute immediate qry_str into qu_version_owner;

exception

when others then

qu_version_owner := null;

end;

end if;

– if it is installed, determine code tester version

CTRepositoryVersionMajor := -1;

CTRepositoryVersionMinor := -1;

CTRepositoryVersionRevision := -1;

CTRepositoryVersionBuild := -1;

if qu_version_owner is not null then

begin

qry_str := 'select QU_VERSION.major_number, QU_VERSION.minor_number, QU_VERSION.revision_number, ’ ||

‘QU_VERSION.build_number from dual’;

execute immediate qry_str into CTRepositoryVersionMajor, CTRepositoryVersionMinor, CTRepositoryVersionRevision, CTRepositoryVersionBuild;

exception

when others then

null;

end;

end if;

– find the team coding schema, if any

begin

– Query by the synonym name to see if team coding exists and the user has access to it.

qry_str := ‘select COUNT(*) from DELL_COM_TEAM_CODING’;

execute immediate qry_str into team_coding_records;

team_coding_table_selectable := 1;

– If successful, get the team coding schema and table

qry_str := 'Select table_owner, table_name ';

if has_dba_synonyms > 0 then

qry_str := qry_str || 'from sys.dba_synonyms ';

else

qry_str := qry_str || 'from sys.all_synonyms ';

end if;

qry_str := qry_str || 'where synonym_name = ‘‘DELL_COM_TEAM_CODING’’ and owner = ‘‘PUBLIC’’ ';

execute immediate qry_str into team_coding_schema, team_coding_table;

exception

when others then

team_coding_schema := null;

team_coding_table := null;

team_coding_table_selectable := 0;

end;

– determine if this is a RAC instance

qry_str := ‘begin if dbms_utility.is_cluster_database’;

qry_str := qry_str || ’ then :isRAC := 1; else :isRAC := 0; end if; end;’;

– determine if this is a RAC DB

begin

execute immediate qry_str using out is_RAC;

exception

when others then

:isRAC := 0;

end;

– determine instance id

begin

execute immediate ‘select dbms_utility.current_instance from dual’ into instance_id;

exception

when others then

instance_id := 1;

end;

– determine instance name and server name if possible

qry_str := ‘select sys_context(’‘USERENV’’,’‘INSTANCE_NAME’’), sys_context(’‘USERENV’’,’‘SERVER_HOST’’) from dual’;

begin

execute immediate qry_str into instance_name, server_name;

exception

– if we don’t have privs on v$instance and not rac, then try to get db name

when others then

if is_rac = 0 then

begin

qry_str := ‘select sys_context(’‘USERENV’’,’‘DB_NAME’’) from dual’;

execute immediate qry_str into instance_name;

exception

when others then

instance_name := null;

end;

end if;

end;

– get timestamp radix

begin

qry_str := ‘select to_char(systimestamp, ‘‘X’’ ) from dual’;

execute immediate qry_str into radix;

exception

when others then

radix := ‘.’;

end;

– determine DBID if possible

begin

qry_str := ‘select dbid from v$database’;

execute immediate qry_str into dbid;

exception

when others then

null;

end;

– get authentication type

begin

qry_str := ‘Select SYS_CONTEXT(’‘USERENV’’, ‘‘IDENTIFICATION_TYPE’’) from dual’;

execute immediate qry_str into authentication_type;

exception

when others then

authentication_type := null;

end;

– Is this an Amazon RDS Instance?

begin

execute immediate ‘select object_name from sys.all_procedures where owner = ‘‘RDSADMIN’’ and object_name = ‘‘RDSADMIN_UTIL’’ and object_type = ‘‘PACKAGE’’ and subprogram_id = 0’ into AmazonRDSPackage;

exception

when others then

AmazonRDSPackage := null;

end;

– What kind of database is this? (pluggable, container, normal)

begin

qry_str := ‘select’ || CHR(13) || CHR(10) ||

’ TO_NUMBER(sys_context(’‘USERENV’’,’‘CON_ID’’)),’ || CHR(13) || CHR(10) ||

’ sys_context(’‘USERENV’’,’‘CON_NAME’’),’ || CHR(13) || CHR(10) ||

’ Case’ || CHR(13) || CHR(10) ||

’ when sys_context(’‘USERENV’’,’‘CON_NAME’’) = ‘‘CDB$ROOT’’ then 1 – container,’ || CHR(13) || CHR(10) ||

’ when sys_context(’‘USERENV’’,’‘CDB_NAME’’) is null then 0 – normal,’ || CHR(13) || CHR(10) ||

’ else 2 – pluggable’ || CHR(13) || CHR(10) ||

’ end db_type’ || CHR(13) || CHR(10) ||

‘from dual’;

execute immediate qry_str into ContainerID, ContainerName, OraDBType;

if OraDBType=0 then

ContainerName := null;

end if;

exception

when others then

ContainerID := -1;

ContainerName := null;

OraDBType := 0;

end;

if OraDBType=2 then

– If this is a pluggable db, then check for application container or application pluggable.

– If user does not have the SELECT privilege on V$CONTAINERS, then we could be connected to

– and application PDB (or root) and not know it.

begin

qry_str := ‘select application_root as app_root, application_pdb as app_pdb’ || CHR(13) || CHR(10) ||

‘from v$containers’ || CHR(13) || CHR(10) ||

‘where name = sys_context(’‘USERENV’’, ‘‘CON_NAME’’)’;

execute immediate qry_str into AppRoot, AppPDB;

if AppRoot = ‘YES’ then

OraDBType := 3;

elsif AppPDB = ‘YES’ then

OraDBType := 4;

end if;

exception

when others then

null;

end;

end if;

– Assign results to output variables

:OUT_DEBUG_CONNECT_SESSION := debug_connect_session;

:OUT_ALTER_ANY_TABLE := alter_any_table;

:OUT_ALTER_SESSION := alter_session;

:OUT_ALTER_SYSTEM := alter_system;

:OUT_ALTER_DATABASE := alter_database;

:OUT_ALTER_USER := alter_user;

:OUT_CREATE_TABLE := create_table;

:OUT_CREATE_ROLE := create_role;

:OUT_CREATE_TRIGGER := create_trigger;

:OUT_CREATE_SEQUENCE := create_sequence;

:OUT_ADMIN_DB_TRIGGER := administer_database_trigger;

:OUT_ADMIN_RS_MAN := administer_resource_manager;

:OUT_EXECUTE_ANY_PROCEDURE := execute_any_procedure;

:OUT_CREATE_ANY_INDEX := create_any_index;

:OUT_CREATE_ANY_SEQUENCE := create_any_sequence;

:OUT_CREATE_ANY_TABLE := create_any_table;

:OUT_CREATE_ANY_TRIGGER := create_any_trigger;

:OUT_DEBUG_ANY_PROCEDURE := debug_any_procedure;

:OUT_SELECT_ANY_TABLE := select_any_table;

:OUT_DELETE_ANY_TABLE := delete_any_table;

:OUT_INSERT_ANY_TABLE := insert_any_table;

:OUT_UPDATE_ANY_TABLE := update_any_table;

:OUT_GRANT_ANY_OBJECT_PRIV := grant_any_object_priv;

:OUT_GRANT_ANY_ROLE := grant_any_role;

:OUT_GRANT_ANY_PRIVILEGE := grant_any_privilege;

:OUT_SELECT_ANY_DICTIONARY := select_any_dictionary;

:OUT_CREATE_PUBLIC_SYN := create_public_syn;

:OUT_SELECT_CATALOG_ROLE := select_catalog_role;

:OUT_USER := user;

:OUT_DB_CHARSET := db_charset;

:OUT_OSUSERNAME := OS_USERNAME;

:OUT_COMPATIBLE_VERSION := compatible_version;

:OUT_TEAM_CODING_SCHEMA := team_coding_schema;

:OUT_TEAM_CODING_TABLE := team_coding_table;

:OUT_TC_TABLE_SELECTABLE := team_coding_table_selectable;

:OUT_SID := sid;

:OUT_LOCAL_TRANS_ID := local_trans_id;

:OUT_HAS_DBMS_JAVA := has_dbms_java;

:OUT_HAS_DBMS_DEBUG := has_dbms_debug;

:OUT_QU_VERSION_OWNER := qu_version_owner;

:OUT_QU_VERSION_MAJOR := CTRepositoryVersionMajor;

:OUT_QU_VERSION_MINOR := CTRepositoryVersionMinor;

:OUT_QU_VERSION_REVISION := CTRepositoryVersionRevision;

:OUT_QU_VERSION_BUILD := CTRepositoryVersionBuild;

:OUT_IS_RAC := is_rac;

:OUT_INSTANCE_ID := instance_id;

:OUT_INSTANCE_NAME := instance_name;

:OUT_SERVER_NAME := server_name;

:OUT_DBID := dbid;

:OUT_RADIX := radix;

:OUT_AUTHENTICATION_TYPE := authentication_type;

:OUT_IS_AMAZON_RDS := AmazonRDSPackage;

:OUT_DB_TYPE := OraDBType;

:OUT_CONTAINER_ID := ContainerID;

:OUT_CONTAINER_NAME := ContainerName;

end;

:IN_MDL(VARCHAR[14],IN)=‘TOAD 13.0.0.80’

:IN_ID(VARCHAR[27],IN)=‘32470848,234493248,54996384’

:isRAC(Unknown,IN)=

:OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=

:OUT_ALTER_ANY_TABLE(INTEGER,OUT)=

:OUT_ALTER_SESSION(INTEGER,OUT)=

:OUT_ALTER_SYSTEM(INTEGER,OUT)=

:OUT_ALTER_DATABASE(INTEGER,OUT)=

:OUT_ALTER_USER(INTEGER,OUT)=

:OUT_CREATE_TABLE(INTEGER,OUT)=

:OUT_CREATE_ROLE(INTEGER,OUT)=

:OUT_CREATE_TRIGGER(INTEGER,OUT)=

:OUT_CREATE_SEQUENCE(INTEGER,OUT)=

:OUT_ADMIN_DB_TRIGGER(INTEGER,OUT)=

:OUT_ADMIN_RS_MAN(INTEGER,OUT)=

:OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=

:OUT_CREATE_ANY_INDEX(INTEGER,OUT)=

:OUT_CREATE_ANY_SEQUENCE(INTEGER,OUT)=

:OUT_CREATE_ANY_TABLE(INTEGER,OUT)=

:OUT_CREATE_ANY_TRIGGER(INTEGER,OUT)=

:OUT_DEBUG_ANY_PROCEDURE(INTEGER,OUT)=

:OUT_SELECT_ANY_TABLE(INTEGER,OUT)=

:OUT_DELETE_ANY_TABLE(INTEGER,OUT)=

:OUT_INSERT_ANY_TABLE(INTEGER,OUT)=

:OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=

:OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=

:OUT_GRANT_ANY_ROLE(INTEGER,OUT)=

:OUT_GRANT_ANY_PRIVILEGE(INTEGER,OUT)=

:OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=

:OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=

:OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)=

:OUT_USER(VARCHAR[0],OUT)=

:OUT_DB_CHARSET(VARCHAR[0],OUT)=

:OUT_OSUSERNAME(VARCHAR[0],OUT)=

:OUT_COMPATIBLE_VERSION(VARCHAR[0],OUT)=

:OUT_TEAM_CODING_SCHEMA(VARCHAR[0],OUT)=

:OUT_TEAM_CODING_TABLE(VARCHAR[0],OUT)=

:OUT_TC_TABLE_SELECTABLE(INTEGER,OUT)=

:OUT_SID(VARCHAR[0],OUT)=

:OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)=

:OUT_HAS_DBMS_JAVA(INTEGER,OUT)=

:OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=

:OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)=

:OUT_QU_VERSION_MAJOR(INTEGER,OUT)=

:OUT_QU_VERSION_MINOR(INTEGER,OUT)=

:OUT_QU_VERSION_REVISION(INTEGER,OUT)=

:OUT_QU_VERSION_BUILD(INTEGER,OUT)=

:OUT_IS_RAC(INTEGER,OUT)=

:OUT_INSTANCE_ID(INTEGER,OUT)=

:OUT_INSTANCE_NAME(VARCHAR[0],OUT)=

:OUT_SERVER_NAME(VARCHAR[0],OUT)=

:OUT_DBID(VARCHAR[0],OUT)=

:OUT_RADIX(VARCHAR[0],OUT)=

:OUT_AUTHENTICATION_TYPE(VARCHAR[0],OUT)=

:OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)=

:OUT_DB_TYPE(INTEGER,OUT)=

:OUT_CONTAINER_ID(INTEGER,OUT)=

:OUT_CONTAINER_NAME(VARCHAR[0],OUT)=


– Session: SYS@PTIA1000

– Timestamp: 00:01:56.124

BEGIN

SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, :IN_ID);

END;

:IN_MDL(VARCHAR[14])=‘TOAD 13.0.0.80’

:IN_ID(VARCHAR[27])=‘32470848,234493248,54996384’


– Session: SYS@PTIA1000

– Timestamp: 00:01:56.147

SELECT u.NAME

FROM sys.USER$ u

WHERE u.TYPE# = 1

ORDER BY 1;


– Session: SYS@PTIA1000

– Timestamp: 00:01:56.200

DECLARE

TYPE tc_syn_type IS VARRAY (2) OF VARCHAR2 (30);

syns CONSTANT tc_syn_type := tc_syn_type (‘QUEST_COM_TEAM_CODING’, ‘SQLNAV4_VCS_CONFIG’);

local_owner VARCHAR2 (30);

local_table VARCHAR2 (30);

local_options VARCHAR2 (16);

local_version VARCHAR2 (16);

BEGIN

local_owner := NULL;

local_table := NULL;

local_options := NULL;

local_version := NULL;

– Look for Team Coding synonym

FOR i IN syns.FIRST … syns.LAST

LOOP

BEGIN

– Query by the synonym name to see if the underlying table exists

EXECUTE IMMEDIATE 'SELECT options, version FROM ’ || syns(i)

INTO local_options, local_version;

– If successful, get the team coding schema and table

Select table_owner, table_name

INTO local_owner, local_table

FROM sys.DBA_SYNONYMS

WHERE owner = ‘PUBLIC’ AND synonym_name = syns(i);

EXIT;

EXCEPTION

WHEN OTHERS THEN

NULL; – we still want to check the other table if we’re on the first of two.

END;

END LOOP;

– Populate out vars

IF local_owner IS NOT NULL AND local_table IS NOT NULL THEN

:out_res := 1;

ELSE

:out_res := 0;

END IF;

:out_owner := local_owner;

:out_table := local_table;

:out_version := local_version;

:out_options := local_options;

END;

:out_res(INTEGER,OUT)=

:out_owner(VARCHAR[0],OUT)=

:out_table(VARCHAR[0],OUT)=

:out_version(VARCHAR[0],OUT)=

:out_options(VARCHAR[0],OUT)=


– Session: SYS@PTIA1000

– Timestamp: 00:01:56.315

select 1 from v$cell where rownum = 1;


– Session: SYS@PTIA1000

– Timestamp: 00:01:58.361

DECLARE

TYPE table_names IS VARRAY (3) OF VARCHAR2 (30);

currval_not_defined EXCEPTION;

PRAGMA EXCEPTION_INIT (currval_not_defined, -8002);

sys_prof_valid NUMBER;

toad_prof_valid NUMBER;

rst NUMBER;

sys_pack VARCHAR2 (17);

prof_seq VARCHAR2 (24);

tables table_names;

dbms_prof_tables CONSTANT table_names

:= table_names (‘plsql_profiler_data’,

‘plsql_profiler_runs’,

‘plsql_profiler_units’) ;

dbms_hprof_tables CONSTANT table_names

:= table_names (‘dbmshp_runs’,

‘dbmshp_function_info’,

‘dbmshp_parent_child_info’) ;

/*---------------------------------------------------------------------------

– Verify that the specified package exists

---------------------------------------------------------------------------*/

FUNCTION CheckPackage (ObjectName IN VARCHAR2)

RETURN NUMBER

AS

valid NUMBER;

err_msg VARCHAR2 (2000);

BEGIN

valid := 1;

BEGIN

EXECUTE IMMEDIATE 'BEGIN ’ || ObjectName || ‘.“11380939675234969121428629266”; END;’;

EXCEPTION

WHEN OTHERS

THEN

err_msg := SQLERRM;

– If error is for missing package member then we are OK

IF INSTR (err_msg, ‘PLS-00302’) > 0

THEN

valid := 1;


#9

what does

select dbms_utility.current_instance from dual

give you?


#10

It get me a “1”

2018-05-12 0:32 GMT+02:00 John Dorlon bounce-jdorlon@toadworld.com:

RE: Toad v13 - parallel queries

Reply by John Dorlon
what does

select dbms_utility.current_instance from dual

give you?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Mette Stephansen

Miracle Expert ServicesMiracle A/S
Borupvang 2C, 2750 Ballerup

53 74 72 26


#11

Hi John

Are you able to reproduce ? Or do you need anything else from me ?

best regards Mette


#12

Hi Mette,

Sorry for the delay. I was hoping you’d say something other than ‘1’ for last question. :slight_smile:

I’ve make a parallel query, found it in the Session Browser, and click on one of the parallel slaves, everything works as expected. Which tab is selected on the top/right? (Session, Process, IO, Waits, etc)

-John


#13

I am not at work now - but I think it was explain plan and also long obs

Reg Mette

Sendt fra en telefon med stave- slå- og andre fejl !

Den 14. maj 2018 kl. 21.16 skrev John Dorlon bounce-jdorlon@toadworld.com:

RE: Toad v13 - parallel queries

Reply by John Dorlon
Hi Mette,

Sorry for the delay. I was hoping you’d say something other than ‘1’ for last question. :slight_smile:

I’ve make a parallel query, found it in the Session Browser, and click on one of the parallel slaves, everything works as expected. Which tab is selected on the top/right? (Session, Process, IO, Waits, etc)

-John

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


#14

OK, I can reproduce it now! Thanks.


#15

Great !

Sendt fra en telefon med stave- slå- og andre fejl !

Den 14. maj 2018 kl. 21.37 skrev John Dorlon bounce-jdorlon@toadworld.com:

RE: Toad v13 - parallel queries

Reply by John Dorlon
OK, I can reproduce it now! Thanks.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


#16

It should be fixed in the 1st or 2nd beta for 13.1.


#17

Looking forward - im troubleshooting parallell queey these says

Sendt fra en telefon med stave- slå- og andre fejl !

Den 14. maj 2018 kl. 21.54 skrev John Dorlon bounce-jdorlon@toadworld.com:

RE: Toad v13 - parallel queries

Reply by John Dorlon
It should be fixed in the 1st or 2nd beta for 13.1.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.