【数据库】Oracle数据库相关

1. 常用SQL语句

删除多余重复数据

DELETE TABLE1 T WHERE T.ID IN
(
    SELECT ID FROM TABLE1 
    GROUP BY ID HAVING COUNT(ID) >1
) 
AND ROWID NOT IN 
(
    SELECT MIN(ROWID) FROM TABLE1 
    GROUP BY ID HAVING COUNT(*) > 1
)

开启/关闭日志

-- 关闭日志
ALTER TABLESPACE tbs NOLOGGIN;
ALTER TABLE TABLE1 NOLOGGING;
-- 开启日志
ALTER TABLESPACE tbs LOGGIN;
ALTER TABLE TABLE1 LOGGING;

查看表空间使用情况

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 			"空闲空间M",
	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 
    	TABLESPACE_NAME,
    	ROUND(SUM(BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
    FROM sys.DBA_DATA_FILES 
) d,
WHERE d.TABLESPACE_NAME=f.TABLESPACE_NAME
ORADER BY 1

表解锁语句

SELECT 
	b.OWNER,
	b.OBJECT_NAME,
	'ALTER SYSTEM KILL SESSION ' || '''' || a.SESSION_ID || ',' || c.SERIAL# || '''' || ';', a.locked_mode 
FROM V$LOCKED_OBJECT a, DBA_OBJECTS b, V$SESSION c
WHERE b.OBJECT_ID=a.OBJECT_ID AND c.SID=a.SESSION_ID

查看已连接的系统主机

-- 查看已连接的系统主机
select 
	b.SID ORACLEID, 
	b.USERNAME ORACLE用户,
	b.SERIAL#,
	SPID 系统ID,
	PADDR,
	SQL_TEXT SQL,
	b.MACHINE 计算机名
FROM V$PROCESS a, V$SESSION b, V$SQLAREA c
WHERE a.ADDR=b.PADDR AND b.SQL_HASH_VALUE=C.HASH_VALUE
-- 查看已连接个数
SELECT MACHINE,COUNT(*) AS COUNT
FROM V$SESSION WHERE STATUS='ACTIVE'
GROUP BY MACHINE;

查看正在执行的SQL语句

SELECT 
	a.SERIAL#,
	a.SID,
	OSUSER 登录身份,
	program 程序,
	USERNAME 登录用户名,
	SCHEMANAME,
	b.CPU_TIME 花费CPU时间,
	STATUS,
	b.SQL_TEXT 执行的SQL
FROM V$PROCESS a LEFT JOIN V$SQL b 
ON a.SQL_ADDRESS=b.ADDRESS AND a.SQL_HASH_VALUE=b.HASH_VALUE
ORDER BY b.CPU_TIME DESC;

数据库创建表空间和用户

-- 1. 创建表空间，注意对dbf文件的路径进行修改
select t.* from dba_data_files t;
-- DROP TABLESPACE DTYJ INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE DTYJ DATAFILE
'/u01/app/oracle/tdr/DTYJ.dbf' SIZE 1024M AUTOEXTEND ON NEXT 50M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
-- DROP TABLESPACE TBS_DTYJ_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TBS_DTYJ_TEMP TEMPFILE
'/u01/app/oracle/tdr/TBS_DTYJ_TEMP.dbf' SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 2. 创建用户
DROP USER dtyj;
CREATE USER dtyj
IDENTIFIED BY dtyj
DEFAULT TABLESPACE DTYJ
TEMPORARY TABLESPACE TBS_DTYJ_TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

-- 3. 权限相关
GRANT CONNECT TO dtyj;
GRANT DBA TO dtyj WITH ADMIN OPTION;
ALTER USER dtyj DEFAULT ROLE ALL;
GRANT CREATE DATABASE LINK TO dtyj;
GRANT CREATE ANY JOB TO dtyj;
GRANT CREATE TABLE TO dtyj;
GRANT SELECT ANY TABLE TO dtyj;
GRANT SELECT ANY DICTIONARY TO dtyj;
GRANT UNLIMITED TABLESPACE TO dtyj WITH ADMIN OPTION;

-- 4. oracle用户下，导入dmp文件
imp dtyj/dtyj file=/home/oracle/temp/dtyj_20201123.dmp ignore=y full=y

2. 常见问题排查

数据库trc/trm日志文件删除

trc和trm日志文件太多时，会占用磁盘空间。需要定时清理，使用如下步骤可以清理。

一、找到trc/trm日志文件目录

cd /u01/app/oracle/diagnostic/rdbms/tdr/tdr/trace

二、删除该目录下日志文件

rm -rf *.trc *. trm

三、重启数据库（可选步骤）

su - oracle		# 切换oracle用户
sqlplus /nolog	# 使用sqlplus登录
sql> conn / as sysdba
sql> shutdown abort
sql> startup
sql> exit

当需要定时清理时可使用如下脚本，并将该脚本放至定时器任务中。

#!/bin/sh
. /etc/profile.d/oraenv.sh
ORACLE_DIAG=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID
for subpath in trace incident
do
if [ -d $ORACLE_DIAG/$subpath]
then
find $ORACLE_DIAG/$subpath -mtime +30 -exec rm -rf {} \;
fi
done

ORACLE_DIAG=$GRID_BASE/crs_base/diag/asm/+asm/+ASM
for subpath in trace incident
do
if [ -d $ORACLE_DIAG/$subpath]
then
find $ORACLE_DIAG/$subpath -mtime +30 -exec rm -rf {} \;
fi
done