set echo off
set pagesize 9999
set linesize 1000
set verify off
set trimspool on
set trimout on
set feedback 6
set recsep off
set Numwidth 20
set longchunk 60000
set long 9999999
set termout on
set arraysize 150
set escape ~
set null ~~~
set heading on

ttitle "Passwords" skip, skip
column username format a25
column password format a25
select username, password from dba_users order by username;

prompt
prompt
prompt NLS Parameters:
prompt ---------------

prompt
ttitle "Session Parameters" skip, skip
column  parameter format a31
column  value format a41
select * from NLS_SESSION_PARAMETERS;

prompt
ttitle "Instance Parameters" skip, skip
select * from NLS_INSTANCE_PARAMETERS;
prompt

prompt
ttitle "Database Parameters" skip, skip
select * from NLS_DATABASE_PARAMETERS;
prompt
prompt
prompt


ttitle off
set heading off
set termout off

clear breaks
clear columns
clear computes

col a fold_after

spool dc03.tmp
select 'set termout off' a, 
'spool r2_' || owner || '.sql' a, 
'prompt connect / as SYSDBA' a,
'prompt set termout off' a,
'prompt spool dc221.tmp' a, 
'prompt @dc22.tmp ' || owner a, 
'prompt spool off' a, 
'prompt @dc23.tmp ' || owner a, 
'prompt set termout off' a, 
'prompt spool dc222.tmp' a, 
'prompt set echo on' a, 
'prompt @dc24.tmp' a, 
'prompt spool off' a, 
'prompt set echo off' a, 
'prompt set pagesize 55' a, 
'prompt set linesize 80' a, 
'prompt set arraysize 150' a, 
'prompt set termout on' a, 
'spool off' a, 
'set termout on'
from dba_objects
where owner != 'SYS' AND 
      owner != 'SYSTEM' AND 
      owner != 'ANONYMOUS' AND
	  owner != 'APEX_030200' AND
      owner != 'APEX_PUBLIC_USER' AND
	  owner != 'APPQOSSYS' AND
      owner != 'AURORA$JIS$UTILITY$' AND
      owner != 'AURORA$ORB$UNAUTHENTICATED' AND
      owner != 'BI' AND
      owner != 'CTXSYS' AND
      owner != 'DBADMIN' AND 
      owner != 'DBSNMP' AND
      owner != 'DEMO' AND
      owner != 'DIP' AND
      owner != 'DMSYS' AND
      owner != 'DRSYS' AND
      owner != 'EXFSYS' AND
      owner != 'FLOWS_030000' AND
      owner != 'FLOWS_FILES' AND
      owner != 'HR' AND
      owner != 'IX' AND
      owner != 'MDDATA' AND
      owner != 'MDSYS' AND
      owner != 'MGMT_VIEW' AND
      owner != 'MTSSYS' AND
      owner != 'ODM' AND
      owner != 'ODM_MTR' AND
      owner != 'OE' AND
      owner != 'OLAPDBA' AND
      owner != 'OLAPSVR' AND
      owner != 'OLAPSYS' AND
      owner != 'ORA_MONITOR' AND 
      owner != 'ORACLE_OCM' AND
      owner != 'ORDPLUGINS' AND
      owner != 'ORDDATA' AND
      owner != 'ORDSYS' AND
      owner != 'OSE$HTTP$ADMIN' AND
      owner != 'OUTLN' AND
      owner != 'OWBSYS' AND
	  owner != 'OWBSYS_AUDIT' AND
      owner != 'PM' AND
      owner != 'PUBLIC' AND 
      owner != 'QS' AND
      owner != 'QS_ADM' AND
      owner != 'QS_CB' AND
      owner != 'QS_CBADM' AND
      owner != 'QS_CS' AND
      owner != 'QS_ES' AND
      owner != 'QS_OS' AND
      owner != 'QS_WS' AND
      owner != 'RMAN'AND
      owner != 'SCOTT' AND
      owner != 'SH' AND
      owner != 'SI_INFORMTN_SCHEMA' AND
      owner != 'SPATIAL_CSW_ADMIN_USR' AND
      owner != 'SPATIAL_WFS_ADMIN_USR' AND
      owner != 'SYSMAN' AND
      owner != 'TSMSYS' AND
      owner != 'WKPROXY' AND
      owner != 'WKSYS' AND
      owner != 'WK_TEST' AND
      owner != 'WMSYS' AND
      owner != 'XDB' AND
      owner != 'XS$NULL'
group by owner;

spool off

clear columns


col a fold_after

spool dc04.tmp
select 'set termout off' a, 
'spool r2_80_' || owner || '.sql' a, 
'prompt connect internal/' a,
'prompt set termout off' a,
'prompt spool ' || owner || '_sch.txt' a, 
'prompt spool dc221.tmp' a, 
'prompt @dc22.tmp ' || owner a, 
'prompt spool off' a, 
'prompt @dc23.tmp ' || owner a, 
'prompt set termout off' a, 
'prompt spool dc222.tmp' a, 
'prompt set echo on' a, 
'prompt @dc24.tmp' a, 
'prompt spool off' a, 
'prompt set echo off' a, 
'prompt set pagesize 55' a, 
'prompt set linesize 80' a, 
'prompt set arraysize 150' a, 
'prompt set termout on' a, 
'spool off' a, 
'set termout on'
from dba_objects
where owner != 'SYS' AND 
      owner != 'SYSTEM' AND 
      owner != 'ANONYMOUS' AND
	  owner != 'APEX_030200' AND
      owner != 'APEX_PUBLIC_USER' AND
	  owner != 'APPQOSSYS' AND
      owner != 'AURORA$JIS$UTILITY$' AND
      owner != 'AURORA$ORB$UNAUTHENTICATED' AND
      owner != 'BI' AND
      owner != 'CTXSYS' AND
      owner != 'DBADMIN' AND 
      owner != 'DBSNMP' AND
      owner != 'DEMO' AND
      owner != 'DIP' AND
      owner != 'DMSYS' AND
      owner != 'DRSYS' AND
      owner != 'EXFSYS' AND
      owner != 'FLOWS_030000' AND
      owner != 'FLOWS_FILES' AND
      owner != 'HR' AND
      owner != 'IX' AND
      owner != 'MDDATA' AND
      owner != 'MDSYS' AND
      owner != 'MGMT_VIEW' AND
      owner != 'MTSSYS' AND
      owner != 'ODM' AND
      owner != 'ODM_MTR' AND
      owner != 'OE' AND
      owner != 'OLAPDBA' AND
      owner != 'OLAPSVR' AND
      owner != 'OLAPSYS' AND
      owner != 'ORA_MONITOR' AND 
      owner != 'ORACLE_OCM' AND
      owner != 'ORDPLUGINS' AND
      owner != 'ORDDATA' AND
      owner != 'ORDSYS' AND
      owner != 'OSE$HTTP$ADMIN' AND
      owner != 'OUTLN' AND
      owner != 'OWBSYS' AND
	  owner != 'OWBSYS_AUDIT' AND
      owner != 'PM' AND
      owner != 'PUBLIC' AND 
      owner != 'QS' AND
      owner != 'QS_ADM' AND
      owner != 'QS_CB' AND
      owner != 'QS_CBADM' AND
      owner != 'QS_CS' AND
      owner != 'QS_ES' AND
      owner != 'QS_OS' AND
      owner != 'QS_WS' AND
      owner != 'RMAN'AND
      owner != 'SCOTT' AND
      owner != 'SH' AND
      owner != 'SI_INFORMTN_SCHEMA' AND
      owner != 'SPATIAL_CSW_ADMIN_USR' AND
      owner != 'SPATIAL_WFS_ADMIN_USR' AND
      owner != 'SYSMAN' AND
      owner != 'TSMSYS' AND
      owner != 'WKPROXY' AND
      owner != 'WKSYS' AND
      owner != 'WK_TEST' AND
      owner != 'WMSYS' AND
      owner != 'XDB' AND
      owner != 'XS$NULL'
group by owner;

spool off

clear columns
@dc03.tmp
@dc04.tmp

set termout off

set feedback off

col a fold_after

spool dc021.tmp
prompt echo ~"Survey Script Commands~";;
prompt echo ~"=======================~";;
prompt @record on;;
prompt `environment -var HOME_BASE -get`;;
prompt @record off;;
prompt string $myHomebase;;
prompt GetCmdData("environment::value::value", $myHomebase);;
select 'echo "--------Begin - ' || owner || '--------";' a,
'if (prompt (~"Do you want to survey ' || owner || '?~")) {' a,
'`del dc21.tmp`;' a, 
'`move r2_' || owner || '.sql dc21.tmp`;' a, 
'_PFre_RunCmdAndWaitUntilFinished("background run -command $myHomebase\\dc2.bat -redirect");' a,
'_PFre_MyBanner( ~"Renaming files prior to downloading.~" );' a,
'`move dc221.tmp ' || owner || '_dc221.tmp`;' a,
'`move dc222.tmp ' || owner || '_dc222.tmp`;' a,
'`background get ' || owner || '_dc221.tmp -name ' || owner || '_database_schema_`;' a,
'`background get ' || owner || '_dc222.tmp -name ' || owner || '_database_sample_`;' a,
'}' a,
'echo "--------Done - ' || owner || '--------";'
from dba_objects do
where owner != 'SYS' AND 
      owner != 'SYSTEM' AND 
      owner != 'ANONYMOUS' AND
	  owner != 'APEX_030200' AND
      owner != 'APEX_PUBLIC_USER' AND
	  owner != 'APPQOSSYS' AND
      owner != 'AURORA$JIS$UTILITY$' AND
      owner != 'AURORA$ORB$UNAUTHENTICATED' AND
      owner != 'BI' AND
      owner != 'CTXSYS' AND
      owner != 'DBADMIN' AND 
      owner != 'DBSNMP' AND
      owner != 'DEMO' AND
      owner != 'DIP' AND
      owner != 'DMSYS' AND
      owner != 'DRSYS' AND
      owner != 'EXFSYS' AND
      owner != 'FLOWS_030000' AND
      owner != 'FLOWS_FILES' AND
      owner != 'HR' AND
      owner != 'IX' AND
      owner != 'MDDATA' AND
      owner != 'MDSYS' AND
      owner != 'MGMT_VIEW' AND
      owner != 'MTSSYS' AND
      owner != 'ODM' AND
      owner != 'ODM_MTR' AND
      owner != 'OE' AND
      owner != 'OLAPDBA' AND
      owner != 'OLAPSVR' AND
      owner != 'OLAPSYS' AND
      owner != 'ORA_MONITOR' AND 
      owner != 'ORACLE_OCM' AND
      owner != 'ORDPLUGINS' AND
      owner != 'ORDDATA' AND
      owner != 'ORDSYS' AND
      owner != 'OSE$HTTP$ADMIN' AND
      owner != 'OUTLN' AND
      owner != 'OWBSYS' AND
	  owner != 'OWBSYS_AUDIT' AND
      owner != 'PM' AND
      owner != 'PUBLIC' AND 
      owner != 'QS' AND
      owner != 'QS_ADM' AND
      owner != 'QS_CB' AND
      owner != 'QS_CBADM' AND
      owner != 'QS_CS' AND
      owner != 'QS_ES' AND
      owner != 'QS_OS' AND
      owner != 'QS_WS' AND
      owner != 'RMAN'AND
      owner != 'SCOTT' AND
      owner != 'SH' AND
      owner != 'SI_INFORMTN_SCHEMA' AND
      owner != 'SPATIAL_CSW_ADMIN_USR' AND
      owner != 'SPATIAL_WFS_ADMIN_USR' AND
      owner != 'SYSMAN' AND
      owner != 'TSMSYS' AND
      owner != 'WKPROXY' AND
      owner != 'WKSYS' AND
      owner != 'WK_TEST' AND
      owner != 'WMSYS' AND
      owner != 'XDB' AND
      owner != 'XS$NULL'
group by owner;

spool off

set feedback off

spool dc022.tmp
prompt echo ~"Survey Script Commands~";;
prompt echo ~"=======================~";;
prompt @record on;;
prompt `environment -var HOME_BASE -get`;;
prompt @record off;;
prompt string $myHomebase;;
prompt GetCmdData("environment::value::value", $myHomebase);;
select 'echo "--------Begin - ' || owner || '--------";' a,
'if (prompt (~"Do you want to survey ' || owner || '?~")) {' a,
'`del dc21.tmp`;' a, 
'`move r2_80_' || owner || '.sql dc21.tmp`;' a, 
'_PFre_RunCmdAndWaitUntilFinished("background run -command $myHomebase\\dc2.bat -redirect");' a,
'_PFre_MyBanner( ~"Renaming files prior to downloading.~" );' a,
'`move dc221.tmp ' || owner || '_dc221.tmp`;' a,
'`move dc222.tmp ' || owner || '_dc222.tmp`;' a,
'`background get ' || owner || '_dc221.tmp -name ' || owner || '_database_schema_`;' a,
'`background get ' || owner || '_dc222.tmp -name ' || owner || '_database_sample_`;' a,
'}' a,
'echo "--------Done - ' || owner || '--------";'
from dba_objects
where owner != 'SYS' AND 
      owner != 'SYSTEM' AND 
      owner != 'ANONYMOUS' AND
	  owner != 'APEX_030200' AND
      owner != 'APEX_PUBLIC_USER' AND
	  owner != 'APPQOSSYS' AND
      owner != 'AURORA$JIS$UTILITY$' AND
      owner != 'AURORA$ORB$UNAUTHENTICATED' AND
      owner != 'BI' AND
      owner != 'CTXSYS' AND
      owner != 'DBADMIN' AND 
      owner != 'DBSNMP' AND
      owner != 'DEMO' AND
      owner != 'DIP' AND
      owner != 'DMSYS' AND
      owner != 'DRSYS' AND
      owner != 'EXFSYS' AND
      owner != 'FLOWS_030000' AND
      owner != 'FLOWS_FILES' AND
      owner != 'HR' AND
      owner != 'IX' AND
      owner != 'MDDATA' AND
      owner != 'MDSYS' AND
      owner != 'MGMT_VIEW' AND
      owner != 'MTSSYS' AND
      owner != 'ODM' AND
      owner != 'ODM_MTR' AND
      owner != 'OE' AND
      owner != 'OLAPDBA' AND
      owner != 'OLAPSVR' AND
      owner != 'OLAPSYS' AND
      owner != 'ORA_MONITOR' AND 
      owner != 'ORACLE_OCM' AND
      owner != 'ORDPLUGINS' AND
      owner != 'ORDDATA' AND
      owner != 'ORDSYS' AND
      owner != 'OSE$HTTP$ADMIN' AND
      owner != 'OUTLN' AND
      owner != 'OWBSYS' AND
	  owner != 'OWBSYS_AUDIT' AND
      owner != 'PM' AND
      owner != 'PUBLIC' AND 
      owner != 'QS' AND
      owner != 'QS_ADM' AND
      owner != 'QS_CB' AND
      owner != 'QS_CBADM' AND
      owner != 'QS_CS' AND
      owner != 'QS_ES' AND
      owner != 'QS_OS' AND
      owner != 'QS_WS' AND
      owner != 'RMAN'AND
      owner != 'SCOTT' AND
      owner != 'SH' AND
      owner != 'SI_INFORMTN_SCHEMA' AND
      owner != 'SPATIAL_CSW_ADMIN_USR' AND
      owner != 'SPATIAL_WFS_ADMIN_USR' AND
      owner != 'SYSMAN' AND
      owner != 'TSMSYS' AND
      owner != 'WKPROXY' AND
      owner != 'WKSYS' AND
      owner != 'WK_TEST' AND
      owner != 'WMSYS' AND
      owner != 'XDB' AND
      owner != 'XS$NULL'
group by owner;

spool off

set termout on

prompt
prompt Users of Interest:
prompt -------------------

prompt
ttitle "Passwords" skip, skip
column username format a25
column password format a25
select username, password from dba_users
where username != 'SYS' AND 
      username != 'SYSTEM' AND 
      username != 'ANONYMOUS' AND
	  username != 'APEX_030200' AND
      username != 'APEX_PUBLIC_USER' AND
	  username != 'APPQOSSYS' AND
      username != 'AURORA$JIS$UTILITY$' AND
      username != 'AURORA$ORB$UNAUTHENTICATED' AND
      username != 'BI' AND
      username != 'CTXSYS' AND
      username != 'DBADMIN' AND 
      username != 'DBSNMP' AND
      username != 'DEMO' AND
      username != 'DIP' AND
      username != 'DMSYS' AND
      username != 'DRSYS' AND
      username != 'EXFSYS' AND
      username != 'FLOWS_030000' AND
      username != 'FLOWS_FILES' AND
      username != 'HR' AND
      username != 'IX' AND
      username != 'MDDATA' AND
      username != 'MDSYS' AND
      username != 'MGMT_VIEW' AND
      username != 'MTSSYS' AND
      username != 'ODM' AND
      username != 'ODM_MTR' AND
      username != 'OE' AND
      username != 'OLAPDBA' AND
      username != 'OLAPSVR' AND
      username != 'OLAPSYS' AND
      username != 'ORA_MONITOR' AND 
      username != 'ORACLE_OCM' AND
      username != 'ORDPLUGINS' AND
      username != 'ORDDATA' AND
      username != 'ORDSYS' AND
      username != 'OSE$HTTP$ADMIN' AND
      username != 'OUTLN' AND
      username != 'OWBSYS' AND
	  username != 'OWBSYS_AUDIT' AND
      username != 'PM' AND
      username != 'PUBLIC' AND 
      username != 'QS' AND
      username != 'QS_ADM' AND
      username != 'QS_CB' AND
      username != 'QS_CBADM' AND
      username != 'QS_CS' AND
      username != 'QS_ES' AND
      username != 'QS_OS' AND
      username != 'QS_WS' AND
      username != 'RMAN'AND
      username != 'SCOTT' AND
      username != 'SH' AND
      username != 'SI_INFORMTN_SCHEMA' AND
      username != 'SPATIAL_CSW_ADMIN_USR' AND
      username != 'SPATIAL_WFS_ADMIN_USR' AND
      username != 'SYSMAN' AND
      username != 'TSMSYS' AND
      username != 'WKPROXY' AND
      username != 'WKSYS' AND
      username != 'WK_TEST' AND
      username != 'WMSYS' AND
      username != 'XDB' AND
      username != 'XS$NULL'
order by username;
prompt ----------------------------------------

prompt
ttitle 'Object Counts for Users of Interest' skip skip
prompt -------------------------------------

prompt
col ow format a18 heading 'Owner'
col ta format 999,999 heading 'Tables'
col ve format 999,999 heading 'Views'

select owner ow,
       sum(decode(object_type,'TABLE',1,0)) ta ,
       sum(decode(object_type,'VIEW',1,0)) ve
from dba_objects
where owner != 'SYS' AND 
      owner != 'SYSTEM' AND
      owner != 'ANONYMOUS' AND
	  owner != 'APEX_030200' AND
      owner != 'APEX_PUBLIC_USER' AND
	  owner != 'APPQOSSYS' AND
      owner != 'AURORA$JIS$UTILITY$' AND
      owner != 'AURORA$ORB$UNAUTHENTICATED' AND
      owner != 'BI' AND
      owner != 'CTXSYS' AND
      owner != 'DBADMIN' AND 
      owner != 'DBSNMP' AND
      owner != 'DEMO' AND
      owner != 'DIP' AND
      owner != 'DMSYS' AND
      owner != 'DRSYS' AND
      owner != 'EXFSYS' AND
      owner != 'FLOWS_030000' AND
      owner != 'FLOWS_FILES' AND
      owner != 'HR' AND
      owner != 'IX' AND
      owner != 'MDDATA' AND
      owner != 'MDSYS' AND
      owner != 'MGMT_VIEW' AND
      owner != 'MTSSYS' AND
      owner != 'ODM' AND
      owner != 'ODM_MTR' AND
      owner != 'OE' AND
      owner != 'OLAPDBA' AND
      owner != 'OLAPSVR' AND
      owner != 'OLAPSYS' AND
      owner != 'ORA_MONITOR' AND 
      owner != 'ORACLE_OCM' AND
      owner != 'ORDPLUGINS' AND
      owner != 'ORDDATA' AND
      owner != 'ORDSYS' AND
      owner != 'OSE$HTTP$ADMIN' AND
      owner != 'OUTLN' AND
      owner != 'OWBSYS' AND
	  owner != 'OWBSYS_AUDIT' AND
      owner != 'PM' AND
      owner != 'PUBLIC' AND 
      owner != 'QS' AND
      owner != 'QS_ADM' AND
      owner != 'QS_CB' AND
      owner != 'QS_CBADM' AND
      owner != 'QS_CS' AND
      owner != 'QS_ES' AND
      owner != 'QS_OS' AND
      owner != 'QS_WS' AND
      owner != 'RMAN'AND
      owner != 'SCOTT' AND
      owner != 'SH' AND
      owner != 'SI_INFORMTN_SCHEMA' AND
      owner != 'SPATIAL_CSW_ADMIN_USR' AND
      owner != 'SPATIAL_WFS_ADMIN_USR' AND
      owner != 'SYSMAN' AND
      owner != 'TSMSYS' AND
      owner != 'WKPROXY' AND
      owner != 'WKSYS' AND
      owner != 'WK_TEST' AND
      owner != 'WMSYS' AND
      owner != 'XDB' AND
      owner != 'XS$NULL'
group by owner
order by ta;
prompt ----------------------------------------

prompt

ttitle off
set heading off

prompt ----------------------------------------------------------------------------

prompt
prompt
prompt Language Parameters of Interest:
prompt ---------------------------------

select * from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';
prompt
prompt
prompt

prompt Run this to set the NLS_LANG variable:
prompt ---------------------------------------

select 'environment -var NLS_LANG -set AMERICAN_AMERICA.' || value from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';
prompt
prompt ----------------------------------------------------------------------------

prompt
prompt
prompt
set termout off
set heading on
spool dc05.tmp
prompt
prompt Oracle passwords.
prompt -----------------

prompt
column username format a25
column password format a25
select username, password from dba_users
where username != 'ANONYMOUS' AND
	  username != 'APEX_030200' AND
      username != 'APEX_PUBLIC_USER' AND
	  username != 'APPQOSSYS' AND
      username != 'AURORA$JIS$UTILITY$' AND
      username != 'AURORA$ORB$UNAUTHENTICATED' AND
      username != 'BI' AND
      username != 'CTXSYS' AND
      username != 'DBADMIN' AND 
      username != 'DBSNMP' AND
      username != 'DEMO' AND
      username != 'DIP' AND
      username != 'DMSYS' AND
      username != 'DRSYS' AND
      username != 'EXFSYS' AND
      username != 'FLOWS_030000' AND
      username != 'FLOWS_FILES' AND
      username != 'HR' AND
      username != 'IX' AND
      username != 'MDDATA' AND
      username != 'MDSYS' AND
      username != 'MGMT_VIEW' AND
      username != 'MTSSYS' AND
      username != 'ODM' AND
      username != 'ODM_MTR' AND
      username != 'OE' AND
      username != 'OLAPDBA' AND
      username != 'OLAPSVR' AND
      username != 'OLAPSYS' AND
      username != 'ORA_MONITOR' AND 
      username != 'ORACLE_OCM' AND
      username != 'ORDPLUGINS' AND
      username != 'ORDDATA' AND
      username != 'ORDSYS' AND
      username != 'OSE$HTTP$ADMIN' AND
      username != 'OUTLN' AND
      username != 'OWBSYS' AND
	  username != 'OWBSYS_AUDIT' AND
      username != 'PM' AND
      username != 'PUBLIC' AND 
      username != 'QS' AND
      username != 'QS_ADM' AND
      username != 'QS_CB' AND
      username != 'QS_CBADM' AND
      username != 'QS_CS' AND
      username != 'QS_ES' AND
      username != 'QS_OS' AND
      username != 'QS_WS' AND
      username != 'RMAN'AND
      username != 'SCOTT' AND
      username != 'SH' AND
      username != 'SI_INFORMTN_SCHEMA' AND
      username != 'SPATIAL_CSW_ADMIN_USR' AND
      username != 'SPATIAL_WFS_ADMIN_USR' AND
      username != 'SYSMAN' AND
      username != 'TSMSYS' AND
      username != 'WKPROXY' AND
      username != 'WKSYS' AND
      username != 'WK_TEST' AND
      username != 'WMSYS' AND
      username != 'XDB' AND
      username != 'XS$NULL'
order by username;
prompt ----------------------------------------------------

prompt
spool off