Oracle

一、查看存储使用情况

1、查询剩余表空间大小

SELECT 
     tablespace_name "表空间名称",
	trunc(total / (1024 * 1024 * 1024),2) "表空间大小(G)",
	trunc(free / (1024 * 1024 * 1024),2) "表空间剩余大小(G)",
	trunc((total - free) / (1024 * 1024 * 1024),2) "表空间使用大小(G)",
	round((total - free) / total, 4) * 100 "使用率 %"
FROM 
	(SELECT 
		tablespace_name, 
		SUM(bytes) free
	FROM 
		dba_free_space
	GROUP BY 
		tablespace_name) a,
	(SELECT 
		tablespace_name, 
		SUM(bytes) total
	FROM 
		dba_data_files
	GROUP BY 
		tablespace_name) b
WHERE 
	a.tablespace_name = b.tablespace_name 
and 
	a.tablespace_name in  ('DL_SJWJ','DL_SJCJ','DL_SJZL');

2、查询LOB类型的大字段占用在哪个字段

SELECT 	
	b.table_name,
	b.column_name,
	s.SEGMENT_NAME,bytes/1024/1024/1024 AS GB 
FROM 
	user_segments s
JOIN 
	dba_lobs b 
ON 
	s.SEGMENT_NAME=b.SEGMENT_NAME
ORDER BY  
	BYTES DESC;

二、操作表空间

1、修改表所在的表空间

ALTER TABLE 表空间名称.表名 MOVE TABLESPACE 表空间名称;

2、给表空间下新建物理文件

ALTER TABLESPACE MAINDATA_IN ADD DATAFILE  
'/home/oracle/APP03.DBF' SIZE 32767M; 
--要现在服务器上创建/home/oracle/该目录

3、查看表空间下有哪些数据文件

SELECT FILE_NAME, BYTES/1024/1024 as MB FROM dba_data_files WHERE TABLESPACE_NAME='SYSTEM';

4、创建表空间、用户

--创建表空间,指定数据文件以及大小，必须要在服务器先创建数据文件所在目录
CREATE TABLESPACE df
DATAFILE 'D:/oracle/data/orcl/df.dbf' 
SIZE 32767M;
--创建对象
CREATE USER user_name
IDENTIFIED BY "password"
DEFAULT TABLESPACE tablespace_name
temporary tablespace temp;
--授予用户连接数据库的权限
GRANT connect, resource, dba TO user_name;

三、回收站相关操作

-- 查看回收站是否开启
SELECT
	value
FROM 
	v$parameter 
WHERE
	name='recyclebin';
--清空回收站
PURGE RECYCLEBIN;
	

四、查询实例名

SELECT instance_name FROM v$instance;
select * from SYS.USER_ERRORS where NAME = upper('[存储过程名]');

五、查询主键

select a.constraint_name, a.column_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P' and a.table_name = 'table_name'

六、查看触发器

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER'and OBJECT_NAME = 'EDG23_EDAPI_CALL_LOG' 
select * from user_triggers where table_name = 'EDG23_EDAPI_CALL_LOG'