[Oracle] Scripts/SQL Úteis I – Oracle

Select 'alter system kill session ''' || b.Sid || ',' || b.Serial# || ''' immediate;'
From V$session b, V$process a
Where b.Paddr = a.Addr
And Type = 'USER'
And b.Username Like '%NOME_USUARIO%'
And b.Machine Like '%NOME_MAQUINA%'
Order By b.Logon_Time, a.Pga_Used_Mem;

Select 'alter system kill session ''' || b.Sid || ',' || b.Serial# || ''' immediate;'
From V$session b
Where Type = 'USER'
And b.Username = 'NOME_USUARIO';

Select Substr(b.Sid, 1, 5) Sid, Substr(b.Serial#, 1, 5) Ser#, Substr(b.Machine, 1, 6) Box,
Substr(b.Username, 1, 8 ) "USER", b.Last_Call_Et Seg_Inactive, Substr(b.Osuser, 1, 8 ) Os_User,
Substr(b.Program, 1, 27) Program, b.Logon_Time
From V$session b, V$process a
Where b.Paddr = a.Addr
And Type = 'USER'
Order By b.Logon_Time, Seg_Inactive;

Select Substr(Job, 1, 3) Job, Substr(Last_Date || ' ' || Last_Sec, 1, 19) Last_Exec,
Substr(Next_Date || ' ' || Next_Sec, 1, 19) Next_Exec, Broken, Failures, Substr(What, 1, 30) Comando,
Substr(Interval, 1, 25) Interval
From Dba_Jobs
Order By Last_Exec;

Select "Tablespace Name", Sum("Size") "Size (M)", Sum("Used") "Used (M)", Sum("Free") "Free (M)",
Sum("Used") * 100 / Sum("Size") "Percentual Utilizado (%)"
From (Select Substr(Df.Tablespace_Name, 1, 35) "Tablespace Name", Round(Df.Bytes / 1024 / 1024, 2) "Size",
Round(e.Used_Bytes / 1024 / 1024, 2) "Used", Round(f.Free_Bytes / 1024 / 1024, 2) "Free"
From Dba_Data_Files Df,
(Select File_Id, Sum(Decode(Bytes, Null, 0, Bytes)) Used_Bytes
From Dba_Extents
Group By File_Id) e,
(Select Max(Bytes) Free_Bytes, File_Id
From Dba_Free_Space
Group By File_Id) f
Where e.File_Id(+) = Df.File_Id
And Df.File_Id = f.File_Id(+)
Order By Df.Tablespace_Name)
Group By "Tablespace Name"
Order By "Percentual Utilizado (%)";

Select Substr(Owner, 1, 15) As Owner, Substr(Object_Type, 1, 35) As Object_Type,
Substr(Object_Name, 1, 30) As Object_Name, Status
From Dba_Objects
Where Status = 'INVALID'
Order By Owner, Object_Type, Object_Name;

Select Username, Account_Status, Default_Tablespace, Temporary_Tablespace, Profile, Granted_Role, Admin_Option,
Default_Role
From Sys.Dba_Users a, Sys.Dba_Role_Privs b
Where a.Username = b.Grantee
Order By Username, Account_Status, Default_Tablespace, Temporary_Tablespace, Profile, Granted_Role;

Select User_Name, Audit_Option, Success, Failure
From Dba_Stmt_Audit_Opts;