- 查看当前用户的缺省表空间
select username,default_tablespace from user_users;
- 查看一个表,所在的表空间
select TABLESPACE_NAME from all_tables where table_name='EMP';
- navicat配置oracle连接
- 从高版本向低版本迁移数据
1.建立directory:
CREATE OR REPLACE DIRECTORY DATA_DUMP_DIR AS '/data/db_export/dmp';
2.查询directory创立:select * from dba_directories;
3.expdp userid="'username/password@orcl as sysdba'" schemas=schema_name dumpfile=dmp_name.dmp DIRECTORY=DATA_DUMP_DIR
4./data/db_export/dmp需要允许oracle用户写入
5.参考
- 在高版本主机运行
expdp swcms/swcms@orcl directory=data_pump_dir DUMPFILE=wsl_transfer_data_10g.dmp logfile=log.log schemas=swcms version=10.2.0.1.0
- 在低版本主机运行
impdp swcms/swcms DIRECTORY=DATA_PUMP_DIR DUMPFILE=WSL_TRANSFER_DATA_10G.DMP SCHEMAS=swcms logfile=logimpdp.log exclude=user - PL/SQL显示中文
环境变量NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
6.sqlplus连接oracle数据库
sqlplus username/password@ip:port/sid
7.更改db连接数
8.csv导入表
10.windows的数据库导入linux报长度错误，可能是因为字符集的问题
11.PL/SQL
1.查看sql失败时可以刷新后再试
2.java字符串拼接SQL可以通过Paste from Host Language转化为正常SQL
3.导入txt
12.父子关系查找
1.补充1
2.补充2_剔除部分分支
3.排序
14.存储过程
1.plsql中procedure的入参类型，如果是number或varchar2的话不需要定义长度。
2.变量使用前要初始化，推荐声明时指定默认值1.select into形式赋值变量的不需要初始化
16.手动刷新物化视图
1.在PL/SQL的Command Window中执行
EXEC dbms_mview.REFRESH('v_user_login_count');
2.查看物化视图上次刷新时间select name, last_refresh from user_mview_refresh_times;
1.将结果导出csv
set echo off
set heading off
set feedback off
set pagesize 1000
set feedback off
set term off
spool c:\test.csv
SELECT 1||','||2 FROM DUAL;
spool off
exit
18.解除锁表
19.rpad汉字长度问题
1.
SELECT RPAD('我1', , 6, '2'),RPAD(to_nchar('我1'), 6, '2'),LENGTHB(CONVERT('我1', 'ZHS16GBK', 'UTF8')) FROM DUAL
2.参考1
3.参考2
21.获取连续日期
SELECT TO_CHAR(TO_DATE('2018-01-01', 'yyyy-mm-dd') + (LEVEL - 1), 'yyyy-mm-dd') days, LEVEL FROM DUAL CONNECT BY TRUNC(TO_DATE('2017-01-01', 'yyyy-mm-dd')) + LEVEL - 1 <= TRUNC(TO_DATE('2017-01-04', 'yyyy-mm-dd')) order by days desc
22.shell操作oralce
sqlplus -s 用户名/密码@tnsnames.ora文件中配置的dababasename <<EOF
set heading off
set pagesize 0
set trimspool on
set feedback off
set termout off
set trimout on
set define on
COL yestoday NEW_VALUE yestoday_date noprint
SELECT TO_CHAR(SYSDATE-1,'yyyymmdd') yestoday FROM DUAL;
spool ${PATH}/log_&yestoday_date..log
SQL分号结尾
spool off
exit
EOF
4.中文注意设置环境变量
1.NLS_LANG=设置为[select userenv('language') from dual;]的值
2.LANG=zh_CN.UTF-8
23.存储中文
24.删除表会附带删除触发器
25.游标使用概要
26.自增主键
27.docker安装11.2.0.1.0
28.创建新的数据库实例
1.创建命令:
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcl -sid orcl -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL
2.查看db参数的常用sql及命令1.
select name from v$datafile;
2.SELECT * FROM NLS_DATABASE_PARAMETERS;
3.show parameter name;
29.开启闪回
32.加快imp导入速度
1.
imp swcms/swcms@orcl file=201803061230_swcms.dmp LOG=/home/oracle/app/oracle/oradata/201803061230_swcms.log feedback=10000 buffer=100000000 COMMIT=Y
33.exp导出时出现ORA-01555和ORA-22924的解决方案
1.
&&lob_column改为&lob_column
34.全库导入
1.注意sqlplus段在shell中需用\转义$
2.也可使用存储过程禁用启用约束，但需调转一下imp约束导入顺序
#!/bin/bash
. /etc/profile
. ~/.bash_profile
dmpfile=`ls /data/db_back_up/dmp/*_XXX.dmp`
echo "imp "$dmpfile" start:`date`"
su - oracle -c "sqlplus /nolog" <<EOF
connect XXX/XXX@orcl as sysdba
declare
v_sql varchar2(1000);
begin
for cur in (select s.sid, s.serial#
from v\$session s
where upper(username) in (upper('XXX'))
)
loop
v_sql := 'alter system kill session ''' || cur.sid || ',' || cur.serial# || ''' immediate';
execute immediate v_sql ;
end loop;
for cur in (select t.username from dba_users t where upper(t.username) in (upper('XXX')))
loop
v_sql := ' drop user ' || cur.username || ' cascade ' ;
execute immediate v_sql ;
end loop;
end;
/
create user XXX identified by "XXX";
grant CONNECT, resource,IMP_FULL_DATABASE to XXX;
exit;
EOF
#全部导入
#imp XXX/XXX@orcl file="$dmpfile" commit=y ignore=y buffer=10240000 feedback=10000 full=y statistics=none
#导入表结构(不含约束)
imp XXX/XXX@orcl file="$dmpfile" commit=y ignore=y buffer=10240000 feedback=10000 full=y rows=n statistics=none CONSTRAINTS=n
tables=`
su - oracle -c "sqlplus -S /nolog" <<EOF
conn XXX/XXX@orcl
set heading off
set long 4000
set lines 4000
col tbl_names format a4000
SELECT WM_CONCAT(UT.TABLE_NAME) tbl_names FROM USER_TABLES UT WHERE UT.TABLE_NAME <> 'XXX';
exit;
EOF
`
#导入除XXX表的数据及约束
imp XXX/XXX@orcl file="$dmpfile" commit=y ignore=y buffer=10240000 feedback=10000 tables="$tables" statistics=none
echo "imp "$dmpfile" end:`date`"
35.跨库dblink
37、查询表空间使用情况
SELECT DDF.TABLESPACE_NAME "表空间名",
ROUND((1 - NVL(FREE_SPACE, 0) / NVL(SPACE, 1)) * 100, 2) "使用率 %",
FREE_SPACE "表空间剩余大小(M)",
SPACE "表空间大小(M)",
SPACE - NVL(FREE_SPACE, 0) "表空间使用大小(M)"
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DDF,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) DFS
WHERE DDF.TABLESPACE_NAME = DFS.TABLESPACE_NAME(+)
UNION ALL --临时表空间
SELECT DTF.TABLESPACE_NAME "表空间名",
ROUND(NVL(USED_SPACE, 0) / NVL(SPACE, 1) * 100, 2) "使用率 %",
NVL(FREE_SPACE, 0) "表空间剩余大小(M)",
NVL(SPACE, 0) "表空间大小(M)",
NVL(USED_SPACE, 0) "表空间使用大小(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) DTF,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TSH
WHERE DTF.TABLESPACE_NAME = TSH.TABLESPACE_NAME(+)
ORDER BY 4 DESC;
38.Druid加密数据库密码
1.
java -cp D:/druid-1.0.13.jar com.alibaba.druid.filter.config.ConfigTools 密码
39.查询数据库某用户表详细信息
SELECT --ATC.OWNER，
ATC.TABLE_NAME "表名"，
ATCS.COMMENTS "表注释"，
ATC.COLUMN_NAME "列名"，
ACC.COMMENTS "列注释",
ATC.DATA_TYPE "类型"，
ATC.DATA_LENGTH "长度"，
ATC.NULLABLE "是否为空",
ATC.DATA_DEFAULT "默认值"
FROM
(SELECT ATC.OWNER，
ATC.TABLE_NAME，
ATC.COLUMN_NAME，
ATC.DATA_TYPE，
ATC.DATA_LENGTH，
ATC.NULLABLE,
ATC.DATA_DEFAULT
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.OWNER =&USER) ATC
LEFT OUTER JOIN ALL_COL_COMMENTS ACC
ON ATC.TABLE_NAME=ACC.TABLE_NAME
AND ATC.COLUMN_NAME=ACC.COLUMN_NAME
AND ATC.OWNER = ACC.OWNER
LEFT OUTER JOIN ALL_TAB_COMMENTS ATCS
ON ATC.TABLE_NAME=ATCS.TABLE_NAME
AND ATC.OWNER = ATCS.OWNER
ORDER BY ATC.TABLE_NAME，ATC.COLUMN_NAME
40.解决ORA-00031: session marked for kill tips
PIDs=`sqlplus -s username/password@orcl as sysdba <<EOF
set head off
set termout off
set feedback off
set pagesize 0
set trimout on
select p.spid from v\\$process p,v\\$session s where p.addr = s.paddr and s.status = 'KILLED';
exit
EOF`
echo $PIDs | awk -F' ' '{for(i=0;++i<=NF;)print "kill -9 "$i}' | sh
41.使用dba编译指定schema下的所有procedures、views等(注意schema名大写)exec dbms_utility.compile_schema('CCSTYJ');
1.查询当前用户下所有需要再编译对象
select 'Alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';
2.查询所有需要再编译对象select 'Alter '||object_type||' '||object_name||' compile;' from all_objects where status = 'INVALID';
42、REGEXP_LIKE(field, '^[[:digit:]]{11}$')中可以匹配８和8
43、行转列
SELECT * FROM
(
SELECT VULDC.USER_ID,T3.MONTH,VULDC.LOGIN_COUNT FROM V_USER.V_USER_LOGIN_DAILY_COUNT VULDC,
(
SELECT TO_CHAR(ADD_MONTHS(SYSDATE-1,-(LEVEL-1)),'YYYY-MM') MONTH FROM DUAL CONNECT BY LEVEL<=6
)T3a
WHERE SUBSTR(VULDC.COUNT_DATE,1,7) = T3.MONTH
)T4
PIVOT
(COUNT(LOGIN_COUNT) for MONTH in ('2019-11' m1,'2019-10' m2,'2019-09' m3,'2019-08' m4,'2019-07' m5,'2019-06' m6));
44、列转行
SELECT WM_CONCAT(''''||TO_CHAR(ADD_MONTHS(SYSDATE-1,-(LEVEL-1)),'YYYY-MM')||''' '||'M'||LEVEL) MONTH FROM DUAL CONNECT BY LEVEL<=6
45、可以通过awr报告查看oracle中慢sql
46、docker安装oracle 12c
1、远程expdp
create database link link_name
connect to user
identified by "password"
using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = name)
)
)'
create or replace directory DATA_DUMP_DIR as 'path'
expdp userid="'sys/oracle@orcl as sysdba'" network_link=link_name schemas=xx dumpfile=xx.dmp DIRECTORY=DATA_DUMP_DIR logfile=xx.log parallel=4 job_name=xx
47、jar下载maven1、maven2
48、dbeaver PJ，无需安装oracle client
