共计 5420 个字符，预计需要花费 14 分钟才能阅读完成。
Oracle笔记
1、设置空表占用表空间
防止导出表不全
show parameter deferred_segment_creation;
alter system set deferred_segment_creation=false;
2、创建DBlink
实现跨库查询
create public database link zcdbstandard connect to system identified by Admin_123 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
//查看所有DBlink
select owner,object_name from dba_objects where object_type='DATABASE LINK';#查询出dba用户下的dblink
HOST = 192.168.1.101 ip地址
system = 账号
Admin_123 = 密码
zcdbstandard = 实例名
删除公共DBlink
DROP PUBLIC DATABASE LINK dblink名称
删除私有DBlink
DROP DATABASE LINK dblink名称
3、创建表空间
CREATE TABLESPACE TEXT --创建表空间
LOGGING
DATAFILE 'E:\OracleDBF\TEXT.DBF' --表空间存储路径
SIZE 1024M --表空间大小
AUTOEXTEND ON --自动增长
maxsize unlimited --增长无限大
EXTENT MANAGEMENT LOCAL;
4、设置密码永不过期
默认密码策略使用一段时间之后将会要求修改密码
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
# 已过期或者已经创建好的用户，登录sys账号，先修改密码为其他的，然后在改回原密码
alter user system identified by oracle;
5、修改最大连接数
sys账号登陆
-- 查看最大连接数
select VALUE from v$parameter where name='processes';
-- 查看当前连接数
select count (*) from V$process;
-- 修改最大连接数
alter system set processes=1000 scope=spfile
-- 重启数据库服务器
6、创建DBA用户
CREATE USER "ROG" IDENTIFIED BY "oracle";
GRANT "DBA" TO "ROG";
GRANT "CONNECT" TO "ROG";
GRANT "RESOURCE" TO "ROG";
ALTER USER "ROG" DEFAULT ROLE "DBA","CONNECT","RESOURCE";
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO "ROG";
alter user "ROG" default tablespace asset;
7、imp用户模式导入数据
imp system/oracle@192.168.1.232/orcl file=E:\OracleDBF\Backup\20220308\text_20220308_bak.dmp fromuser=rog touser=orcl ignore=y statistics=none
statistics=none # 不导入数据库统计信息
fromuser=rog touser=orcl # 把rog用户导入到orcl用户
file=E:\OracleDBF\Backup\20220308\text_20220308_bak.dmp # 导入的dump文件
system/oracle@192.168.1.232/orcl # 连接信息
8、触发器
实现ID列自增长
# oracle数据库id自增 使用序列和触发器
# 创建序列 prd_cfd_hqdx_parent_id 序列名
Create Sequence prd_cfd_hqdx_parent_id
Increment by 1 -- 每次加几个
start with 1 -- 从1开始计数
nomaxvalue -- 不设置最大值,设置最大值：maxvalue 9999
nocycle -- 一直累加，不循环
cache 10;
# 创建触发器 prd_cfd_hqdx_parent_Sequence 触发器名 prd_cfd_hqdx_parent 表名
Create trigger prd_cfd_hqdx_parent_Sequence before
insert on prd_cfd_hqdx_parent for each row /*对每一行都检测是否触发*/
begin
select prd_cfd_hqdx_parent_id.nextval into:New.ID from dual;
end;
# 删除序列：
DROP SEQUENCE 序列名;
# 删除触发器
DROP TRIGGER 触发器名;
9、表名字段名转大写
# 所有表名改大写
begin
for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
begin
execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;
exception
when others then
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;
# 运行后数据库表字段名全替换成大写 //不包括oracle关键字
begin
for cl in (SELECT table_name,column_name from user_tab_columns WHERE column_name<>upper(column_name) and upper(column_name) not in('SIZE','CHECK')) loop
begin
execute immediate 'alter table '||cl.table_name||' rename column "'|| cl.column_name ||'" to '||upper(cl.column_name);
exception
when others then
dbms_output.put_line(cl.table_name||'.'||cl.column_name||'已存在');
end;
end loop;
end;
10、添加常用函数，Oracle默认不自带
ISNULL
# 创建isnull函数
create or replace function isnull(i_obj in varchar2, i_obj2 in varchar2)
return varchar2
is
begin
return nvl(i_obj, i_obj2);
end isnull;
LEFT
CREATE OR REPLACE FUNCTION "LEFT" (str in varchar2,sublen in integer) return varchar2 is
strlen integer;
begin
strlen := length(str);
if sublen<=0 then
return '';
elsif strlen<=sublen then
return str;
else
return SUBSTR(str,0,sublen);
end if;
return '';
end LEFT;
RIGHT
CREATE OR REPLACE FUNCTION "LEFT" (str in varchar2,sublen in integer) return varchar2 is
strlen integer;
begin
strlen := length(str);
if sublen<=0 then
return '';
elsif strlen<=sublen then
return str;
else
return SUBSTR(str,0,sublen);
end if;
return '';
end LEFT;
11、Oracle插入日期数据
Oracle插入日期需要进行转换
Oracle插入日期
to_date ( '2007-12-20 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' )
to_date ( '2007-12-14 14:10' , 'YYYY-MM-DD HH24:MI' )
to_date ( '2007-12-14 14' , 'YYYY-MM-DD HH24' )
to_date ( '2007-11-15' , 'YYYY-MM-DD' )
to_date ( '2007-09' , 'YYYY-MM' )
to_date ( '2007' , 'YYYY' )
12、Oracle实现update from
子查询方式实现
UPDATE A SET A.NAME =( SELECT B.NAME FROM B WHERE B.ID =A.ID) WHERE A.ID IN ( SELECT ID FROM B);
视图方式实现
UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID = B.ID)
SET ANAME = BNAME;
13、Oracle用户模式备份bat脚本
脚本运行之前需要安装好Bandizip软件，脚本使用了Bandizip的7z压缩命令
@echo on
setlocal
rem 设置数据库和备份文件参数...
set Ymd=%date:~,4%%date:~5,2%%date:~8,2%
set bakdir=G:\OracleDB\Backup
set sid=192.168.1.211/asset
set username=system
set password=oracle
set dbname=rog
md %bakdir%\%Ymd%
set connect=%username%/%password%@%sid%
rem 执行文件备份......
D:\Oracle11g\product\11.2.0\dbhome_1\BIN\exp %connect% file=%bakdir%\%Ymd%\%dbname%_backup_%Ymd%.dmp owner=%dbname%
D:\Bandizip\bz.exe c %bakdir%\%Ymd%\%dbname%_backup_%Ymd%.7z %bakdir%\%Ymd%\%dbname%_backup_%Ymd%.dmp
del /f %bakdir%\%Ymd%\%dbname%_backup_%Ymd%.dmp
endlocal
exit
14、常见报错
1、ORA-12518
错误信息：TNS:listener could not hand off client connection
客户连接到监听器后，监听器把客户重定向到调度程序端口，由于操作系统问题，这些连接会被拒绝。
要解决这个问题，只需要在 LISTENER.ORA 的头部加入这一行 DIRECT_HANDOFF_TTC_LISTENER = OFF 即可
然后重启监听服务
lsnrctl stop
lsnrctl start2、Navicat连接报错
错误信息：Oracle library is not loaded
是oci.dll版本不对。因为Navicat是通过Oracle客户端连接Oracle服务器的，Oracle的客户端分为两种，一种是标准版，一种是简洁版，即Oracle Install Client。而我们用Navicat时通常会在自己的安装路径下包含多个版本的OCI，如果使用Navicat连接Oracle服务器出现错误时，多数是因为Navicat本地的OCI版本与Oracle服务器不符造成的。所以我们要做的就是下载对应版本的OCI使之与我们所安装的Oracle服务器相符合。
OCI下载地址：Oracle Instant Client Downloads | Oracle 中国
下载解压好到指定位置，Navicat环境变量配置一下即可
正文完
