olap_info.sql

 

spool olap_info.html set markup html on spool on set lines 200 pages 5000 echo on col comp_name format a40 col owner format a18 col aw_name format a22 col object_name format a40 col object_type format a40 col product format a40 col version format a15 col status format a17 col schema format a11 col action_time format a35 col version format a11 col comments format a45 col grantee format a20 col granted_role format a20 col username format a9 col awname format a20 col name format a20 col value format a20 col description format a70 col action_time format a30 col bundle_series format a13 col timestamp format a31 col script format a80 col identifier format a10 col statement format a80   select * from v$version; select parameter, value from v$option where parameter = 'OLAP'; select * from registry$history order by action_time desc; select * from sys.registry$error where identifier='XOQ'; select * from sys.registry$error where identifier='APS'; select * from sys.registry$error where identifier='AMD'; select patch_id, version, action, action_time, status, description, bundle_id, bundle_series from dba_registry_sqlpatch; select comp_id, comp_name, version, status, schema from dba_registry order by status; select comp_id, comp_name, version, status, schema from dba_registry where comp_name like '%OLAP%'; select * from dba_role_privs where grantee = 'SYS' and granted_role like '%OLAP%'; select name "FEATURE", version, detected_usages, first_usage_date "FROM", last_usage_date "TO", currently_used from DBA_FEATURE_USAGE_STATISTICS where name like '%OLAP%'; select * from dba_aws order by owner, aw_name; select username, awname, owner#, awseq# from aw$, dba_users where user_id = owner#; select owner, object_name, object_type, created from dba_objects where object_name in (select 'AW$'||aw_name from dba_aws) order by 1,2; select object_name, object_type from dba_objects where owner='SYS' and object_name||'.'||object_type in (select object_name||'.'||object_type from dba_objects where owner='OLAPSYS'); select name, value, description from v$parameter where name = 'compatible'; select username, account_status, default_tablespace from dba_users where username = 'OLAPSYS'; select count(*) from dba_objects where owner='OLAPSYS'; select owner, count(*) "INVALID" from dba_objects where status <> 'VALID' group by owner; select owner, object_name, object_type, status from dba_objects where status !='VALID' and owner in ('SYS','OLAPSYS') order by owner; select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and OWNER = 'PUBLIC'; select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and OWNER = 'XDB'; select owner, object_name, object_type, status FROM dba_objects WHERE (owner='OLAPSYS' AND status!='VALID') OR (owner='SYS' AND (object_name like '%OLAP%' OR object_name like '%oracle/AWAction%' OR object_name like '%oracle/AWXML%') AND status!='VALID') ORDER BY 1,2; select owner, object_name, object_type, status from dba_objects where status='INVALID' and object_type != 'MATERIALIZED VIEW' order by owner;