Oracle 常用语句

SELECT t.owner,
 t.table_name AS tableName,
 f.comments AS comments
 FROM all_tables t
 INNER JOIN user_tab_comments f
 ON t.table_name = f.table_name;

SELECT a.table_name,
 b.comments,
 c.column_name,
 c.comments
 FROM user_tables a
 LEFT JOIN user_tab_comments b
 ON a.table_name = b.table_name
 LEFT JOIN user_col_comments c
 ON a.table_name = c.table_name

SELECT t.owner,
 t.table_name AS tablename,
 f.comments AS comments
 FROM all_tables t
 INNER JOIN all_tab_comments f
 ON t.table_name = f.table_name;

SELECT a.owner,
 a.table_name,
 b.comments,
 c.column_name,
 c.comments
 FROM all_tables a
 LEFT JOIN all_tab_comments b
 ON a.table_name = b.table_name
 LEFT JOIN all_col_comments c
 ON a.table_name = c.table_name

select * from DBA_JOBS;
select * from dba_jobs_running;
select * from user_jobs;

select a.username, a.STATUS, a.OSUSER, a.sid, a.SERIAL#, a.program, b.spid, c.sql_text, c.SQL_ID
 from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
 and a.sql_hash_value = c.hash_value
 and a.username is not null;
-- kill 会话
alter system kill session ‘sid,serial#’;

select l.session_id as sid,
 s.serial#,
 l.locked_mode as 锁模式,
 l.oracle_username as 登录用户,
 l.os_user_name as 登录机器用户名,
 s.machine as 机器名,
 s.terminal as 终端用户名,
 o.object_name as 被锁对象名,
 s.logon_time as 登录数据库时间
 from v$locked_object l, all_objects o, v$session s
 where l.object_id = o.object_id
 and l.session_id = s.sid
 order by sid, s.serial#;

SELECT owner,
 segment_name,
 segment_type,
 ceil(max_size / (1024 * 1024 * 1024)) AS "上限(G)",
 bytes / (1024 * 1024 * 1024) AS "空间大小(G)"
 FROM dba_segments a