/*
SQUAD DBA ORACLE - TOTVS CLOUD
OBS: Para execução desse script o usuário conectado ao banco precisa 
de privilégios de leitura nos objetos v$instance, dba_segments e dba_users.
*/

set heading on
set verify on
set term on
set serveroutput on size 1000000
set wrap on
set linesize 200
set pagesize 1000
set feedback off
set markup csv on

spool migrador_totvs_consinco.csv

--set markup html on
set markup html on spool on preformat off entmap on

clear screen

PROMPT
PROMPT ==== USER INFORMATION ====
PROMPT

set lines 400
set pages 1000
col username for a25
col tablespace_name for a25
col temporary_tablespace for a25
col default_tablespace for a25
select u.username, u.default_tablespace, u.temporary_tablespace, s.tablespace_name tablespaces, nvl(round(sum(s.bytes)/1024/1024/1024,2),0) as size_gb
from dba_segments s
right join dba_users u
on s.owner = u.username 
where u.username not in ('SYS','SYSTEM','DBSNMP','APPQOSSYS','GSMCATUSER','XS$NULL','MDDATA','REMOTE_SCHEDULER_AGENT'
,'DBSFWUSER','SYSBACKUP','GSMUSER','GGSYS','ANONYMOUS','SYSRAC','CTXSYS','OJVMSYS','SI_INFORMTN_SCHEMA','DVF'                   
,'DVSYS','AUDSYS','GSMADMIN_INTERNAL','DIP','ORDPLUGINS','MDSYS','OLAPSYS','LBACSYS','ORDDATA','SYSKM','OUTLN'                 
,'ORACLE_OCM','SYS$UMF','XDB','WMSYS','ORDSYS','SYSDG')                 
group by u.username, s.tablespace_name, u.temporary_tablespace, u.default_tablespace
order by 5 desc, 4,1; 

PROMPT
PROMPT ==== CHARSET INFORMATION ====
PROMPT

set lines 100
set pages 10
col charset for a25
col instance_name for a20
SELECT (select instance_name from v$instance) instance_name, value$ charset 
FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

PROMPT
PROMPT ==== INVALID OBJECTS ====
PROMPT

set lines 100
set pages 2000
column owner format a24;
column object_name format a36;
column object_type format a24;
select owner, object_type, object_name
from   dba_objects
where  status = 'INVALID';

spool off

set markup html off

cl scr

exit;