Oracle篇日常使用笔记

Oracle篇 日常使用笔记

1、带条件匹配更新，无主键

update pay_request a 
   set PAYEE_ACCOUNT_NO =
       (select 收款人账户
          from MY_COLLECTION b
         where (pay_amount || remark) = (to_number(导入金额) || 支付摘要))#通过合并字段来形成唯一字段达到主键的效果
 where is_valid = '1'
   and is_salary_id = '2';

 2、PLSQL Developer 登录连接无响应 打开tnsnames.ora文件
 cmd 然后tnsping 实例名来测试是否成功

 XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = **localhost**)(PORT = 1521))#本地连接不上，把HOST出改为localhostb，不使用主机名。
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
CZDZH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =IP地址)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 实例名)
    )
  )#远程连接不上新建一个远程配置，和本地的差不多，只需要修改HOST和SERVICE_NAME



Oracle 恢复数据文件（IMP方式）：
imp 用户名称/1(密码)@orcl（数据库实例名称）  file=dmp(需要恢复的文件绝对路径) log=log（日志需要存放的绝对路径）  fromuser=导出的Oracle用户名称 touser=导入的Oracle用户名称
--如果没有日志不知道导出的用户是谁，可以使用 full=y



Oracle 恢复数据文件（IMPDP方式）：

create directory GGYTH as '文件目录绝对路径'; --创建需要恢复的文件目录的逻辑目录

grant read,write on directory GGYTH to "用户名称" --将权该目录权限授予用户

create user "用户名称" identified by 1
default tablespace MOF  
temporary tablespace  TEMP; 
grant connect,resource,dba to "用户名称" --创建用户

IMPDP 用户名称/1(密码)@orcl(数据库实例名称) directory=GGYTH （逻辑目录名称）DUMPFILE=DMP（恢复的文件名称）  LOGFILE=.LOG（日志文件名称）  table_exists_action=replace REMAP_SCHEMA=（导出用户名称）:（导入用户名称）
--table_exists_action(参数详解):1)skip：默认操作;2)replace：先drop表，然后创建表，最后插入数据;3)append：在原来数据的基础上增加数据;4)truncate：先truncate，然后再插入数据
--impdp与imp不同的是不使用绝对路径，把相应文件的绝对路径创建成一个逻辑目录并授权给用户
--如何区分需要恢复的文件使用imp或者impdp，可以查看日志，有导出表的就是imp，有处理对象的是impdp



表空间不够用了怎么办：
SELECT a.tablespace_name "MOF", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name
--查询各个表空间已使用与未使用的
（1）给表空间增加数据文件

ALTER TABLESPACE app_data ADD DATAFILE  
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;  

（2）新增数据文件，并且允许数据文件自动增长

ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

（3）允许已存在的数据文件自动增长

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'  
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;  

（4）手工改变已存在数据文件的大小

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'  
RESIZE 100M; 
--------------------- 

--创建表空间
/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace emaoyi_temp 
tempfile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\emaoyi_temp.dbf' 
size 10m 
autoextend on 
next 10m maxsize 20480m 
extent management local; 

/*第2步：创建数据表空间 */
create tablespace emaoyi 
logging 
datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\emaoyi.dbf' 
size 10m 
autoextend on 
next 10m maxsize 20480m 
extent management local;

2020/08/05
wm_concat函数没有，脚本执行不了
sys 登录

1.解锁sys用户

alter user sys account unlock;

2.创建包，包体，函数

以sys用户登录数据库，执行下面的命令

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
(
CURR_STR VARCHAR2(32767), 
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);

定义类型body：

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;

自定义函数：自定义行变列函数:

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
 
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;

3.创建同义词并授权

create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL
create public synonym wm_concat for sys.wm_concat
grant execute on WM_CONCAT_IMPL to public
grant execute on wm_concat to public

imp和impdp导出

exp 数据库用户名/密码@实例名称 file=dmp文件保存路径log=日志保存路径  owner=(导出多个相同库，用户名用分号分开，例如用户1，用户2，用户2)

expdp一样要给用户授权逻辑目录的权限

expdp LXDZH2020/1@CZDZH DIRECTORY=LXZF DUMPFILE=LXDZH2020.dmp LOGFILE=LXDZH2020.log FULL=y 

select row_number() over(partition by code order by code desc ) rn,t.* from gap_ele_bank t
sum(pr.pay_amount) over(partition by pcv.clear_bank_code ) 总额度

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。
rank()与dense_rank()的区别
rank()和dense_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense_rank()是非跳跃排序，有两个第一名时接下来是第二名。

原文链接：https://blog.csdn.net/qq_39304896/article/details/106819788