oracle数据库使用示例

oracle数据库使用示例

1. 查数据库版本
SELECT * FROM V$VERSION;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

2. 查系统函数
SELECT owner,object_name FROM all_OBJECTS WHERE OBJECT_type='FUNCTION';
OWNER	OBJECT_NAME
SYS	GETLONG
SYS	GETTVOID

3. 查函数定义
SELECT dbms_lob.substr(dbms_metadata.get_ddl('FUNCTION','CONCATSTR','NINTH')) FROM DUAL;
CREATE OR REPLACE EDITIONABLE FUNCTION "NINTH"."CONCATSTR" (input ConcatObj) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING ConcatStrImpl;

4. 包头、包体、存储过程、触发器、函数、type查询等
SELECT * FROM user_source;
NAME	TYPE	LINE	TEXT	ORIGIN_CON_ID
YJ_AUTORENEWAL_SCREENING	PACKAGE	1	"package yj_autoRenewal_screening is"

SELECT distinct type FROM user_source;
TYPE
TYPE BODY
PROCEDURE
PACKAGE
PACKAGE BODY
TRIGGER
FUNCTION
TYPE

5. 查表结构,视图定义
select dbms_metadata.get_ddl('TABLE','TEST01','NINTH') as ddl_tab from dual
select dbms_metadata.get_ddl('VIEW','DEPTREE','NINTH') as ddl_tab from dual

6. 查是否存在表
SELECT * FROM dba_tables WHERE table_name LIKE '%TEST01%';

7. 查数据字典
select
atc.owner,
atc.table_name
,atc.column_name
,atc.data_type
,atc.data_length
,atc.nullable
,acc.comments
,atc.column_id
from all_tab_columns atc
join all_col_comments acc
on atc.table_name=acc.table_name
and atc.owner=acc.owner
and atc.column_name=acc.column_name
where atc.table_name='TEST01'
--and atc.owner='TPREPORT'
order by atc.owner,atc.table_name, atc.column_id

8. 查表的主键
SELECT OWNER,table_name,LISTAGG(column_name,',') WITHIN group(ORDER BY column_name) AS pkcols
FROM
(
select a.OWNER,a.table_name,a.column_name
from all_cons_columns a
join all_constraints b
on a.constraint_name = b.constraint_name
and a.OWNER=b.OWNER
and b.constraint_type = 'P'
AND a.table_name='TEST01'
) t1
GROUP BY OWNER,table_name;
OWNER	TABLE_NAME	PKCOLS
NINTH	TEST01	POLICYNO

9. 查表占用大小
SELECT segment_name AS table_name,sum(bytes/1024/1024) AS size_mb
FROM user_segments
WHERE segment_name='TEST01'
GROUP BY segment_name
;
TEST01	195006.0625