---------------------------------- -- Timestamp: 16:11:37.565 Connect: FX_SHANKAR_A@DEV11G ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:38.174 SELECT version, product, sysdate, null FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE '%ORACLE%'; -- Elapsed Time: 0.015 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:38.190 Declare Toad_Security_Exists number; 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; drop_any_sequence 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; utplsql_ver VARCHAR2 (60); utplsql_own sys.all_objects.owner%TYPE; e_inherit_privs EXCEPTION; PRAGMA EXCEPTION_INIT(e_inherit_privs, -6598); compatible_version varchar2(60); cant_access_vparameter integer; sid number; qry_str varchar2(4000); has_dbms_java number; has_dbms_debug number; local_trans_id varchar2(60); qu_version_owner user_objects.object_name%type; CTRepositoryVersionMajor number; CTRepositoryVersionMinor number; CTRepositoryVersionRevision number; CTRepositoryVersionBuild number; instance_id number; instance_name varchar2(100); server_name varchar2(100); OS_USERNAME varchar2(60); radix varchar2(1); dbid varchar2(30); dbname varchar2(9); authentication_type varchar2(30); IsExadata number; AmazonRDSPackage user_objects.object_name%type; cx_table_count number; -- -- swap out ALL_ views for DBA_ views if possible function check_for_dba(in_str in varchar2) return varchar2 is result varchar2(4000); begin result := in_str; if has_dba_objects > 0 then result := REPLACE (result, 'all_objects', 'dba_objects'); end if; if has_dba_synonyms > 0 then result := REPLACE (result, 'all_synonyms', 'dba_synonyms'); end if; return result; end; 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, 'DROP ANY SEQUENCE', 1, 0)) drop_any_seq, 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, drop_any_sequence, 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', 'DROP ANY SEQUENCE', '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; -- -- Is Toad Security in effect? begin qry_str := 'Select 1 from toad.toad_restrictions where (user_name = USER or user_name in ( select ROLE from sys.session_roles)) and rownum = 1'; execute immediate qry_str into Toad_Security_Exists; exception when others then Toad_Security_Exists := 0; end; -- -- 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 cant_access_vparameter := 0; qry_str := 'select value from v$parameter where name = ''compatible'''; execute immediate qry_str into compatible_version; exception when others then compatible_version := null; cant_access_vparameter := 1; end; -- -- check for privileges on sys.dbms_transaction begin qry_str := 'select sys.dbms_transaction.local_transaction_id from dual'; execute immediate qry_str 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 from sys.all_objects where owner = ''SYS'' and object_type = ''PACKAGE'' and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')'; qry_str := check_for_dba(qry_str); 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 from sys.all_objects where object_name = ''QU_VERSION'' and object_type in (''PACKAGE'',''SYNONYM'') and rownum = 1'; qry_str := check_for_dba(qry_str); 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; -- -- determine if this is a RAC instance qry_str := 'begin if dbms_utility.is_cluster_database then :OUT_IS_RAC := 1; else :OUT_IS_RAC := 0; end if; end;'; begin execute immediate qry_str using out :OUT_IS_RAC; exception when others then :OUT_IS_RAC := 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 :OUT_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, DBName if possible begin qry_str := 'select dbid, name from v$database'; execute immediate qry_str into dbid, dbname; 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 qry_str := 'select object_name from sys.all_procedures where owner = ''RDSADMIN'' and object_name = ''RDSADMIN_UTIL'' and object_type = ''PACKAGE'' and subprogram_id = 0'; execute immediate qry_str into AmazonRDSPackage; exception when others then AmazonRDSPackage := null; end; -- -- Is this an Exadata Instance? begin qry_str := 'select 1 from v$cell where rownum = 1'; execute immediate qry_str into IsExadata; exception when others then IsExaData := 0; end; -- -- check for existence of Codexpert tables Select count('x') into cx_table_count from sys.all_tables where owner in ('TOAD', USER) and table_name in ('CX_XPERTRUN', 'CX_XPERTLINES', 'CX_XPERTITEMS', 'CX_TYPES', 'CX_SEVERITYS', 'CX_SETRULES', 'CX_RULESETS', 'CX_METRICS', 'CX_METRICRANGES', 'CX_CATEGORYS', 'CX_BASERULES'); -- -- determine if utPLSQL is installed utplsql_ver := NULL; BEGIN -- Fetch version using the public API EXECUTE IMMEDIATE 'SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL' INTO utplsql_ver; EXCEPTION -- Attempt direct access on ORA-06598 WHEN e_inherit_privs THEN BEGIN qry_str := 'SELECT do.owner FROM sys.all_objects do JOIN sys.all_synonyms ds ON (do.owner = ds.table_owner) AND (do.object_name = ds.table_name) WHERE ds.owner = ''PUBLIC'' AND ds.table_name = ''UT'' AND ds.synonym_name = ''UT'' AND do.object_type = ''PACKAGE'''; qry_str := check_for_dba(qry_str); EXECUTE IMMEDIATE qry_str INTO utplsql_own; qry_str := 'begin :utplsql_ver := ' || utplsql_own || '.ut_utils.gc_version; end;'; EXECUTE IMMEDIATE qry_str USING OUT utplsql_ver; EXCEPTION WHEN OTHERS THEN utplsql_ver := SQLCODE; END; WHEN OTHERS THEN utplsql_ver := SQLCODE; END; -- -- Done with SQL. 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_DROP_ANY_SEQUENCE := drop_any_sequence; :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_DB_CHARSET := db_charset; :OUT_USER := user; :OUT_CX_TABLE_COUNT := cx_table_count; :OUT_OSUSERNAME := OS_USERNAME; :OUT_COMPATIBLE_VERSION := compatible_version; :OUT_CANT_ACCESS_PARAMETER := cant_access_vparameter; :OUT_SID := sid; :OUT_TOAD_SEC_EXISTS := Toad_Security_Exists; :OUT_UTPLSQL_VER := utplsql_ver; :OUT_HAS_DBMS_JAVA := has_dbms_java; :OUT_HAS_DBMS_DEBUG := has_dbms_debug; :OUT_LOCAL_TRANS_ID := local_trans_id; :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_INSTANCE_ID := instance_id; :OUT_INSTANCE_NAME := instance_name; :OUT_SERVER_NAME := server_name; :OUT_DBID := dbid; :OUT_DBName := dbname; :OUT_RADIX := radix; :OUT_AUTHENTICATION_TYPE := authentication_type; :OUT_IS_EXADATA := IsExadata; :OUT_IS_AMAZON_RDS := AmazonRDSPackage; end; :IN_MDL(VARCHAR[21],IN)='TOAD Beta 14.0.25.592' :IN_ID(VARCHAR[29],IN)='57563728,1030704400,661332176' :OUT_IS_RAC(INTEGER,OUT)=1 :OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=1 :OUT_ALTER_ANY_TABLE(INTEGER,OUT)=0 :OUT_ALTER_SESSION(INTEGER,OUT)=1 :OUT_ALTER_SYSTEM(INTEGER,OUT)=0 :OUT_ALTER_DATABASE(INTEGER,OUT)=0 :OUT_ALTER_USER(INTEGER,OUT)=0 :OUT_CREATE_TABLE(INTEGER,OUT)=1 :OUT_CREATE_ROLE(INTEGER,OUT)=0 :OUT_CREATE_TRIGGER(INTEGER,OUT)=1 :OUT_CREATE_SEQUENCE(INTEGER,OUT)=1 :OUT_ADMIN_DB_TRIGGER(INTEGER,OUT)=0 :OUT_ADMIN_RS_MAN(INTEGER,OUT)=0 :OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=0 :OUT_CREATE_ANY_INDEX(INTEGER,OUT)=0 :OUT_CREATE_ANY_SEQUENCE(INTEGER,OUT)=0 :OUT_CREATE_ANY_TABLE(INTEGER,OUT)=0 :OUT_CREATE_ANY_TRIGGER(INTEGER,OUT)=0 :OUT_DROP_ANY_SEQUENCE(INTEGER,OUT)=0 :OUT_DEBUG_ANY_PROCEDURE(INTEGER,OUT)=1 :OUT_SELECT_ANY_TABLE(INTEGER,OUT)=0 :OUT_DELETE_ANY_TABLE(INTEGER,OUT)=0 :OUT_INSERT_ANY_TABLE(INTEGER,OUT)=0 :OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=0 :OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=0 :OUT_GRANT_ANY_ROLE(INTEGER,OUT)=0 :OUT_GRANT_ANY_PRIVILEGE(INTEGER,OUT)=0 :OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=0 :OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=0 :OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)= :OUT_DB_CHARSET(VARCHAR[8],OUT)='AL32UTF8' :OUT_USER(VARCHAR[12],OUT)='FX_SHANKAR_A' :OUT_CX_TABLE_COUNT(INTEGER,OUT)=0 :OUT_OSUSERNAME(VARCHAR[10],OUT)='mweinstock' :OUT_COMPATIBLE_VERSION(VARCHAR[0],OUT)= :OUT_CANT_ACCESS_PARAMETER(INTEGER,OUT)=1 :OUT_SID(VARCHAR[4],OUT)='1347' :OUT_TOAD_SEC_EXISTS(INTEGER,OUT)=0 :OUT_UTPLSQL_VER(VARCHAR[4],OUT)='-904' :OUT_HAS_DBMS_JAVA(INTEGER,OUT)=1 :OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=1 :OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)= :OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)= :OUT_QU_VERSION_MAJOR(INTEGER,OUT)=-1 :OUT_QU_VERSION_MINOR(INTEGER,OUT)=-1 :OUT_QU_VERSION_REVISION(INTEGER,OUT)=-1 :OUT_QU_VERSION_BUILD(INTEGER,OUT)=-1 :OUT_INSTANCE_ID(INTEGER,OUT)=3 :OUT_INSTANCE_NAME(VARCHAR[5],OUT)='trdd3' :OUT_SERVER_NAME(VARCHAR[10],OUT)='nylddbcl03' :OUT_DBID(VARCHAR[0],OUT)= :OUT_DBName(VARCHAR[0],OUT)= :OUT_RADIX(VARCHAR[1],OUT)='.' :OUT_AUTHENTICATION_TYPE(VARCHAR[5],OUT)='LOCAL' :OUT_IS_EXADATA(INTEGER,OUT)=0 :OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)= -- Elapsed Time: 0.554 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:38.744 Select feature from toad.toad_restrictions where user_name=USER or user_name in ( select ROLE from sys.session_roles); -- ORA-00942: table or view does not exist -- Elapsed Time: 0.378 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:39.177 select /*+ RULE */ TABLE_NAME from all_tab_privs where table_name like 'DBA_%' and table_schema = 'SYS' and privilege = 'SELECT'; -- Elapsed Time: 0.031 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:39.408 Select username from sys.ALL_USERS order by username; -- Elapsed Time: 0.015 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:39.462 Select synonym_name, table_owner, table_name from sys.all_synonyms where owner = 'PUBLIC' and synonym_name in ('QUEST_TEAM_CODING', 'DELL_COM_TEAM_CODING', 'QUEST_COM_TEAM_CODING', 'SQLNAV4_VCS_CONFIG'); -- Elapsed Time: 0.089 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:39.566 select 1 from DELL_COM_TEAM_CODING where rownum = 1; -- ORA-00980: synonym translation is no longer valid -- Elapsed Time: 0.398 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:40.776 SELECT VALUE FROM V$OPTION WHERE PARAMETER='Objects'; -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:41.208 Select t.table_name, user as owner, t.cluster_name, t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows, t.initial_extent, t.degree, t.logging, t.cache, t.buffer_pool , t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested , t.Compression, t.dropped , t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created , decode(nvl(tablespace_name, 'x') || upper(partitioned) || nvl(iot_type, 'x') || to_char(pct_free), 'xNOx0', 'YES', 'NO') as External from sys.user_all_tables t where 1=1 order by table_name; -- Elapsed Time: 0.318 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:41.526 Select user, table_name from sys.user_snapshots order by table_name; -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:41.526 Select user, object_name, created, last_ddl_time, object_id from sys.user_objects Where object_type = 'TABLE' order by object_name; -- Elapsed Time: 0.071 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:43.853 select banner from v$version; -- Elapsed Time: 0.016 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:46.344 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 || '."20600284624472132911876236441"; 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; ELSE valid := 0; END IF; END; RETURN valid; END; BEGIN IF :check_hierarchical = 0 THEN tables := dbms_prof_tables; sys_pack := 'sys.dbms_profiler'; prof_seq := 'plsql_profiler_runnumber'; ELSE tables := dbms_hprof_tables; sys_pack := 'sys.dbms_hprof'; prof_seq := 'dbmshp_runnumber'; END IF; -- Check for the SYS.DBMS_PROFILER package sys_prof_valid := CheckPackage (sys_pack); -- If it exists, check for the other profiler objects IF sys_prof_valid = 1 THEN toad_prof_valid := 1; BEGIN -- Check for all tables FOR i IN tables.FIRST .. tables.LAST LOOP BEGIN EXECUTE IMMEDIATE 'SELECT 1 FROM ' || tables (i) || ' WHERE 0 = 1' INTO rst; EXCEPTION WHEN NO_DATA_FOUND THEN toad_prof_valid := 1; WHEN OTHERS THEN RAISE; END; END LOOP; -- Check for the sequence BEGIN EXECUTE IMMEDIATE 'SELECT ' || prof_seq || '.currval FROM DUAL'; EXCEPTION WHEN currval_not_defined THEN toad_prof_valid := 1; WHEN OTHERS THEN RAISE; END; -- Check for the TOAD_PROFILER package IF :check_hierarchical <> 1 THEN toad_prof_valid := CheckPackage ('toad_profiler'); END IF; EXCEPTION WHEN OTHERS THEN toad_prof_valid := 0; END; ELSE toad_prof_valid := 0; END IF; :out_sys_prof_valid := sys_prof_valid; :out_toad_prof_valid := toad_prof_valid; END; :check_hierarchical(INTEGER,IN)=0 :out_sys_prof_valid(INTEGER,OUT)= :out_toad_prof_valid(INTEGER,OUT)= ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:11:48.079 select object_name nam, Decode(object_type, 'TABLE', 1, 'VIEW', 2, 3) typ from sys.user_objects where object_type in ('PROCEDURE', 'PACKAGE', 'FUNCTION') order by nam asc; -- Elapsed Time: 0.317 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:39.138 with PACKAGES as (Select :own owner, object_name, object_type, decode(status, 'VALID', 'V', 'I') status, last_ddl_time, object_id, created from sys.user_objects where 1=1 and object_type in ('PACKAGE', 'PACKAGE BODY')) SELECT PACKAGES.owner, PACKAGES.object_name, PACKAGES.object_type, PACKAGES.status, PACKAGES.last_ddl_time, PACKAGES.object_id, PACKAGES.created ,NVL(pi.AUTHID, 'DEFINER') AUTHID ,NVL(d.debuginfo, 'F') DEBUGINFO FROM PACKAGES ,(SELECT DISTINCT object_id, nvl(authid, 'DEFINER') authid FROM sys.user_procedures WHERE object_type = 'PACKAGE') pi ,(SELECT debuginfo, object_id FROM sys.all_probe_objects WHERE owner = :own AND object_type in ('PACKAGE', 'PACKAGE BODY')) d WHERE PACKAGES.object_id = pi.object_id (+) AND d.object_id (+) = PACKAGES.object_id order by 3, 2; :own(VARCHAR[12],IN)='FX_SHANKAR_A' -- Elapsed Time: 0.649 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:48.567 Select owner, object_name, object_type from sys.ALL_OBJECTS where object_name = :obj and object_type not like '%PARTITION%' and object_type <> 'UNDEFINED' and owner = :own union all Select owner, constraint_name as object_name, 'CONSTRAINT' as object_type from sys.ALL_CONSTRAINTS where constraint_name = :obj and owner = :own order by 3; :obj(VARCHAR[18],)='SELECT_TRADE_BY_ID' :own(VARCHAR[7],IN)='P_TRADE' -- Elapsed Time: 0.015 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:48.582 select owner, index_name object_name, 'INDEX' object_type from sys.all_indexes where index_name = :obj and owner = :own union all select owner, name object_name, 'QUEUE' object_type from sys.all_queues where name = :obj and owner = :own and owner = :own union all select owner, trigger_name object_name, 'TRIGGER' object_type from sys.all_triggers where trigger_name = :obj and owner = :own union all select owner, queue_table object_name, 'QUEUE TABLE' object_type from sys.all_queue_tables where queue_table = :obj and owner = :own union all select owner, mview_name object_name, 'MATERIALIZED VIEW' object_type from sys.all_mviews where mview_name = :obj and owner = :own union all select owner, synonym_name object_name, 'SYNONYM' object_type from sys.all_synonyms where synonym_name = :obj and owner = :own order by 3; :obj(VARCHAR[18],)='SELECT_TRADE_BY_ID' :own(VARCHAR[7],IN)='P_TRADE' -- Elapsed Time: 0.03 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:48.836 Select user as owner, object_name, object_type from sys.user_objects where object_name = :obj and object_type not like '%PARTITION%' and object_type <> 'UNDEFINED' union all Select owner, constraint_name as object_name, 'CONSTRAINT' as object_type from sys.user_constraints where owner = :own and constraint_name = :obj order by 3; :obj(VARCHAR[7],)='P_TRADE' :own(Unknown,)= -- Elapsed Time: 0.015 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:48.852 Select created, last_ddl_time, object_id, status from sys.user_objects where object_name = :nm and object_type = :t; :nm(VARCHAR[7],IN)='P_TRADE' :t(VARCHAR[12],IN)='PACKAGE BODY' -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:48.852 Select last_ddl_time FROM sys.USER_OBJECTS WHERE object_type = :type AND object_name = :name; :TYPE(VARCHAR[12],IN)='PACKAGE BODY' :name(VARCHAR[7],IN)='P_TRADE' -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:48.852 Select TEXT from SYS.USER_SOURCE where NAME=:name and TYPE=:type order by LINE; :name(VARCHAR[7],)='P_TRADE' :TYPE(VARCHAR[12],)='PACKAGE BODY' -- Elapsed Time: 0.146 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:50.087 Select created, last_ddl_time, object_id, status from sys.user_objects where object_name = :nm and object_type = :t; :nm(VARCHAR[7],IN)='P_TRADE' :t(VARCHAR[12],IN)='PACKAGE BODY' -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:51.173 Select TEXT from SYS.USER_SOURCE where NAME=:name and TYPE=:type order by LINE; :name(VARCHAR[7],)='P_TRADE' :TYPE(VARCHAR[12],)='PACKAGE BODY' -- Elapsed Time: 0.032 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:13:51.373 Select last_ddl_time FROM sys.USER_OBJECTS WHERE object_type = :type AND object_name = :name; :TYPE(VARCHAR[12],IN)='PACKAGE BODY' :name(VARCHAR[7],IN)='P_TRADE' -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:16:06.725 Select created, last_ddl_time, object_id, status from sys.user_objects where object_name = :nm and object_type = :t; :nm(VARCHAR[7],IN)='P_TRADE' :t(VARCHAR[12],IN)='PACKAGE BODY' -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:16:06.772 Select created, last_ddl_time, object_id, status from sys.ALL_OBJECTS where object_name = :nm and owner = :o and object_type = :t; :nm(VARCHAR[7],IN)='P_TRADE' :o(VARCHAR[6],IN)='MARC_B' :t(VARCHAR[12],IN)='PACKAGE BODY' -- Elapsed Time: 0.015 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:17:13.654 SELECT BUSINESS_ID FROM trade_state_irs_view; -- Elapsed Time: 0.278 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:17:21.019 SELECT BUSINESS_ID FROM trade_state_view; -- Elapsed Time: 0.085 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:08.924 BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END; ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:09.194 select PARAMETER,VALUE from nls_session_parameters where PARAMETER in('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT','NLS_CURRENCY'); -- Elapsed Time: 0.115 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:09.394 select to_char(9,'9C') from dual; -- Elapsed Time: 0 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:09.457 SELECT distinct BUSINESS_ID FROM trade_state_irs_view WHERE trade_id = ptrade_id; -- ORA-00904: "PTRADE_ID": invalid identifier -- Elapsed Time: 0.039 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:11.404 SELECT distinct BUSINESS_ID FROM trade_state_view WHERE trade_id = ptrade_id; -- ORA-00904: "PTRADE_ID": invalid identifier -- Elapsed Time: 0.046 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:13.446 DECLARE LogData clob; BEGIN dbms_lob.CreateTemporary(lob_loc=>LogData, cache=>true, dur=>dbms_lob.call); dbms_lob.freetemporary(LogData); END; -- Elapsed Time: 0.01 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:13.457 DECLARE v_lines SYS.DBMS_OUTPUT.chararr; v_line VARCHAR2 (32767); v_clob CLOB; v_cnt NUMBER; v_output VARCHAR2 (32767); PROCEDURE write_to_clob (p_clob IN OUT CLOB, p_str IN OUT VARCHAR2) AS BEGIN BEGIN IF p_str IS NOT NULL THEN DBMS_LOB.writeappend (p_clob, LENGTH (p_str), p_str); END IF; EXCEPTION WHEN VALUE_ERROR THEN NULL; END; p_str := NULL; END; BEGIN v_cnt := :inOutCnt; SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt); DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session); DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite); v_output := NULL; FOR line IN 1 .. v_lines.COUNT LOOP IF :trim_line = 1 THEN v_line := TRIM (v_lines (line)); ELSE v_line := v_lines (line); END IF; IF v_line IS NOT NULL THEN v_line := REPLACE (v_line, CHR (0)); IF LENGTHB (v_output) + LENGTHB (v_line) > 32767 THEN write_to_clob (v_clob, v_output); END IF; v_output := v_output || v_line; IF line < v_lines.COUNT THEN IF LENGTHB (v_output) + 2 > 32767 THEN write_to_clob (v_clob, v_output); END IF; v_output := v_output || CHR (13) || CHR (10); END IF; END IF; END LOOP; write_to_clob (v_clob, v_output); if v_cnt > 0 then :outLines := v_clob; end if; :inOutCnt := v_cnt; DBMS_LOB.freetemporary (v_clob); END; :trim_line(INTEGER,IN)=1 :inOutCnt(INTEGER,IN/OUT)=500 :outLines(CLOB,OUT)= ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:19.097 BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END; ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:19.339 select PARAMETER,VALUE from nls_session_parameters where PARAMETER in('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT','NLS_CURRENCY'); -- Elapsed Time: 0.006 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:19.479 select to_char(9,'9C') from dual; -- Elapsed Time: 0.037 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:19.563 SELECT distinct BUSINESS_ID FROM trade_state_irs_view; -- Elapsed Time: 0.016 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:19.700 SELECT distinct BUSINESS_ID FROM trade_state_view; -- Elapsed Time: 0.026 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:18:19.803 DECLARE v_lines SYS.DBMS_OUTPUT.chararr; v_line VARCHAR2 (32767); v_clob CLOB; v_cnt NUMBER; v_output VARCHAR2 (32767); PROCEDURE write_to_clob (p_clob IN OUT CLOB, p_str IN OUT VARCHAR2) AS BEGIN BEGIN IF p_str IS NOT NULL THEN DBMS_LOB.writeappend (p_clob, LENGTH (p_str), p_str); END IF; EXCEPTION WHEN VALUE_ERROR THEN NULL; END; p_str := NULL; END; BEGIN v_cnt := :inOutCnt; SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt); DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session); DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite); v_output := NULL; FOR line IN 1 .. v_lines.COUNT LOOP IF :trim_line = 1 THEN v_line := TRIM (v_lines (line)); ELSE v_line := v_lines (line); END IF; IF v_line IS NOT NULL THEN v_line := REPLACE (v_line, CHR (0)); IF LENGTHB (v_output) + LENGTHB (v_line) > 32767 THEN write_to_clob (v_clob, v_output); END IF; v_output := v_output || v_line; IF line < v_lines.COUNT THEN IF LENGTHB (v_output) + 2 > 32767 THEN write_to_clob (v_clob, v_output); END IF; v_output := v_output || CHR (13) || CHR (10); END IF; END IF; END LOOP; write_to_clob (v_clob, v_output); if v_cnt > 0 then :outLines := v_clob; end if; :inOutCnt := v_cnt; DBMS_LOB.freetemporary (v_clob); END; :trim_line(INTEGER,IN)=1 :inOutCnt(INTEGER,IN/OUT)=500 :outLines(CLOB,OUT)= ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:19:06.858 Select created, last_ddl_time, object_id, status from sys.user_objects where object_name = :nm and object_type = :t; :nm(VARCHAR[7],IN)='P_TRADE' :t(VARCHAR[12],IN)='PACKAGE BODY' -- Elapsed Time: 0.005 seconds ---------------------------------- -- Session: FX_SHANKAR_A@DEV11G -- Timestamp: 16:19:07.033 Select created, last_ddl_time, object_id, status from sys.ALL_OBJECTS where object_name = :nm and owner = :o and object_type = :t; :nm(VARCHAR[7],IN)='P_TRADE' :o(VARCHAR[6],IN)='MARC_B' :t(VARCHAR[12],IN)='PACKAGE BODY' -- Elapsed Time: 0.005 seconds