Abaixo alguns SQLs que são úteis no dia-a-dia do DBA.

Para executar kill user:

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;

Ou por usuário específico:

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

obs.: Alterar o NOME_USUARIO e/ou NOME_MAQUINA para o que deseja, detalhe que tem que ser maiúsculos por é assim que está no dicionário de dados do Oracle.

Ver as sessões abertas:

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;

Para ver os Jobs:

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;

Para ver a ocupação dos Tablespaces:

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 (%)";

Ver os Objetos Inválidos:

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;

obs.: Aqui ainda pode especificar os Object_Type (PACKAGE, PACKAGE BODY, SEQUENCE, SYNONYM, TRIGGER etc).

Listar usuários e suas permissões:

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;

Listas as auditorias ativas:

Select User_Name, Audit_Option, Success, Failure
From Dba_Stmt_Audit_Opts;

Espero postar outros em breve.

Att,
capin