ORACLE - Swiss penknife QUERY

Swiss penknife QUERY

SHOW AND KILL USER CONNECTED 

SQL> alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='TESTE';

SHOW SIZE BY DATAFILE

SQL> select file_id, block_id, blocks*8192/1024 MB,

owner || '.' || segment_name "Name", block_id*8192/1024 "Position MB"

from  sys.dba_Extents

where file_id = &&fileid

union

select file_id, block_id, blocks*8192/1024, 'Free' "Name", block_id*8192/1024 "P

osition MB"

from sys.dba_free_space

where file_id = &fileid

order by 1,2,3

/

SHOW INFO BY OBJECTS

SQL> select owner, object_name, object_type, status from all_objects where object_name='TSILGT';

OWNER       OBJECT_NAME       OBJECT_TYPE   STATUS

------------------------------ ------------------------------ ------------------- -------

TREINA       STP_GRAVATABLOG       PROCEDURE   VALID

SANKHYA        STP_GRAVATABLOG       PROCEDURE   VALID

WMS       STP_GRAVATABLOG       PROCEDURE   VALID

TESTE       STP_GRAVATABLOG       PROCEDURE   INVALID

SQL> alter procedure teste.STP_GRAVATABLOG compile;

Warning: Procedure altered with compilation errors.

SQL> show error

Errors for PROCEDURE TESTE.STP_GRAVATABLOG:

LINE/COL ERROR

-------- -----------------------------------------------------------------

33/5 PL/SQL: SQL Statement ignored

46/9 PL/SQL: ORA-00942: table or view does not exist

SQL> SELECT text FROM all_source WHERE name = 'STP_GRAVATABLOG'  and owner='TESTE' ORDER BY line;

SHOW SIZE BY TABLE OR SEGMENTS

SQL>select sum(bytes/1024/1024) as Tamanho_MB from dba_segments

where owner = 'SVP'

and segment_type = 'TABLE'

and segment_name in ('TABLE_1','TABLE_2','TABLE_N')

SQL>select onwer, segment_name, segment_type, tablespace_name, bytes/1024/1024 

from dba_segments where segment_name='TABLE_NAME';

SHOW DATAFILES INFO GENERAL/STATUS 

SQL> select tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY/1024/1024 nextmb from dba_data_files;

TABLESPACE FILE_NAME MBYTES AUT   MAXBYTES   NEXTMB

---------- ---------------------------------------- ---------- --- ---------- ----------

USERS   /u01/app/oracle/oradata/XE/users.dbf    100 YES 11264 .001220703

SYSAUX   /u01/app/oracle/oradata/XE/sysaux.dbf   710 YES 32767.9844 .001220703

UNDOTBS1   /u01/app/oracle/oradata/XE/undotbs1.dbf     75 YES 32767.9844 .000610352

SYSTEM   /u01/app/oracle/oradata/XE/system.dbf   360 YES   600 .001220703

SHOW ALL INX / REBUILD

SQL>select 'alter index ' || owner || '.' || index_name ||' rebuild;'
from dba_indexes
where status ='UNUSABLE'
order by index_name
/

in construction ...