1、查看表空间的名称及大小
SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024)), 0) TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
2、查看表空间物理文件的名称及大小
SELECT TABLESPACE_NAME,
FILE_ID,
FILE_NAME,
ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
3、查看回滚段名称及大小
SELECT SEGMENT_NAME,
TABLESPACE_NAME,
R.STATUS,
(INITIAL_EXTENT / 1024) INITIALEXTENT,
(NEXT_EXTENT / 1024) NEXTEXTENT,
MAX_EXTENTS,
V.CUREXT CUREXTENT
FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V
WHERE R.SEGMENT_ID = V.USN(+)
ORDER BY SEGMENT_NAME;
4、查看控制文件
SELECT NAME FROM V$CONTROLFILE;
5、查看日志文件
SELECT MEMBER FROM V$LOGFILE;
6、查看表空间的使用情况
SELECT SUM(BYTES) / (1024 * 1024) AS FREE_SPACE, TABLESPACE_NAME
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;
SELECT A.TABLESPACE_NAME,
A.BYTES TOTAL,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES * 100) / A.BYTES "% USED ",
(C.BYTES * 100) / A.BYTES "% FREE "
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
7、查看数据库库对象
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT#
FROM ALL_OBJECTS
GROUP BY OWNER, OBJECT_TYPE, STATUS;
8、查看用户创建的表
SELECT *
FROM all_objects
WHERE OWNER='HNMP' AND OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE '%EOS_DICT_ENTRY%';
SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%EOS_DICT_ENTRY%';
9、查看数据库的版本
SELECT VERSION
FROM PRODUCT_COMPONENT_VERSION
WHERE SUBSTR(PRODUCT, 1, 6) = 'ORACLE';
10、查看数据库的创建日期和归档方式
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
11、 SELECT * INTO XXX FROM XXX 报： ORA-00905 异常：
如果想在PL/SQL中实现该功能，可使用Create table newTable as select * from …：
如： create table NewTable as select * from ATable;
若表已经存在，则可以使用语法：
INSERT INTO EOS_DICT_ENTRY_BAK SELECT * FROM EOS_DICT_ENTRY WHERE DICTTYPEID='SBLB_2'
12、查看 DBLINK
在 PL/SQL -> Database Links
或
查看全部用户的：
SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='DATABASE LINK';
或
SELECT * FROM DBA_DB_LINKS;
查看本用户的：SELECT * FROM USER_DB_LINKS;
13、创建 DBLINK
create database link link_test connect to user1 identified by user1pwd
using ' (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.9.1)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='DATABASE LINK';
-- 删除 dblink
删除权限为public的dblink： DROP PUBLIC DATABASE LINK DBLINK名称;
删除权限为私有的dblink： drop database link dblink名称;
14、UPDATE JOIN
MERGE INTO <update_table> b -- 指定将 update或者Insert目的表
USING <relation_table> a --指定你要update或者Insert的记录的来源，可以是一个表，视图，子查询
ON (a.CODE=b.CODE ) -- 目的表和源表（视图，子查询）的关联
WHEN matched THEN -- 当关联上时
update set b.DESCRIPTION=a.DESCRIPTION
[delete where (a.CODE='2')] --update子句后面可以跟delete子句来去掉目的表一些不需要的行
WHEN NOT matched THEN -- 当关联不上时
insert (NAME, CODE, DESCRIPTION) values (a.NAME, a.CODE, a.DESCRIPTION) ----insert 后面不加into
-- https://blog.csdn.net/naomi_qing/article/details/70785702
-- 获取表结构信息
SELECT T1.TABLE_NAME AS "表名",
--T1.TABLE_NAME || CHR(13) || T3.COMMENTS AS "表名称及说明",
--T3.COMMENTS AS "表说明",
T2.COMMENTS AS "名称",
T1.COLUMN_NAME AS "代码",
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')' AS "数据类型",
(
SELECT CASE WHEN T5.CONSTRAINT_TYPE IS NOT NULL THEN '是' ELSE '否' END
FROM ALL_CONSTRAINTS T5 JOIN ALL_CONS_COLUMNS T6
ON T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME
WHERE T1.TABLE_NAME=T5.TABLE_NAME AND T5.CONSTRAINT_TYPE='P'
AND T6.COLUMN_NAME =T1.COLUMN_NAME
)
AS "主键",
--T1.NULLABLE AS "是否为空",
T2.COMMENTS AS "注释"
--T1.DATA_DEFAULT "默认值"
--T4.CREATED AS "建表时间"
FROM COLS T1
LEFT JOIN USER_COL_COMMENTS T2
ON T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
LEFT JOIN USER_TAB_COMMENTS T3
ON T1.TABLE_NAME = T3.TABLE_NAME
LEFT JOIN USER_OBJECTS T4
ON T1.TABLE_NAME = T4.OBJECT_NAME
WHERE NOT EXISTS (SELECT T4.OBJECT_NAME
FROM USER_OBJECTS T4
WHERE T4.OBJECT_TYPE = 'TABLE'
AND T4.TEMPORARY = 'Y'
AND T4.OBJECT_NAME = T1.TABLE_NAME)
AND T1.TABLE_NAME IN(
'SB_DHPCXX',
'SB_JLZC'
) AND T2.COMMENTS IS NOT NULL
ORDER BY T1.TABLE_NAME, T1.COLUMN_ID;
15、查看是否锁表
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;
--杀进程
ALTER SYSTEM KILL SESSION '271,11189';
16、查询对象依赖/引用
SELECT *
FROM USER_DEPENDENCIES
WHERE REFERENCED_NAME = 'MY_STATUS_CHG_LOG';
-- 16、用户管理
创建、授权、修改密码：https://blog.csdn.net/ly7472712/article/details/115859946
解锁：http://www.mobiletrain.org/about/BBS/135132.html
17、定时任务 user_jobs
查询用户创建的定时任务
select * from user_jobs -- where what like '%BBTJ_PSCKXXTJ%';
创建定时任务
DECLARE
X NUMBER;
begin
sys.dbms_job.submit(job =>X,
what => 'declare s1 varchar2(200); s2 varchar2(200); begin KSH_KB_CKHWXX_JGB(s1,s2); end;',
next_date => to_date('05-11-2010', 'dd-mm-yyyy'),
interval => 'SYSDATE + 10/（60*24）');
commit;
end;
18、查看所有用户表
SELECT * FROM ALL_TABLES A WHERE A.OWNER = UPPER('数据库用户名');
SELECT * FROM ALL_TABLES A WHERE A.TABLE_NAME LIKE '%SCCJ%' ;
SELECT *
FROM all_objects
WHERE OWNER='HNMP' AND OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE '%EOS_DICT_ENTRY%';
19、当前时间字符串
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISSSSS') FROM DUAL;
20、查询实例名
SELECT INSTANCE_NAME FROM V$INSTANCE;
21、Oracle输入密码错误多次导致账号被锁处理方法
(1)从 xshell 登录，然后切换到 oracle 用户下
su - oracle
(2)登录 sqlplus并连接 oracle
sqlplus /nolog
connect /as sysdba
(3)查询被锁账号并解锁
SQL> select username,account_status from dba_users where username='被锁账号'; 查询被锁定的账号，若已被锁，account_status 为 LOCKED(TIMED)
SQL> alter user 被锁账号 account unlock;
22、创建索引
CREATE INDEX INDEX_SB_JLZC_SBZCBH ON SB_JLZC(SBZCBH) ONLINE; -- 后面加个ONLINE就不会锁表
23、序列
--创建序列
CREATE SEQUENCE SEQ1
START WITH 1 -- 指定序列初始值为1
INCREMENT BY 1 --指定序列值每次递增1
NOMAXVALUE --指定序列值没有上限（实际最大值为10^28-1，），默认设置。你也可以用maxvalue N来指定序列的最大值
NOCYCLE --指定序列到达最大值时不循环，默认设置。如果指定cycle，那么序列到达最大值后，会从头开始循环。
CACHE 20; --为了提高访问速度，预先分配好并缓存在内存中的序列值个数，默认设置
--查看已创建的序列
SELECT * FROM ALL_OBJECTS OJ WHERE OJ.OBJECT_TYPE='SEQUENCE';-- AND OJ.OWNER='HNMP';
--查看序列的定义
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME='SEQ_OPER_ID';
