SQL JOINS
文氏图
#1 ORACLE中查询被锁定的表,以及如何解锁
-- ORACLE表被锁原因：EBS操作某一个FORM界面,
-- 或者后台数据库操作某一个表时发现一直出于"假死"状态,可能是该表被某一用户锁定,导致其他用户无法继续操作;
-- 查询被锁的表
SELECT B.OWNER,B.OBJECT_NAME,A.SESSION_ID,A.LOCKED_MODE
FROM V$LOCKED_OBJECT A,DBA_OBJECTS B
WHERE B.OBJECT_ID = A.OBJECT_ID;
-- 查看是哪个SESSION引起的
SELECT B.USERNAME,B.SID,B.SERIAL#,LOGON_TIME
FROM V$LOCKED_OBJECT A,V$SESSION B
WHERE A.SESSION_ID = B.SID ORDER BY B.LOGON_TIME;
--杀 掉对应进程即解锁
ALTER SYSTEM KILL SESSION'866,20840' -- 其中866是SID 20840是SERIAL#
#2 ORA-01654：索引无法通过表空间扩展
-- ORA-01654：索引无法通过表空间扩展
-- 查看所有表空间的数据使用情况
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 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 AND UPPER(F.TABLESPACE_NAME) = 'REFORMEREMS_DATA'
ORDER BY 1
-- 有可能是索引表空间不够
SELECT SUM(BYTES/1024/1024) SIZEMB FROM DBA_FREE_SPACE Z
-- 该索引的 NEXT_EXTENT 过大
SELECT S.INDEX_NAME,
S.TABLE_NAME,
S.TABLESPACE_NAME,
S.INITIAL_EXTENT,
S.NEXT_EXTENT
FROM USER_INDEXES S
WHERE S.INDEX_NAME = 'IDX_SUBJECTIVE_TEST_NO'
-- 重建该索引
ALTER INDEX IDX_SUBJECTIVE_TEST_NO REBUILD TABLESPACE REFORMEREMS_DATA STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0)
-- 通过计算 470.6875M的剩余空间,不能满足 NEXT_EXTENT 的1250MB空间
SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'REFORMEREMS_DATA'
-- 通过如下SQL扩展表空间大小,ORA-01144：文件大小(6553600块)超出4194303块的最大数；
-- 认表空间数据文件大小根据DATA BLOCKS的大小有关，默认最大为32GB,无法再进行扩展了,解决办法是增加表空间数据文件。
ALTER DATABASE DATAFILE 'D:\ORACLEDATA\REFORMEREMS_DATA.DBF' RESIZE 51200M
-- 通过如下SQL增加表空间数据文件,以下SQL语句为REFORMEREMS_DATA表空间创建了一个名称为REFORMEREMS_DATA02.DBF的数据文件，
-- 该数据文件默认大小为10GB。
ALTER TABLESPACE USERS ADD DATAFILE 'D:\ORACLEDATA\REFORMEREMS_DATA02.DBF' SIZE 10240M
ALTER TABLESPACE "REFORMEREMS_DATA"
ADD DATAFILE 'E:\ORACLEDATA\REFORMEREMS_DATA02.DBF' SIZE 10240M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
-- 此时，REFORMEREMS_DATA表空间有了两个数据文件。
-- 再次执行表空间使用情况SQL语句，查看表空间使用情况。
#3 tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 220.165.9.54)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ONLINE
RANGE
#4 Oracle Instant Client安装与配置
- 把下载的
instantclient-basic-nt-11.2.0.2.0.zip压缩包解压，放到C:\instantclient_11_2目录下。
在“环境变量”的“系统变量”中增加：
ORACLE_HOME = C:\instantclient_11_2
TNS_ADMIN = C:\instantclient_11_2
NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
修改Path变量，在后面添加 C:\instantclient_11_2
- 新建
tnsnames.ora文件
在C:\instantclient_11_2新建一个tnsnames.ora文件，增加自己的数据库别名配置。
#5 SQL Server 2008 清空删除日志文件(瞬间日志变几M)
-- SQL Server 2008 清空删除日志文件(瞬间日志变几M)
USE [master]
GO
ALTER DATABASE PPQA SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE PPQA SET RECOVERY SIMPLE
GO
USE PPQA
GO
DBCC SHRINKFILE (N'PPQA_log' , 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE PPQA SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE PPQA SET RECOVERY FULL
GO
#6 Oracle 查版本号
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
#7 建立唯一索引、唯一性约束
-- 对参考学生表创建唯一索引,同时忽略考号为空的情况
CREATE UNIQUE INDEX IDX_TEST_NO_UNIQUE ON T_EMS_EXAM_STUDENT(NVL2(NULLIF(EXAM_COURSE_ID,NULL) ,NULLIF(TEST_NO,NULL),NULL),NVL2(NULLIF(TEST_NO,NULL) ,NULLIF(EXAM_COURSE_ID,NULL),NULL))
-- 对学生表创建学籍号唯一约束
ALTER TABLE T_BAS_STUDENT ADD CONSTRAINT UNQ_STUDENT_CODE UNIQUE (STUDENT_CODE)
-- 删除唯一性索引
DROP INDEX IDX_TEST_NO_UNIQUE
-- 删除唯一性约束
ALTER TABLE T_BAS_STUDENT DROP CONSTRAINT UNQ_STUDENT_CODE
#8 Oracle批量删除以T_TMP_开头的表
DECLARE
BEGIN
FOR VCUR IN (SELECT T.TABLE_NAME
FROM USER_TABLES T
WHERE T.TABLE_NAME LIKE 'T_TMP_%') LOOP
EXECUTE IMMEDIATE 'DROP TABLE '||VCUR.TABLE_NAME;
END LOOP;
END;
#8 Sqlserver 密码过期时间查询
-- Sqlserver 密码过期时间查询
DECLARE @login NVARCHAR(30)
-- 查询设定密码过期的登陆账号
SELECT @login = name
FROM sys.sql_logins
WHERE is_expiration_checked = 1
AND name = 'Tdf'
-- 计算登录账户密码已使用天数
SELECT @login AS 'login' ,
DATEDIFF(d,
CAST(LOGINPROPERTY(@login, 'PasswordLastSetTime') AS datetime),
GETDATE()) AS 'pwd_using_days'
-- 计算密码到期之前的剩余天数
-- 注意,只适合于sql server 2008
SELECT LOGINPROPERTY(@login, 'DaysUntilExpiration') AS 'days_until_expiration'
