ORACLE使用中的查询系统表相关记录

-- 加ORACLE并发 /*+parallel(t,10) (b,10)*/

-- NOLOGGING 无日志 CREATE TABLE table_name NOLOGGING

 

-- ORACLE进程查询

select sess.sid,

sess.serial#,

lo.oracle_username,

lo.os_user_name,

ao.object_name,

lo.locked_mode

from v$locked_object lo,

dba_objects ao,

v$session sess

where ao.object_id = lo.object_id and lo.session_id = sess.sid;

ORACLE删除进程

alter system kill session '7479,45865'

 

表权限更改 grant select,insert,update,delete,all on 表名 to 用户名

查询某个字段长度变更，并且生成变更语句。

select 'alter table ' || a.OWNER ||'.'||a.TABLE_NAME ||' modify USR_CITY_NM varchar2(32) ; ' from all_tab_columns a where a.COLUMN_NAME = 'USR_CITY_NM'

 

 

-- ORACLE查询表空间相关问题

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,

2),

'990.99') "使用比",

F.TOTAL_BYTES/1024 "空闲空间(g)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 DESC;

 

-- 查询ORACLE 所以表，字段的基本信息

SELECT A.OWNER 所属用户,

A.TABLE_NAME 表名,

E.TABLE_COMMENTS 表中文名,

A.COLUMN_NAME 字段名,

E.COLUMN_COMMENTS 字段中文名,

A.DATA_TYPE 字段类型,

A.字段长度,

A.字段精度,

A.是否为空,

A.创建日期,

A.最后修改日期,

CASE

WHEN A.OWNER = D.OWNER AND A.TABLE_NAME = D.TABLE_NAME AND

A.COLUMN_NAME = D.COLUMN_NAME THEN

'主键'

ELSE

''

END 是否主键

FROM (SELECT A.OWNER,

A.TABLE_NAME,

B.COLUMN_NAME,

B.DATA_TYPE,

CASE

WHEN B.DATA_PRECISION IS NULL THEN

B.DATA_LENGTH

ELSE

DATA_PRECISION

END 字段长度,

DATA_SCALE 字段精度,

DECODE(NULLABLE, 'Y', '√', 'N', '×') 是否为空,

C.CREATED 创建日期,

C.LAST_DDL_TIME 最后修改日期

FROM ALL_TABLES A, ALL_TAB_COLUMNS B, ALL_OBJECTS C

WHERE A.TABLE_NAME = B.TABLE_NAME

AND A.OWNER = B.OWNER

AND A.OWNER = C.OWNER

AND A.TABLE_NAME = C.OBJECT_NAME

-- AND A.OWNER = 'CDBADM' --此处用户名可以更改，但必须大写英文

AND C.OBJECT_TYPE = 'TABLE') A

LEFT JOIN (SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME

FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND B.CONSTRAINT_TYPE = 'P') D

ON A.OWNER = D.OWNER

AND A.TABLE_NAME = D.TABLE_NAME

AND A.COLUMN_NAME = D.COLUMN_NAME

LEFT JOIN (SELECT CL.OWNER,

T.TABLE_NAME,

T.COMMENTS TABLE_COMMENTS,

CL.COLUMN_NAME,

CL.COMMENTS COLUMN_COMMENTS

FROM ALL_COL_COMMENTS CL, ALL_TAB_COMMENTS T

WHERE CL.OWNER = T.OWNER

AND CL.TABLE_NAME = T.TABLE_NAME) E

ON A.OWNER = E.OWNER

AND A.TABLE_NAME = E.TABLE_NAME

AND A.COLUMN_NAME = E.COLUMN_NAME

ORDER BY A.OWNER, A.TABLE_NAME;