ORACLE_OCM.MGMT_DB_LL_METRICS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 27

    select version into l_db_version from v$instance;

Line: 33

    select substr(value,1,5) into l_compat_vers from v$parameter where lower(name) = 'compatible';

Line: 84

      execute immediate 'SELECT UPPER(CDB) FROM V$DATABASE' into g_is_cdb;

Line: 225

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 229

      'SELECT name, '||
      ' case '||
      '  when name=''filesystemio_options'' and value like ''asynch%'' then ''asynch'' '||
      '  when name=''filesystemio_options'' and value like ''none%'' then ''none'''||
      '  when name=''filesystemio_options'' and value like ''directIO%'' then ''directIO'''||
      '  when name=''filesystemio_options'' and value like ''setall%'' then ''setall'''||
      '  when name=''filesystemio_options'' then '' '''||
      '  else value '||
      ' end,'||
      ' isdefault FROM gv$parameter ' ||
      ' WHERE name != ''resource_manager_plan'' '||
      ' AND inst_id = ' || inst_id_row.inst_id,inst_id_row.instance_name);

Line: 250

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 254

      'SELECT pdb, '||
      ' name, '||
      ' CASE '||
       ' WHEN name=''filesystemio_options'' and value like ''asynch%'' then ''asynch'' '||
       ' WHEN name=''filesystemio_options'' and value like ''none%'' then ''none'' '||
       ' WHEN name=''filesystemio_options'' and value like ''directIO%'' then ''directIO'' '||
       ' WHEN name=''filesystemio_options'' and value like ''setall%'' then ''setall''  '||
       ' WHEN name=''filesystemio_options'' then '' '' '||
       ' ELSE value END value, '||
       ' isdefault '||
       'FROM '||
       ' (SELECT * FROM  '||
        ' (WITH '||
         ' override AS (SELECT name, value, isdefault, con_id '||
          ' FROM gv$system_parameter  '||
          ' WHERE con_id != 0 AND inst_id = ' || inst_id_row.inst_id ||'), '||
         ' pdbs AS (SELECT DISTINCT con_id, name pdb '||
          ' FROM gv$containers WHERE con_id != 2 and inst_id = '|| inst_id_row.inst_id ||') '||
         ' SELECT p.name, p.value, p.isdefault, pdb.pdb '||
         ' FROM gv$system_parameter p, pdbs pdb '||
         ' WHERE name NOT IN '||
          ' (SELECT name '||
           ' FROM override o '||
           ' WHERE o.con_id = pdb.con_id) '||
          ' AND p.con_id = 0 '||
         ' UNION '||
         ' SELECT name,value,isdefault,pdb.pdb '||
          ' FROM override o, pdbs pdb '||
           ' WHERE pdb.con_id = o.con_id) '||
        ') WHERE name != ''resource_manager_plan'' order by 1,2', inst_id_row.instance_name);

Line: 292

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 296

    'SELECT distinct pdb.pdb,' ||
    ' p.name,' ||
    ' case ' ||
    ' when p.name=''filesystemio_options'' and p.value like ''asynch%'' then ''asynch''' ||
    ' when p.name=''filesystemio_options'' and p.value like ''none%'' then ''none''' ||
    ' when p.name=''filesystemio_options'' and p.value like ''directIO%'' then ''directIO''' ||
    ' when p.name=''filesystemio_options'' and p.value like ''setall%'' then ''setall'' ' ||
    ' when p.name=''filesystemio_options'' then '' ''' ||
    ' else p.value end,' ||
    ' p.isdefault' ||
    ' from gv$system_parameter p, (select DISTINCT con_id, name pdb from gv$containers where con_id != 2) pdb' ||
    ' where p.con_id = pdb.con_id' ||
    ' and name != ''resource_manager_plan'' ' ||
    ' and inst_id = ' || inst_id_row.inst_id,inst_id_row.instance_name);

Line: 327

    'select inst_id, group_number, disk_number, header_status, path '||
    'from gv$asm_disk where group_number > 0 and header_status != ''MEMBER'' ');

Line: 344

    'select client_name, status from DBA_AUTOTASK_CLIENT where lower(client_name) = ''sql tuning advisor'' ');

Line: 358

      write_metric('db_components',' select '' '' namespace, comp_id, comp_name, version, status, schema from sys.dba_registry');

Line: 360

      write_metric('db_components',' select namespace, comp_id, comp_name, version, status, schema from sys.dba_registry');

Line: 372

  write_metric('db_invobj_cnt',' select owner, count(*) from sys.dba_objects where status = ''INVALID'' group by owner ');

Line: 384

    write_metric('db_scheduler_jobs',' select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from sys.dba_scheduler_jobs where job_name=''GATHER_STATS_JOB''');

Line: 421

  select 16*1024*24*60*60, 32*1024*24*60*60 into c_time_16, c_time_32 from dual;

Line: 423

  SELECT LPAD(version, 10, '0') into l_db_version from v$instance;

Line: 426

      'SELECT count(*) from dba_registry_history ' ||
      'where comments=''CPUJan2012'' OR comments=''CPUApr2012''';

Line: 434

      select 4*1024*1024*1024*2575 into l_seconds_1988 from dual;

Line: 440

    'SELECT d.current_scn, svalue ' ||
    'FROM v$database d, ' ||
    '(SELECT sum(s.value) svalue from v$sysstat s WHERE s.name in (''calls to kcmgas'', ''redo writes''))';

Line: 446

    'select dbms_flashback.get_system_change_number from dual';

Line: 449

    'SELECT sum(s.value) svalue from v$sysstat s WHERE s.name in (''calls to kcmgas'', ''redo writes'')';

Line: 454

  SELECT
    (((((( (to_number(to_char(sysdate,'YYYY'))-l_base_year)* 12
          +(to_number(to_char(sysdate,'MM'))-1))           * 31
         + (to_number(to_char(sysdate,'DD'))-1))           * 24
        +  (to_number(to_char(sysdate,'HH24'))))           * 60
       +   (to_number(to_char(sysdate,'MI'  ))))           * 60
      +    (to_number(to_char(sysdate,'SS'  ))))
     * l_max_rate) + l_seconds_1988
    INTO l_maximum_scn FROM dual;

Line: 467

      SELECT to_char(((l_maximum_scn - l_current_scn) / c_time_32),'99999D99')
        INTO l_headroom FROM dual;

Line: 470

      SELECT to_char((((l_maximum_scn - l_seconds_1988) / c_time_32) +
                     ((l_seconds_1988 - l_current_scn) / c_time_16)),'99999D99')
          INTO l_headroom FROM dual;

Line: 475

    SELECT to_char(((l_maximum_scn - l_current_scn) / c_time_16),'99999D99')
      INTO l_headroom FROM dual;

Line: 480

  SELECT count(*) INTO l_dblink_out FROM DBA_DB_LINKS;

Line: 483

  SELECT count(*) INTO l_dblink_in FROM dba_audit_trail
    WHERE action_name='LOGON' AND comment_text LIKE '%DBLINK_INFO%' AND timestamp > (sysdate-1) ORDER BY timestamp;

Line: 489

      EXECUTE IMMEDIATE 'select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME=''Flashback Timestamp TimeZone''' INTO l_is_gmt;

Line: 500

    'SELECT scn_per_sec, round(avg_scn_per_sec), to_char(time_stamp, ''YYYY-MM-DD HH24:MI:SS'') ' ||
    'FROM ' ||
      '( SELECT time_stamp, scn_per_sec, avg(scn_per_sec) over() avg_scn_per_sec, max(scn_per_sec) over() max_scn_per_sec ' ||
        'FROM ' ||
          '( SELECT time_dp time_stamp, scn, ' ||
                 'round ((scn - lag(scn,1) over(ORDER BY time_dp))/ ' ||
                        '(60*60*24 * (time_dp - lag(time_dp,1) over(ORDER BY time_dp))), ' ||
                        '0) scn_per_sec ' ||
            'FROM sys.smon_scn_time ' ||
            l_sql_piece ||
          ') ' ||
        'WHERE scn_per_sec IS NOT NULL order by time_stamp desc ' ||
      ') ' || -- remove first entry which will have null because of lag()
    'WHERE scn_per_sec = max_scn_per_sec and rownum = 1';

Line: 517

  SELECT to_char(created,'YYYY-MM-DD HH24:MI:SS') INTO l_created FROM v$database;

Line: 521

    SELECT value INTO l_reject_thresh FROM v$parameter WHERE name='_external_scn_rejection_threshold_hours';

Line: 554

' SELECT  l.group# group_num, ' ||
/*
Comment this column as it changes frequently
'          NLS_INITCAP(l.status) status, ' ||
*/
' '''', ' ||
' l.members members, ' ||
' lf.member file_name, ' ||
/*
Comment this column as it changes frequently
' NLS_INITCAP(l.archived) archived, ' ||
*/
' '''', ' ||
' l.bytes logsize, ' ||
/*
Comment this column as it changes frequently
' l.sequence# sequence_num, ' ||
*/
' '''', ' ||
/*
Comment this column as it changes frequently
' l.first_change# first_change_scn, ' ||
*/
' '''', ' ||
' l.thread# as thread_num , lf.type type' ||
' FROM    v$log l, ' ||
'         v$logfile lf ' ||
' WHERE   l.group# = lf.group#');

Line: 609

    'SELECT /*+ ORDERED */ ' ||
    ' REPLACE(REPLACE(ddf.file_name, chr(10), ''''), chr(13), '''') file_name, ' ||
    l_status_clause1 ||
    ' ddf.tablespace_name tablespace_name,'||
    ' '''', ' ||
    ' ddf.autoextensible autoextensible, '||
    ' ddf.increment_by increment_by, ' ||
    ' ddf.maxbytes max_file_size, ' ||
    ' vdf.create_bytes, ' ||
    ' ''NA'' os_storage ' ||
    'FROM v$datafile vdf, ' ||
    'sys.dba_data_files ddf ' ||
    'WHERE  (vdf.file# = ddf.file_id) ' ||
    'UNION ALL ' ||
    'SELECT /*+ ORDERED  */ ' ||
    ' REPLACE(REPLACE(dtf.file_name, chr(10), ''''), chr(13), '''') file_name, '||
    l_status_clause2 ||
    ' dtf.tablespace_name tablespace_name,'||
    ' '''', ' ||
    ' dtf.autoextensible autoextensible, '||
    ' dtf.increment_by increment_by, ' ||
    ' dtf.maxbytes max_file_size, ' ||
    ' vtf.create_bytes, ' ||
    ' ''NA'' os_storage ' ||
    'FROM v$tempfile vtf, ' ||
    ' sys.dba_temp_files dtf ' ||
    'WHERE (dtf.file_id =  vtf.file#)');

Line: 646

  'SELECT /*+ ORDERED NO_PARALLEL(ddf) */ pdb.pdb,' ||
        ' REPLACE(REPLACE(ddf.file_name, chr(10), ''''), chr(13), ''''), ' ||
        ' ddf.online_status,' ||
        ' ddf.tablespace_name,' ||
        ' '''',' ||
        ' ddf.autoextensible,' ||
        ' ddf.increment_by,' ||
        ' ddf.maxbytes,' ||
        ' vdf.create_bytes,' ||
        ' ''NA'' ' ||
  'FROM sys.cdb_data_files ddf, v$datafile vdf, ' ||
  '(select DISTINCT con_id, name pdb from gv$containers where con_id != 2 ) pdb ' ||
  'WHERE (vdf.file# = ddf.file_id) and ddf.con_id = pdb.con_id and vdf.con_id = pdb.con_id ' ||
  'UNION ALL ' ||
  'SELECT /*+ NO_PARALLEL(dtf) */ pdb.pdb,' ||
        ' REPLACE(REPLACE(dtf.file_name, chr(10), ''''), chr(13), ''''),' ||
        ' dtf.status,' ||
        ' dtf.tablespace_name,' ||
        ' '''',' ||
        ' dtf.autoextensible,' ||
        ' dtf.increment_by,' ||
        ' dtf.maxbytes,' ||
        ' vtf.create_bytes,' ||
        ' ''NA'' ' ||
  'FROM sys.cdb_temp_files dtf, v$tempfile vtf, ' ||
  '(select DISTINCT con_id, name pdb from gv$containers where con_id != 2) pdb ' ||
  'WHERE (dtf.file_id = vtf.file#) and dtf.con_id = pdb.con_id and vtf.con_id = pdb.con_id');

Line: 706

  l_sql_db_tablespaces := 'SELECT ' ||
  ' dtp.tablespace_name,'  ||
  ' dtp.status,' ||
  ' dtp.contents,' ||
  ' dtp.extent_management,' ||
  ' dtp.allocation_type,' ||
  ' dtp.logging,' ||
  ' '''',' || -- this column in GC metric changes too often
  ' dtp.initial_extent,' ||
  ' dtp.next_extent,' ||
  ' dtp.pct_increase,' ||
  ' dtp.max_extents,' ||
  ' '''',' || -- this column in GC metric changes too often
  ' dtp.min_extents,' ||
  ' dtp.min_extlen,' ||
  l_segspace ||
  l_blocksize ||
  l_bigfile ||
  ' FROM '||
  ' sys.dba_tablespaces dtp';

Line: 741

    '(SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
    'SELECT /*+ NO_PARALLEL(dtp) */ pdb.pdb,' ||
    'dtp.tablespace_name,' ||
    'dtp.status,' ||
    'dtp.contents,' ||
    'dtp.extent_management,' ||
    'dtp.allocation_type,' ||
    'dtp.logging,' ||
    ''''',' ||
    'dtp.initial_extent,' ||
    'dtp.next_extent,' ||
    'dtp.pct_increase,' ||
    'dtp.max_extents,' ||
    ''''',' ||
    'dtp.min_extents,' ||
    'dtp.min_extlen,' ||
    'dtp.segment_space_management,' ||
    'dtp.block_size,' ||
    'dtp.bigfile ' ||
    'FROM sys.cdb_tablespaces dtp, pdbview pdb ' ||
    'WHERE NOT (dtp.extent_management LIKE ''LOCAL'' AND ' ||
    'dtp.contents LIKE ''TEMPORARY'') AND ' ||
    'dtp.con_id = pdb.con_id ' ||
    'UNION ALL ' ||
    'SELECT  /*+ NO_PARALLEL(dtp) */ pdb.pdb,' ||
    'dtp.tablespace_name,' ||
    'dtp.status,' ||
    'dtp.contents,' ||
    'dtp.extent_management,' ||
    'dtp.allocation_type,' ||
    'dtp.logging,' ||
    ''''',' ||
    'dtp.initial_extent,' ||
    'dtp.next_extent,' ||
    'dtp.pct_increase,' ||
    'dtp.max_extents,' ||
    ''''',' ||
    'dtp.min_extents,' ||
    'dtp.min_extlen,' ||
    'dtp.segment_space_management,' ||
    'dtp.block_size,' ||
    'dtp.bigfile ' ||
    'FROM sys.cdb_tablespaces dtp, pdbview pdb ' ||
    'WHERE dtp.extent_management LIKE ''LOCAL'' AND ' ||
    'dtp.contents LIKE ''TEMPORARY'' AND ' ||
    'dtp.con_id = pdb.con_id');

Line: 795

    'SELECT cf.name file_name, ' ||
    ' db.controlfile_type status, ' ||
    ' to_char(db.controlfile_created,''YYYY-MM-DD HH24:MI:SS'') creation_date, ' ||
/*
Comment this column as it changes frequently
    ' db.controlfile_sequence# sequence_num, ' ||
*/
    ' '''', ' ||
/*
Comment this column as it changes frequently
    ' db.controlfile_change# change_num, ' ||
*/
    ' '''', ' ||
/*
Comment this column as it changes frequently
    ' to_char(db.controlfile_time,''YYYY-MM-DD HH24:MI:SS'') mod_date ' ||
*/
    ' '''', ' ||
/*
Comment this column as it is unavailable from db
    ' os_storage_entity '
*/
    ' ''NA'' ' ||
    ' FROM v$controlfile cf, ' ||
    ' v$database db ' );

Line: 831

    'SELECT ' ||
    ' drs.segment_name rollname, ' ||
    ' drs.status status, ' ||
    ' drs.tablespace_name tablespace_name, ' ||
    ' rs.extents extents, ' ||
/*
Comment this column as per George
    ' rs.rssize rollsize, ' ||
*/
    ' '''', ' ||
    ' drs.initial_extent initial_size, ' ||
    ' drs.next_extent next_size, ' ||
    ' drs.max_extents maximum_extents, ' ||
    ' drs.min_extents minimum_extents, ' ||
    ' drs.pct_increase pct_increase, ' ||
    ' rs.optsize optsize, ' ||
    ' rs.aveactive aveactive, ' ||
    ' rs.wraps wraps, ' ||
    ' rs.shrinks shrinks, ' ||
    ' rs.aveshrink aveshrink, ' ||
/*
Comment this column as its a volatile data
    ' rs.hwmsize hwmsize ' ||
*/
    ' '''' ' ||
    ' FROM sys.dba_rollback_segs drs, ' ||
    ' v$rollstat rs ' ||
    ' WHERE drs.segment_id = rs.usn (+) ' ||
    ' and substr(drs.segment_name,1,7) != ''_SYSSMU''');

Line: 870

    'SELECT /*+ NO_PARALLEL(drs) */ pdb.pdb pdb_name, '||
    ' drs.segment_name rollname, ' ||
    ' drs.status status, ' ||
    ' drs.tablespace_name tablespace_name, ' ||
    ' rs.extents extents, ' ||
/*
Comment this column as per George
    ' rs.rssize rollsize, ' ||
*/
    ' '''', ' ||
    ' drs.initial_extent initial_size, ' ||
    ' drs.next_extent next_size, ' ||
    ' drs.max_extents maximum_extents, ' ||
    ' drs.min_extents minimum_extents, ' ||
    ' drs.pct_increase pct_increase, ' ||
    ' rs.optsize optsize, ' ||
    ' rs.aveactive aveactive, ' ||
    ' rs.wraps wraps, ' ||
    ' rs.shrinks shrinks, ' ||
    ' rs.aveshrink aveshrink, ' ||
/*
Comment this column as its a volatile data
    ' rs.hwmsize hwmsize ' ||
*/
    ' '''' ' ||
    ' FROM sys.cdb_rollback_segs drs, (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) pdb, ' ||
    ' v$rollstat rs ' ||
    ' WHERE drs.segment_id = rs.usn (+) ' ||
    ' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' ||
    ' and drs.con_id = pdb.con_id' );

Line: 908

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 912

    ' select sganame,sgasize  ' ||
    ' from ' ||
    ' ((SELECT ''Shared Pool (MB)'' sganame, ' ||
    ' ROUND(NVL(sum(bytes)/1024/1024,0)) sgasize ' ||
    ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND pool = ''shared pool'') ' ||
    ' UNION ' ||
    ' (SELECT ''Buffered Cache (MB)'' sganame, ' ||
    ' ROUND(NVL(bytes/1024/1024,0)) sgasize ' ||
    ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND ((name = ''db_block_buffers'' AND pool IS NULL ) OR name = ''buffer_cache'')) ' ||
    ' UNION ' ||
    ' (SELECT ''Large Pool (KB)'' "NAME", ' ||
    ' ROUND(NVL(sum(bytes)/1024,0)) "SIZE" ' ||
    ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND pool = ''large pool'') ' ||
    ' UNION ' ||
    ' (SELECT ''Java Pool (MB)'' "NAME", ' ||
    ' ROUND(NVL(sum(bytes)/1024/1024,0)) "SIZE" ' ||
    ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND pool = ''java pool'') ' ||
    ' UNION ' ||
    ' (SELECT ''Fixed SGA (KB)'' "NAME", ' ||
    ' ROUND(NVL(value/1024,0)) "SIZE" ' ||
    ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND name=''Fixed Size'') ' ||
    ' UNION ' ||
    ' (SELECT ''Variable SGA (MB)'' "NAME", ' ||
    ' ROUND(NVL(value/1024/1024,0)) "SIZE" ' ||
    ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND name=''Variable Size'') ' ||
    ' UNION ' ||
    ' (SELECT ''Redo Buffers (KB)'' "NAME", ' ||
    ' ROUND(NVL(value/1024,0)) "SIZE" ' ||
    ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
    ' AND name=''Redo Buffers'') ' ||
    ' UNION ' ||
    ' (SELECT ''Total SGA (MB)'' "NAME", ' ||
    ' ROUND(NVL(sum(bytes)/1024/1024,0)) "SIZE" ' ||
    ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
    ') ' ||
    ' UNION ' ||
    ' (SELECT ''Maximum SGA (MB)'' "NAME", ' ||
    ' ROUND(NVL(sum(value)/1024/1024,0)) "SIZE" ' ||
    ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
    ')) ' ||
    ' ORDER BY sgasize ' , inst_id_row.instance_name);

Line: 970

    'SELECT sessions_max, ' ||
    'sessions_warning, ' ||
/*
Comment this column as per George
'sessions_current , ' ||
*/
    ' '''', ' ||
    ' sessions_highwater, ' ||
    ' users_max ' ||
    ' FROM v$license ');

Line: 992

    l_data_mining := 'select ''ORACLE_DATA_MINING'' as name, ''FALSE'' as selected ';

Line: 1000

    l_data_mining := ' select ''ORACLE_DATA_MINING'' as name, decode((select status from dba_registry where comp_id=''ODM''), ''VALID'', ''TRUE'', ''FALSE'') as selected ';

Line: 1006

    ' select ''INTER_MEDIA'' as name, decode((select username from dba_users where username=''ORDSYS''), ''ORDSYS'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''SPATIAL'' as name, decode((select username from dba_users where username=''MDSYS''), ''MDSYS'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''OLAP'' as name, decode((select username from dba_users where username=''OLAPSYS''), ''OLAPSYS'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''ORACLE_TEXT'' as name, decode((select username from dba_users where username=''CTXSYS''), ''CTXSYS'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''ULTRA_SEARCH'' as name, decode((select username from dba_users where username=''WKSYS''), ''WKSYS'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''LABEL_SECURITY'' as name, decode((select username from dba_users where username=''LBACSYS''), ''LBACSYS'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''SAMPLE_SCHEMA'' as name, decode((select count(*) from dba_users where username IN(''HR'',''PM'',''QS'',''SH'',''OE'')), 0, ''FALSE'', ''TRUE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''JSERVER'' as name, decode((select count(*) from sys.obj$ where type#=29), 0, ''FALSE'', ''TRUE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    l_data_mining ||
    ' from dual ' ||
    ' union ' ||
    ' select ''XDB'' as name, decode((select username from dba_users where username=''XDB''), ''XDB'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ' ||
    ' union ' ||
    ' select ''EM_REPOSITORY'' as name, decode((select username from dba_users where username=''SYSMAN''), ''SYSMAN'', ''TRUE'', ''FALSE'') as selected  ' ||
    ' from dual ');

Line: 1049

    'select '||
    ' (select decode(count(*),1,''YES'',''NO'') FROM sys.obj$ o, sys.user$ u '||
    '  WHERE u.name = ''PERFSTAT'' AND o.owner# = u.user# AND o.name = ''STATSPACK'' '||
    '  AND o.type# = 11 AND o.status = 1) is_installed, '||
    ' (select nvl(INTERVAL,'''') from dba_jobs '||
    '  where what like ''statspack.snap%'' and SCHEMA_USER=''PERFSTAT'' and rownum = 1) freq from dual');

Line: 1065

  'select USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, '||
  'to_char(CREATED,''YYYY-MM-DD HH24:MI:SS'') creation_time, PROFILE,'||
  'to_char(EXPIRY_DATE,''YYYY-MM-DD HH24:MI:SS'') expire_time from dba_users');

Line: 1078

  'WITH pdbs AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) '||
  'SELECT /*+ NO_PARALLEL(u) */ pdb, '||
  ' USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, '||
  ' to_char(CREATED,''YYYY-MM-DD HH24:MI:SS'') ,' ||
  ' PROFILE, ' ||
  ' to_char(EXPIRY_DATE,''YYYY-MM-DD HH24:MI:SS'') ' ||
  ' FROM cdb_users u, pdbs p '||
  ' WHERE p.con_id = u.con_id');

Line: 1097

    'select DEVICE_TYPE, ''DATAFILE'' from v$backup_datafile d, V$BACKUP_PIECE p '||
    'where d.SET_COUNT=p.SET_COUNT and d.SET_STAMP=p.SET_STAMP and p.STATUS =''A'' '||
    'and d.FILE# != 0 '||
    'union all '||
    'select p.DEVICE_TYPE,''REDOLOG'' from V$BACKUP_REDOLOG r, V$BACKUP_PIECE p '||
    'where r.SET_COUNT=p.SET_COUNT and r.SET_STAMP=p.SET_STAMP and p.STATUS =''A''');

Line: 1116

        'SELECT dbid, log_mode FROM v$database');

Line: 1121

        'SELECT dbid, log_mode, force_logging, database_role FROM v$database');

Line: 1131

    'SELECT  dbid, log_mode, force_logging, database_role, flashback_on, supplemental_log_data_min FROM v$database');

Line: 1151

                 'select ''CONTROLFILE AUTOBACKUP'',nvl((select value from v$rman_configuration where name=''CONTROLFILE AUTOBACKUP''),'''') from dual ' ||
                 'union ' ||
                 'select name, value from v$rman_configuration where name=''CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE'' and value like ''DISK%'' ' );

Line: 1163

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 1171

  l_dbproperties_select   VARCHAR2(100);

Line: 1181

    l_dbproperties_select := ' ''SYSTEM'' default_temp_tablespace, ';

Line: 1192

    l_dbproperties_select := ' p.property_value default_temp_tablespace, ';

Line: 1207

    l_dv_status := ' , case when (select count(*) from dba_users where username =''DVSYS'' and user_id = 1279990)  > 0 ' ||
                   ' then case when (select value from v$option where parameter =''Oracle Database Vault'') =''TRUE'' ' ||
                   '  then 1 ' || -- Enabled
                   '  else 0 ' || --Disabled
                   ' end ' ||
                   ' when (select count(*) from dba_users where username =''DVSYS'' and user_id = 1279990) = 0 ' ||
                   ' then case when (select value from v$option where parameter =''Oracle Database Vault'') = ''TRUE'' ' ||
                   '  then -1 ' || -- Not Configured
                   '  else -2 ' || -- Not Installed
                   ' end ' ||
                   'end ' ||
                   ' dv_status_code ';

Line: 1222

  select length(addr)*4 into l_word_length from v$process where rownum=1;

Line: 1259

                 'SELECT '||
                 ' a.name database_name, ' ||
                 ' e.global_name global_name, ' ||
                 ' b.banner banner, ' ||
                 ' c.host_name host_name, ' ||
                 ' c.instance_name instance_name, ' ||
                 ' to_char(c.startup_time,''YYYY-MM-DD HH24:MI:SS'') startup_time, ' ||
                 ' decode(c.logins,''RESTRICTED'',''YES'',''NO'') logins, ' ||
                 ' a.log_mode log_mode, ' ||
                 ' decode(a.open_mode,''READ ONLY'',''YES'',''NO'') open_mode, ' ||
                 ' nlsp1.value characterset, ' ||
                 ' nlsp2.value national_characterset, ' ||
                 l_dbproperties_select ||
                 ' to_char(a.created,''YYYY-MM-DD HH24:MI:SS'') created ' ||
                 l_banner_split_clause ||
                 ' c.version' ||
                 l_banner_bitrelstr_clause ||
                 l_supplemental_log ||
                 l_dv_status ||
                 ' FROM  gv$database a, ' ||
                       ' gv$version b, ' ||
                       ' gv$instance c, ' ||
                       ' global_name e,' ||
                       ' gv$nls_parameters nlsp1 , ' ||
                       ' gv$nls_parameters nlsp2 ' ||
                 l_dbproperties_from ||
                 ' WHERE b.banner LIKE ''%Oracle%''  ' ||
                 ' AND nlsp1.parameter = ''NLS_CHARACTERSET'' ' ||
                 ' AND nlsp2.parameter = ''NLS_NCHAR_CHARACTERSET'' ' ||
                 l_dbproperties_where ||
                 ' AND a.INST_ID = ' || inst_id_row.inst_id ||
                 ' AND b.INST_ID = ' || inst_id_row.inst_id ||
                 ' AND c.INST_ID = ' || inst_id_row.inst_id ||
                 ' AND nlsp1.INST_ID = ' || inst_id_row.inst_id ||
                 ' AND nlsp2.INST_ID = ' || inst_id_row.inst_id
                 ,inst_id_row.instance_name);

Line: 1304

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 1308

    'with pdbview AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
    'SELECT /*+ NO_PARALLEL(e) NO_PARALLEL(p) NO_PARALLEL(u) */ pdb.pdb, ' ||
           'a.name, ' ||
           'e.property_value, ' ||
           'b.banner, ' ||
           'c.host_name, ' ||
           'c.instance_name, ' ||
           'to_char(c.startup_time,''YYYY-MM-DD HH24:MI:SS''), ' ||
           'decode(c.logins,''RESTRICTED'',''YES'',''NO''), ' ||
           'a.log_mode, ' ||
           'case when pdb.con_id=0 then ' ||
             'decode(a.open_mode,''READ ONLY'',''YES'',''NO'') ' ||
           'else ' ||
             'decode(vpb.OPEN_MODE,''READ ONLY'',''YES'',''NO'') ' ||
           'end, ' ||
           'nlsp1.value, ' ||
           'nlsp2.value, ' ||
           'p.property_value, ' ||
           'to_char(a.created,''YYYY-MM-DD HH24:MI:SS''), ' ||
           'substr(banner,instr(banner,''Oracle Database ''),19), ' ||
           'nvl(trim(substr(banner,instr(banner,''Oracle Database '')+19,instr(banner,'' Release'') - instr(banner,''Oracle Database  '') - 19)),decode(instr(banner,''Personal''),0,'''',''Personal'')), ' ||
           'c.version dbversion, ' ||
           'DECODE(addr.word_len, 64, ''Y'', ''N''), ' ||
           'substr(substr(banner, instr(banner, ''-'') + 2), instr(substr(banner, instr(banner, ''-'') + 2),'' '')+1), ' ||
           'a.supplemental_log_data_min, ' ||
           'case when (select count(*) from cdb_users u where u.username =''DVSYS'' and u.user_id = 1279990 and u.con_id = pdb.con_id) > 0 then  ' ||
             'case when (select value from v$option o where o.parameter =''Oracle Database Vault'' and (o.con_id = pdb.con_id or o.con_id = 0)) = ''TRUE'' ' ||
               'then 1 ' || -- Enabled
               'else 0 ' || --Disabled
             'end ' ||
           'when (select count(*) from cdb_users u where u.username =''DVSYS'' and u.user_id = 1279990 and u.con_id = pdb.con_id) = 0 then  ' ||
             'case when (select value from v$option o where parameter =''Oracle Database Vault''  and (o.con_id = pdb.con_id or o.con_id = 0)) = ''TRUE'' ' ||
               'then -1 ' || -- Not Configured
               'else -2 ' || -- Not Installed
             'end ' ||
           'end ' ||
    'FROM gv$database a, ' ||
         'gv$version b, ' ||
         'gv$instance c, ' ||
         'CDB_PROPERTIES e, ' ||
         'gv$nls_parameters nlsp1, ' ||
         'gv$nls_parameters nlsp2, ' ||
         'CDB_PROPERTIES p, ' ||
         '(select length(addr)*4 word_len from gv$process where rownum=1) addr, ' ||
         'pdbview pdb, ' ||
         'gv$pdbs vpb ' ||
    'WHERE b.banner LIKE ''%Oracle%''  ' ||
    'AND   nlsp1.parameter = ''NLS_CHARACTERSET'' ' ||
    'AND   nlsp2.parameter = ''NLS_NCHAR_CHARACTERSET'' ' ||
    'AND   e.property_name = ''GLOBAL_DB_NAME'' ' ||
    'AND   p.property_name = ''DEFAULT_TEMP_TABLESPACE'' ' ||
    'AND   (a.con_id = pdb.con_id OR a.con_id = 0) ' ||
    'AND   (b.con_id = pdb.con_id OR b.con_id = 0) ' ||
    'AND   (c.con_id = pdb.con_id OR c.con_id = 0) ' ||
    'AND   (nlsp1.con_id = pdb.con_id OR nlsp1.con_id = 0) ' ||
    'AND   (nlsp2.con_id = pdb.con_id OR nlsp2.con_id = 0) ' ||
    'AND   e.con_id = pdb.con_id ' ||
    'AND   p.con_id = pdb.con_id ' ||
    'AND   vpb.con_id(+) = pdb.con_id' ||
    ' AND  a.INST_ID = ' || inst_id_row.inst_id ||
    ' AND  b.INST_ID = ' || inst_id_row.inst_id ||
    ' AND  c.INST_ID = ' || inst_id_row.inst_id ||
    ' AND  nlsp1.INST_ID = ' || inst_id_row.inst_id ||
    ' AND  nlsp2.INST_ID = ' || inst_id_row.inst_id ||
    ' AND  vpb.INST_ID = ' || inst_id_row.inst_id
    ,inst_id_row.instance_name);

Line: 1399

     execute immediate 'select 1 from dual where exists (' || p_install_sql || ')' into l_isInstalled;

Line: 1426

    execute immediate 'select ''TRUE'' from dual where exists (' || p_usage_sql || ')' into l_isUsed;

Line: 1521

      'select ' ||
      '''' || g_dbID || ''', ' || -- DBID
      '''' ||  substr('OCM ' || p_name,0,64) || ''',' || -- NAME
      '''' || l_option_version || ''',' || -- Version
      'decode(''' || l_isUsed || ''',''TRUE'',''1'',''''),' || -- Detected Usages
      '1,' || -- Total Samples
      '''' || l_isUsed || ''' ,' || -- Currently Used
      ''''',' || -- First Usage Date
      ''''',' || -- Last Usage Date
      ''''',' || -- Aux Count
      ''''',' || -- Last Sample Date
      ''''',' || -- Last Sample Period
      '''' || replace(l_feature_info,'|','#') || '''' || -- Feature Info
      ' from dual');

Line: 1583

    'SELECT 1 from v$option where parameter=''Real Application Clusters'' and value =''TRUE'' and rownum = 1';

Line: 1585

    'select 1  from (select count(*) CNT from gv$instance where rownum <=2) where CNT >= 2';

Line: 1602

    'SELECT 1 from v$option where parameter=''Oracle Label Security'' and value =''TRUE'' and rownum = 1';

Line: 1604

    'select 1  from lbacsys.lbac$polt where owner <> ''SA_DEMO'' and rownum = 1';

Line: 1621

    'SELECT 1 from v$option where parameter like ''%Data Mining'' and value =''TRUE'' and rownum = 1';

Line: 1623

    'select 1  from odm.odm_mining_model where rownum = 1';

Line: 1639

    'SELECT 1 from (Select count(*) CNT from dba_users where username in (''DVSYS'',''DVF'') ) where CNT = 2';

Line: 1641

    'select 1 from dba_users where username = ''DVSYS'' and user_id = 1279990';

Line: 1658

    'SELECT 1 from dba_users where username = ''AVSYS'' and rownum = 1';

Line: 1674

    'SELECT 1 from dba_users where username = ''CONTENT'' and rownum = 1';

Line: 1677

    'select 1 from (select count(*) CNT from content.odm_document where rownum <= 9005 ) where CNT > 9004';

Line: 1693

    'SELECT 1 from dba_users where username = ''CONTENT'' and rownum = 1';

Line: 1695

    'select 1 from content.odm_record where rownum = 1';

Line: 1717

     SELECT '"' || owner || '"'
     FROM dba_tables WHERE table_name = 'SMP_REP_VERSION';

Line: 1721

     SELECT owner
     FROM dba_tables WHERE table_name = 'SMP_VDS_REPOS_VERSION';

Line: 1725

      SELECT COUNT(*)
      INTO v_count FROM ( SELECT DISTINCT program FROM
        v$session WHERE
        upper(program) LIKE '%XPNI.EXE%'
        OR upper(program) LIKE '%VMS.EXE%'
        OR upper(program) LIKE '%EPC.EXE%'
        OR upper(program) LIKE '%TDVAPP.EXE%'
        OR upper(program) LIKE 'VDOSSHELL%'
        OR upper(program) LIKE '%VMQ%'
        OR upper(program) LIKE '%VTUSHELL%'
        OR upper(program) LIKE '%JAVAVMQ%'
        OR upper(program) LIKE '%XPAUTUNE%'
        OR upper(program) LIKE '%XPCOIN%'
        OR upper(program) LIKE '%XPKSH%'
        OR upper(program) LIKE '%XPUI%');

Line: 1756

       dbms_sql.parse(cursor1,'select c_current_version, c_component from '||v_schema||'.smp_rep_version', dbms_sql.native);

Line: 1796

     write_option_record('OEM 9i',NULL,'select 1 from dual');

Line: 1810

    'select 1 from dual where sdo_version IS NOT NULL';

Line: 1812

    'select 1 from (select count(*) CNT from ALL_SDO_GEOM_METADATA where owner <> ''MDSYS'' and rownum =1) where CNT >0';

Line: 1814

    'select count(*) from ALL_SDO_GEOM_METADATA where owner <> ''MDSYS''';

Line: 1815

 l_versionSQL VARCHAR2(50) := 'select sdo_version from dual';

Line: 1830

    'SELECT 1 from v$option where parameter = ''Partitioning'' and value =''TRUE'' and rownum = 1';

Line: 1832

  'select 1 from (select sum(tot) CNT from
 ( select count(*) tot
   from
   ( select owner, table_name
     from dba_tables
     where partitioned=''YES''
     -- list of schemas to be excluded
     and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
     minus
     select change_table_schema, change_table_name
     from change_tables )
   union all
   select count(*) tot
   from dba_indexes di
   where partitioned=''YES''
   and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
   and not exists
   ( select change_table_schema, change_table_name
     from change_tables ct
     where di.table_owner = ct.change_table_schema
     and di.table_name = ct.change_table_name))) where CNT > 0';

Line: 1855

 ' select num||'':''||idx_or_tab||'':''||user_id||'':''||ptype||'':''||subptype||'':''||
    pcnt||'':''||subpcnt||'':''||
    pcols||'':''||subpcols||'':''||idx_flags||'':''||
    idx_type||'':''||idx_uk||''|'' my_string
   from (select * from
          (select /*+ full(o) */ dense_rank() over
                  (order by  decode(i.bo#,null,p.obj#,i.bo#)) NUM,
                  decode(o.type#,1,''I'',2,''T'',null)  IDX_OR_TAB,
                  u.user# USER_ID,
                  decode(p.parttype, 1, ''RANGE'', 2, ''HASH'', 3,
                  ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') PTYPE,
                  decode(mod(p.spare2, 256), 0, null, 2, ''HASH'', 3,
                  ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') SUBPTYPE,
                  p.partcnt PCNT,
                  mod(trunc(p.spare2/65536), 65536) SUBPCNT,
                  p.partkeycols PCOLS,
                  mod(trunc(p.spare2/256), 256) SUBPCOLS,
                  decode(p.flags, 0, null, decode(mod(p.flags,3),0,''LP'',1,''L'',2,''GP'', null)) IDX_FLAGS,
                  decode(i.type#, 1, ''NORMAL''|| decode(bitand(i.property, 4), 0, '''', 4, ''/REV''),
                  2, ''BITMAP'', 3, ''CLUSTER'', 4, ''IOT - TOP'',
                  5, ''IOT - NESTED'', 6, ''SECONDARY'', 7, ''ANSI'', 8, ''LOB'',
                  9, ''DOMAIN'') IDX_TYPE,
                  decode(i.property, null,null,
                    decode(bitand(i.property, 1), 0, ''NONUNIQUE'', 1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
                  from sys.partobj$ p, sys.obj$ o, sys.user$ u, sys.ind$ i
                  where o.obj# = i.obj#(+)
                  and   o.owner# = u.user#
                  and   p.obj# = o.obj#
                  and   u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
                  -- fix bug 3074607 - filter on obj$
                  and o.type# in (1,2,19,20,25,34,35)
                  -- exclude change tables
                  and o.obj# not in ( select obj# from sys.cdc_change_tables$)
                  -- exclude local partitioned indexes on change tables
                  and i.bo# not in  ( select obj# from sys.cdc_change_tables$)
            union all
            -- global nonpartitioned indexes on partitioned tables
            select dense_rank() over (order by  decode(i.bo#,null,p.obj#,i.bo#)) NUM,
                  ''I'' IDX_OR_TAB,
                  u.user# USER_ID,
                  null,null,null,null,cols PCOLS,null,
                  ''GNP'' IDX_FLAGS,
                  decode(i.type#, 1, ''NORMAL''||
                                 decode(bitand(i.property, 4), 0, '''', 4,
                                    ''/REV''),
                                  2, ''BITMAP'', 3, ''CLUSTER'', 4,
                                     ''IOT - TOP'',
                                  5, ''IOT - NESTED'', 6, ''SECONDARY'', 7,
                                      ''ANSI'', 8, ''LOB'',
                                  9, ''DOMAIN'') IDX_TYPE,
                  decode(i.property, null,null,
                         decode(bitand(i.property, 1),
                                0, ''NONUNIQUE'',
                                1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
            from sys.partobj$ p, sys.user$ u, sys.obj$ o, sys.ind$ i
            where p.obj# = i.bo#
            -- exclude global nonpartitioned indexes on change tables
            and   i.bo# not in  ( select obj# from sys.cdc_change_tables$)
            and   o.owner# = u.user#
            and   p.obj# = o.obj#
            and   p.flags =0
            and   bitand(i.property, 2) <>2
            and   u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS''))
            order by num, idx_or_tab desc)';

Line: 1922

   'select 1 from (select sum(tot) CNT from
 ( select count(*) tot
   from dba_tables
   where partitioned=''YES''
   and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
   union all
   select count(*) tot
   from dba_indexes
   where partitioned=''YES''
   and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS''))) where CNT > 0';

Line: 1934

   'select num||'':''||idx_or_tab||'':''||user_id||'':''||ptype||'':''||subptype||'':''
       ||pcnt||'':'' ||subpcnt||'':''||
       pcols||'':''||subpcols||'':''||idx_flags||'':''||
       idx_type||'':''||idx_uk||''|'' my_string from (select * from
          (select /*+ full(o) */ dense_rank() over
                  (order by  decode(i.bo#,null,p.obj#,i.bo#)) NUM,
                  decode(o.type#,1,''I'',2,''T'',null)  IDX_OR_TAB,
                  u.user# USER_ID,
                  decode(p.parttype, 1, ''RANGE'', 2, ''HASH'', 3,
                              ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') PTYPE,
                  decode(mod(p.spare2, 256), 0, null, 2, ''HASH'', 3,
                        ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') SUBPTYPE,
                  p.partcnt PCNT,
                  mod(trunc(p.spare2/65536), 65536) SUBPCNT,
                  p.partkeycols PCOLS,
                  mod(trunc(p.spare2/256), 256) SUBPCOLS,
                  decode(p.flags,0,null,decode(mod(p.flags,3),0,
                             ''LP'',1,''L'', 2,''GP'' ,null)) IDX_FLAGS,
                  decode(i.type#, 1, ''NORMAL''||
                                  decode(bitand(i.property, 4), 0,
                                      '''', 4, ''/REV''),
                  2, ''BITMAP'', 3, ''CLUSTER'', 4, ''IOT - TOP'',
                  5, ''IOT - NESTED'', 6, ''SECONDARY'', 7, ''ANSI'',
                           8, ''LOB'', 9, ''DOMAIN'') IDX_TYPE,
                  decode(i.property, null,null,
                                     decode(bitand(i.property, 1), 0,
                                         ''NONUNIQUE'',
                                     1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
                  from sys.partobj$ p, sys.obj$ o, sys.user$ u, sys.ind$ i
                  where o.obj# = i.obj#(+)
                  and   o.owner# = u.user#
                  and   p.obj# = o.obj#
                  and   u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
                  -- fix bug 3074607 - filter on obj$
                  and o.type# in (1,2,19,20,25,34,35)
            union all
            select dense_rank() over (order by  decode(i.bo#,null,p.obj#,
                                                           i.bo#)) NUM,
                   ''I'' IDX_OR_TAB,
                   u.user# USER_ID,
                   cast(null as varchar2(20)) c0,
                   cast(null as varchar2(20)) c1,
                   cast(null as number) c2,
                   cast( null as number) c3,
                   cols PCOLS,
                   cast(null as number) c4,
                   ''GNP'' IDX_FLAGS,
                   decode(i.type#, 1, ''NORMAL''||
                                 decode(bitand(i.property, 4), 0, '''', 4,
                                                            ''/REV''),
                                  2, ''BITMAP'', 3, ''CLUSTER'', 4,
                                           ''IOT - TOP'',
                                  5, ''IOT - NESTED'', 6, ''SECONDARY'', 7,
                                           ''ANSI'', 8, ''LOB'',
                                  9, ''DOMAIN'') IDX_TYPE,
                   decode(i.property, null,null,
                     decode(bitand(i.property, 1), 0,  ''NONUNIQUE'',  1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
            from sys.partobj$ p, sys.user$ u, sys.obj$ o, sys.ind$ i
            where p.obj# = i.bo#
            and   o.owner# = u.user#
            and   p.obj# = o.obj#
            and   p.flags =0
            and   bitand(i.property, 2) <>2
            and   u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS''))
            order by num, idx_or_tab desc)';

Line: 2016

    'SELECT 1 from v$option where parameter=''OLAP'' and value =''TRUE'' and rownum = 1';

Line: 2018

    'SELECT 1 FROM olapsys.dba$olap_cubes '||
    ' WHERE OWNER <> ''SH'' and rownum = 1 '||
    ' UNION ALL '||
    'SELECT 1 FROM '||
    ' (SELECT count(*) CNT FROM dba_aws '||
    '  where upper(AW_NAME) NOT IN '||
    '  (''EXPRESS'', ''CWMTOECM'', ''AWMD'', ''AWREPORT'', ''AWCREATE10G'', ''AWXML'')'||
    ' ) where CNT > 0';

Line: 2027

    'SELECT OWNER || ''-'' || AW_NAME FROM DBA_AWS ';

Line: 2036

    'SELECT DBID,NAME, VERSION, DETECTED_USAGES, TOTAL_SAMPLES, CURRENTLY_USED, '||
    'to_char(FIRST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') FIRST_USAGE, '||
    'to_char(LAST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_USAGE, '||
    'AUX_COUNT, '||
    'to_char(LAST_SAMPLE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_SAMPLE, '||
    'LAST_SAMPLE_PERIOD, '||
    'replace((nvl(TO_CHAR(substr(FEATURE_INFO,0,4000)),'''')),''|'',''#'') FEATURE_DETAIL '||
    'FROM DBA_FEATURE_USAGE_STATISTICS where version in ( select max(version) from DBA_FEATURE_USAGE_STATISTICS  )' );

Line: 2099

    'with pdbview AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
    'SELECT pdb.pdb, DBID, NAME, VERSION, DETECTED_USAGES, TOTAL_SAMPLES, CURRENTLY_USED, ' ||
    ' to_char(FIRST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') FIRST_USAGE, ' ||
    ' to_char(LAST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_USAGE, ' ||
    ' AUX_COUNT, ' ||
    ' to_char(LAST_SAMPLE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_SAMPLE, ' ||
    ' LAST_SAMPLE_PERIOD, ' ||
    ' replace((nvl(TO_CHAR(substr(FEATURE_INFO,0,4000)),'''')),''|'',''#'') FEATURE_DETAIL ' ||
    'FROM CDB_FEATURE_USAGE_STATISTICS cfus, pdbview pdb ' ||
    'where cfus.version in ' ||
    '  (select max(version) from CDB_FEATURE_USAGE_STATISTICS where con_id = pdb.con_id) ' ||
    '  and cfus.con_id = pdb.con_id ');

Line: 2147

      'SELECT  s.DBID, s.NAME, s.VERSION, s.HIGHWATER, s.LAST_VALUE '||
      ' FROM DBA_HIGH_WATER_MARK_STATISTICS s, V$DATABASE d '||
      ' where s.version in ( select max(version) from dba_high_water_mark_statistics ) ' ||
      ' and s.dbid = d.dbid');

Line: 2162

    'WITH pdbs AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
    'SELECT p.pdb, s.DBID, s.NAME, s.VERSION, s.HIGHWATER, s.LAST_VALUE ' ||
    'FROM CDB_HIGH_WATER_MARK_STATISTICS s, V$DATABASE d, pdbs p ' ||
    'WHERE s.version in ( SELECT max(version) FROM dba_high_water_mark_statistics ) ' ||
    'AND s.dbid = d.dbid ' ||
    'AND s.con_id = p.con_id');

Line: 2183

      'SELECT * FROM (SELECT DBID,VERSION ,to_char(TIMESTAMP,''YYYY-MM-DD HH24:MI:SS''), CPU_COUNT, CPU_CORE_COUNT, CPU_SOCKET_COUNT '||
      'FROM DBA_CPU_USAGE_STATISTICS '||
      'where version in (select max(version) from DBA_CPU_USAGE_STATISTICS) ORDER BY Timestamp desc) WHERE ROWNUM <= 1 ');

Line: 2197

                 'select /*+ NO_PARALLEL(dp) */ '||
                 'sys_context(''USERENV'',''CON_NAME'') as NAME, con_uid as CON_UID, sys_context(''USERENV'',''DB_UNIQUE_NAME'') as SERVICE_NAME '||
                 'from dba_pdbs dp where dp.pdb_name = ''PDB$SEED'' '||
                 'union '||
                 'select /*+ NO_PARALLEL(a) NO_PARALLEL(b) */ a.pdb_name as NAME, a.con_uid as CON_UID, NVL(b.NAME,a.pdb_name) as SERVICE_NAME '||
                 'from dba_pdbs a LEFT JOIN cdb_services b ON a.pdb_name = b.pdb '||
                 'where a.pdb_name <> ''PDB$SEED'' and '||
                 '(b.name is null or regexp_like(b.name,''^''||a.pdb_name||''$|^''||a.pdb_name||''\.'', ''i'')) ORDER BY name');

Line: 2206

                 'select /*+ NO_PARALLEL(dp) */ '||
                 'sys_context(''USERENV'',''CON_NAME'') as NAME, con_uid, sys_context(''USERENV'',''DB_UNIQUE_NAME'') as SERVICE_NAME, ''YES'' as IS_ROOT '||
                 'from dba_pdbs dp where dp.pdb_name = ''PDB$SEED'' '||
                 'union  '||
                 'select /*+ NO_PARALLEL(a) NO_PARALLEL(b) */ a.pdb_name as NAME, a.con_uid, NVL(b.NAME,a.pdb_name) as SERVICE_NAME, ''NO'' as IS_ROOT '||
                 'from dba_pdbs a LEFT JOIN cdb_services b ON a.pdb_name = b.pdb '||
                 'where a.pdb_name <> ''PDB$SEED'' and '||
                 '(b.name is null or regexp_like(b.name,''^''||a.pdb_name||''$|^''||a.pdb_name||''\.'', ''i'')) ORDER BY name');

Line: 2221

  CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;

Line: 2225

      'WITH pdbs AS (SELECT DISTINCT con_id, name pdb, inst_id FROM gv$containers where con_id != 2) ' ||
      'select /*+ NO_PARALLEL(s) */ p.pdb, name, network_name, TO_CHAR(creation_date, ''YYYY-MM-DD HH24:MI:SS''), '||
      'failover_method, failover_type, failover_retries, failover_delay,min_cardinality, max_cardinality, '||
      'goal, dtp, enabled, aq_ha_notifications, clb_goal, edition '||
      'from sys.cdb_services s, pdbs p WHERE s.con_id = p.con_id and p.inst_id = ' || inst_id_row.inst_id
      ,inst_id_row.instance_name);

Line: 2245

    l_sql :=  'select replace(cc.cellname, '':'', ''_'') , cell.name, cell.id, cell.cellVersion, cell.releaseVersion' ||
     ' from v$cell_config cc, ' ||
     ' xmltable ' ||
     '(''/cli-output/cell'' passing xmltype(cc.confval) ' ||
     ' columns ' ||
     ' name varchar2(256) path ''name'', ' ||
     ' id varchar2(256) path ''id'', ' ||
     ' cellVersion varchar2(256) path ''cellVersion'', ' ||
     ' releaseVersion varchar2(256) path ''releaseVersion'' ' ||
     ') cell where cc.conftype=''CELL''';

Line: 2273

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2280

        l_sql := 'select cell.* ' ||
    ' from ' ||
    ' v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/cell'' passing xmltype(cc.confval) ' ||
    ' columns ' ||
    ' name varchar2(256) path ''name'', ' ||
    ' bmcType varchar2(256) path ''bmcType'', ' ||
    ' cellVersion varchar2(256) path ''cellVersion'', ' ||
    ' cpuCount varchar2(256) path ''cpuCount'', ' ||
    ' fanCount varchar2(256) path ''fanCount'', ' ||
    ' id varchar2(256) path ''id'', ' ||
    ' interconnectCount varchar2(256) path ''interconnectCount'', ' ||
    ' iormBoost varchar2(256) path ''iormBoost'', '||
    ' ipaddress1 varchar2(256) path ''ipaddress1'', ' ||
    ' ipaddress2 varchar2(256) path ''ipaddress2'', ' ||
    ' ipaddress3 varchar2(256) path ''ipaddress3'', ' ||
    ' ipaddress4 varchar2(256) path ''ipaddress4'', ' ||
    '  kernelVersion varchar2(256) path ''kernelVersion'', ' ||
    ' makeModel varchar2(256) path ''makeModel'', ' ||
    ' metricHistoryDays varchar2(256) path ''metricHistoryDays'', ' ||
    ' powerCount varchar2(256) path ''powerCount'' ' ||
    ' ) cell where ' ||
    ' cc.conftype=''CELL'' and cellname='''|| l_ip_var || '''';

Line: 2327

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2334

  l_sql := 'select gds.name, gds.availableTo, gds.cellDisk, gds.gdcomment, ' ||
    'gds.creationTime, gds.errorCount, gds.id, round(gds.offset/1048576), ' ||
    'round(gds.gdsize/1048576), gds.status ' ||
    ' from ' ||
    ' v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/griddisk'' passing xmltype(cc.confval) ' ||
    ' columns ' ||
    ' name varchar2(256) path ''name'', ' ||
    ' availableTo varchar2(256) path ''availableTo'', ' ||
    ' cellDisk varchar2(256) path ''cellDisk'', ' ||
    ' gdcomment varchar2(4000) path ''comment'', ' ||
    ' creationTime varchar2(256) path ''creationTime'', ' ||
    ' errorCount varchar2(256) path ''errorCount'', ' ||
    ' id varchar2(256) path ''id'', ' ||
    ' offset varchar2(256) path ''offset'', ' ||
    ' gdsize varchar2(256) path ''size'', ' ||
    ' status varchar2(256) path ''status'' ' ||
    ' ) gds  where cc.conftype=''GRIDDISKS'' and cellname='''|| l_ip_var || '''';

Line: 2377

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2385

    ' select cds.name, cds.cdcomment, cds.creationTime, cds.deviceName, ' ||
    ' cds.devicePartition, cds.errorCount,round(cds.freeSpace/1073741824, 2), cds.id, cds.lun, ' ||
    ' cds.raidLevel,round(cds.cdsize/1073741824, 2), cds.status ' ||
    ' from ' ||
    ' v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/celldisk'' passing xmltype(cc.confval) ' ||
    ' columns ' ||
    ' name varchar2(256) path ''name'', ' ||
    ' cdcomment varchar2(4000) path ''comment'', ' ||
    ' creationTime varchar2(256) path ''creationTime'', ' ||
    ' deviceName varchar2(256) path ''deviceName'', ' ||
    ' devicePartition varchar2(256) path ''devicePartition'', ' ||
    ' errorCount varchar2(256) path ''errorCount'', ' ||
    ' freeSpace varchar2(256) path ''freeSpace'', ' ||
    ' id varchar2(256) path ''id'', ' ||
    ' lun varchar2(256) path ''lun'', ' ||
    ' raidLevel varchar2(256) path ''raidLevel'', ' ||
    ' cdsize varchar2(256) path ''size'', ' ||
    ' status varchar2(256) path ''status'' ' ||
    ' ) cds  where cc.conftype=''CELLDISKS'' and cellname='''|| l_ip_var || '''';

Line: 2430

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2438

    ' select luns.name, luns.cellDisk, luns.deviceName, luns.id, luns.isSystemLun,  ' ||
    ' luns.lunAutoCreate, round(luns.lunSize/1073741824, 2), luns.physicalDevices, ' ||
    ' luns.raidLevel, luns.status ' ||
    ' from ' ||
    '     v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/lun'' passing xmltype(cc.confval) ' ||
    '  columns ' ||
    '   name varchar2(256) path ''name'', ' ||
    '   cellDisk varchar2(256) path ''cellDisk'', ' ||
    '   deviceName varchar2(256) path ''deviceName'', ' ||
    '   id varchar2(256) path ''id'', ' ||
    '   isSystemLun varchar2(256) path ''isSystemLun'', ' ||
    '   lunAutoCreate varchar2(256) path ''lunAutoCreate'', ' ||
    '   lunSize varchar2(256) path ''lunSize'', ' ||
    '   physicalDevices varchar2(256) path ''physicalDevices'', ' ||
    '   raidLevel varchar2(256) path ''raidLevel'', ' ||
    '   status varchar2(256) path ''status'' ' ||
    ' ) luns where cc.conftype=''LUNS'' and cellname='''|| l_ip_var || '''';

Line: 2481

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2489

    ' select pds.name, pds.id, pds.luns, pds.physicalInsertTime,  ' ||
    ' round (pds.physicalSize/1073741824, 2), pds.status ' ||
    ' from ' ||
    '     v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/physicaldisk'' passing xmltype(cc.confval) ' ||
    '  columns ' ||
    '   name varchar2(256) path ''name'', ' ||
    '   id varchar2(256) path ''id'', ' ||
    '   luns varchar2(256) path ''luns'', ' ||
    '   physicalInsertTime varchar2(256) path ''physicalInsertTime'', ' ||
    '   physicalSize varchar2(256) path ''physicalSize'', ' ||
    '   status varchar2(256) path ''status'' ' ||
    ' ) pds where cc.conftype like ''PHYSICAL%'' and cellname='''|| l_ip_var || '''';

Line: 2527

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2535

    ' select iorm.* ' ||
    ' from ' ||
    '     v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/interdatabaseplan'' passing xmltype(cc.confval) ' ||
    '  columns ' ||
    '   name varchar2(256) path ''name'', ' ||
    '   catPlan varchar2(256) path ''catPlan'', ' ||
    '   dbPlan varchar2(256) path ''dbPlan'', ' ||
    '   status varchar2(256) path ''status'' ' ||
    ' ) iorm where cc.conftype =''IORM'' and cellname='''|| l_ip_var || '''';

Line: 2569

    DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
    DBMS_SQL.NATIVE);

Line: 2576

  l_sql := 'select cell.* ' ||
    '  from ' ||
    '  v$cell_config cc, ' ||
    ' xmltable ' ||
    ' (''/cli-output/cell'' passing xmltype(cc.confval) ' ||
    ' columns ' ||
    '   releaseTrackingBug varchar2(256) path ''releaseTrackingBug'' ' ||
    ' ) cell where ' ||
    '   cc.conftype=''CELL'' and cellname='''|| l_ip_var || '''';

Line: 2610

   SELECT  name into l_db_name FROM v$database ;

Line: 2615

   select PARALLEL into l_par  from v$instance;

Line: 2633

   select value into l_db_characterset from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';

Line: 2638

   select LPAD(version,10,'0') into l_vers from v$instance;

Line: 2641

         'select count(*) from dba_registry where COMP_NAME = ''Oracle XML Database'' ' ||
         'and STATUS = ''VALID''' ;

Line: 2679

    execute immediate 'select * from (select instance_name ' ||
                ' from (select '' '' instance_name from dual ' ||
                ' union all ' ||
                ' select instance_name from v$asm_client) ' ||
                ' order by instance_name desc) ' ||
                ' where rownum = 1 ' into l_asm_instance_name;

Line: 2750

  select dbid into g_dbID from v$database;

Line: 2751

  select version into g_db_version from v$instance;

Line: 2798

    select dbid into g_dbID from v$database;

Line: 2799

    select version into g_db_version from v$instance;

Line: 2862

  select host_name into l_hostName from v$instance;

Line: 2863

  select value into l_dbUniqueName from v$parameter where name='db_unique_name';

Line: 2864

  select value into l_dbDomain from v$parameter where name='db_domain';

Line: 2873

    select value into l_diagDest from v$parameter where lower(name)='diagnostic_dest';

Line: 2880

  select value into l_dbName from v$parameter where name='db_name';

Line: 2881

  select value into l_dbInstanceName from v$parameter where name='instance_name';

Line: 2905

    select dbid into g_dbID from v$database;

Line: 2906

    select version into g_db_version from v$instance;