Oracle

查看当前用户的缺省表空间

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导入表

9.表连接多条记录分别取第一条

10.windows的数据库导入linux报长度错误，可能是因为字符集的问题

11.PL/SQL

1.查看sql失败时可以刷新后再试
2.java字符串拼接SQL可以通过Paste from Host Language转化为正常SQL
3.导入txt

12.父子关系查找

1.补充1
2.补充2_剔除部分分支
3.排序

13.oracle 按自定义的序列排序结果集

14.存储过程

1.plsql中procedure的入参类型，如果是number或varchar2的话不需要定义长度。
2.变量使用前要初始化，推荐声明时指定默认值

1.select into形式赋值变量的不需要初始化

15.where条件中使用正则表达式

16.手动刷新物化视图

1.在PL/SQL的Command Window中执行EXEC dbms_mview.REFRESH('v_user_login_count');
2.查看物化视图上次刷新时间select name, last_refresh from user_mview_refresh_times;

17、PL/SQL执行sql脚本

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

20.小数和字符串拼接0不见的问题解决

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

1.参考1
2.参考2

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

5.使用变量注意变量后的.要显示需要..

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.开启闪回

30.exp和imp字符集不一致问题

31.ORA-00257解决办法

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