# 数据库笔记

# oracle表空间相关操作

oracle存储结构

# 查看表空间的名称及大小

    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;

# 查看表空间物理文件的名称及大小

    SELECT tablespace_name, 
    file_id, 
    file_name, 
    round(bytes / (1024 * 1024), 0) total_space 
    FROM dba_data_files 
    ORDER BY tablespace_name;

# 查看回滚段名称及大小

    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;

# 查看控制文件

    SELECT NAME FROM v$controlfile;

# 查看日志文件

    SELECT MEMBER FROM v$logfile;

# 查看表空间的使用情况

    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;

# 查看数据库库对象

    SELECT owner, object_type, status, COUNT(*) count# 
    FROM all_objects 
    GROUP BY owner, object_type, status;

# 查看数据库的版本

    SELECT version 
    FROM product_component_version 
    WHERE substr(product, 1, 6) = 'Oracle';

# 查看数据库的创建日期和归档方式

    SELECT created, log_mode, log_mode FROM v$database; 
    SELECT file_name, tablespace_name, bytes/(1024*1024) M  FROM dba_data_files;

# 修改表空间数据文件的大小

    ALTER database datafile '/u01/app/oracle/product/10.1/dbs/users01.dbf' RESIZE 32M;

# 在表空间新增加数据文件

    ALTER TABLESPACE users ADD DATAFILE '/oralcda/u03/app/oracle/oradata/prod/users02.dbf' SIZE 10M;

# 重新查询

    SELECT file_name, tablespace_name, bytes/(1024*1024) M  FROM dba_data_files;

# Oracle中创建用户和授权

# orache创建用户：

CREATE USER USERNAME IDENTIFIED BY PASSWORD
 default tablespace ERP
temporary tablespace TEMP;

创建用户名为： USERNAME, 密码为 PASSWORD 的用户，指定默认表空间为ERP，临时表空间为TEMP

# 分配权限：

GRANT CONNECT TO USERNAME;  --授权普通用户
GRANT RESOURCE  TO USERNAME;  --授权开发人员
GRANT DBA TO USERNAME; --授予DBA权限
GRANT UNLIMITED TABLESPACE TO USERNAME;--授予不限制的表空间
GRANT SELECT ANY TABLE TO USERNAME; --授予查询任何表
GRANT SELECT ANY DICTIONARY TO USERNAME;--授予 查询 任何字典

# 常用查询

查看所有用户：

select * from dba_users;
select * from all_users;
select * from user_users;

查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs;
select * from user_sys_privs;

查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

查看用户对象权限：

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

查看所有角色：

select * from dba_roles;

查看用户或角色所拥有的角色：

select * from dba_role_privs;
select * from user_role_privs;

查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

SqlPlus中查看一个用户所拥有权限

SQL>select * from dba_sys_privs where grantee='username';

Oracle删除指定用户所有表的方法

select 'Drop table '||table_name||';' from all_tables
where owner='要删除的用户名(注意要大写)';

删除用户

drop user user_name cascade;

查询当前用户下所有的表：

select table_name from user_tables;

删除用户下所有的表数据:

select 'truncate table  ' || table_name from user_tables;

# Oracle查询锁表和解锁

Oracle数据库操作中，我们有时会用到锁表查询以及解锁和kill进程等操作

锁表查询的代码有以下的形式：

    select count(*) from v$locked_object;
    select * from v$locked_object;

查看哪个表被锁

    select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

查看是哪个session引起的

    select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

杀掉对应进程

    alter system kill session'1025,41';

其中1025为sid,41为serial#.

# 快速查询和kill

    --查锁表进程
    select sess.sid, 
  sess.serial#, 
  lo.oracle_username, 
  lo.os_user_name, 
  ao.object_name, 
  lo.locked_mode 
  from v$locked_object lo, 
  dba_objects ao, 
  v$session sess 
    where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
    select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

   --杀锁表进程
   alter system kill session '1383,52821';

# Oracle dblink应用

# 查看dblink

select owner,object_name from dba_objects where object_type='DATABASE LINK';
or
select * from dba_db_links;

# 创建dblink

前提: 创建dblink的用户有对应的数据库权限

create public database link 或者create database link ;

grant create public database link,create database link to myAccount;

create public database link dblinkname connect to username identified by password
　　 using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)';

如果在create之后不加public,则创建的dblink就不是公共的,就只有创建者可以使用了.

如何确定数据库的servicename:

sqlplus中使用

show parameter[s] service_names;

注意parameter和parameters都可以或者

select name,value from v$parameter where name='service_names';

# 使用db link

例如,在本机数据库上创建了一个scott_rmthost的public dblink(使用远程主机的scott用户连接),则用sqlplus连接到本机数据库,执行select * from scott.emp@scott_rmthot即可以将远程数据库上的scott用户下的emp表中的数据获取到. 也可以在本地建一个同义词来指向scott.emp@scott_rmthost,这样取值就方便多了.

# 删除dblink

drop public database link dblinkname;

例:

create public database link chengwenit connect to chengweniterp
identified by "123456" 　　 using '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.102)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME =CWIT) ) )';

# Oracle循环语句参考示例

--备份错误数据
create table bms_batch_def_noprice0714 as(
select *
  from bms_batch_def t
 where nvl(t.unitprice, 0) = 0
   and t.createfrom = 7);
--修改错误数据
begin
  for c1 in (select *
   from bms_batch_def t
  where nvl(t.unitprice, 0) = 0
    and t.createfrom = 7) loop
    update bms_batch_def a
 set (a.notaxsuprice, a.unitprice) =
     (select b.notaxsuprice, b.unitprice
  from bms_batch_def b
 where b.batchid = c1.oldbatchid)
     where a.batchid = c1.batchid;
  end loop;
end;

# Oracle job应用

# 创建Oracle Job

var job1 number;
begin
  dbms_job.submit(:job1, 'test;', sysdate,'sysdate+1/1440');
  commit;
  dbms_job.run(:job1);
end;
/

# 常用的定时时间

每分钟执行 Interval => TRUNC(sysdate,’mi’) + 1 / (24*60)
每天定时执行

例如：每天的凌晨2点执行 Interval => TRUNC(sysdate) + 1 +2 / (24)

每周定时执行

例如：每周一凌晨2点执行

Interval => TRUNC(next_day(sysdate,'星期一'))+2/24

每月定时执行

例如：每月1日凌晨2点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

每季度定时执行例如每季度的第一天凌晨2点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
每半年定时执行

例如：每年7月1日和1月1日凌晨2点 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

每年定时执行

例如：每年1月1日凌晨2点执行 Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

# SQLserver查询锁表和解锁

由于写错了一个过滤条件kettle从用友U8数据库中拉取发票明细数据时数据量过大强制停止后仍然在无法查询，怀疑是被锁表了。

查询锁表语句

select request_session_id spid,
 OBJECT_NAME(resource_associated_entity_id) tableName
  from sys.dm_tran_locks
 where resource_type = 'OBJECT'

强制解锁

declare @spid  int 
Set @spid  = 58 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

# Oracle截取字符串

oracle 截取字符(substr)，检索字符位置(instr) case when then else end语句使用常用函数：substr和instr

# SUBSTR（string,start_position,[length]）求子字符串，返回字符串

string 元字符串
start_position 开始位置（从0开始）
length 可选项，子字符串的个数

# 示例

substr("ABCDEFG", 0); -- 返回：ABCDEFG，截取所有字符
substr("ABCDEFG", 2); -- 返回：CDEFG，截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); -- 返回：ABC，截取从A开始3个字符，字符串排列位置不变。
substr("ABCDEFG", 0, 100); -- 返回：ABCDEFG。
substr("ABCDEFG", -3); -- 返回：EFG，注意参数-3，为负值时表示从尾部开始算起

# INSTR（string,subString,position,ocurrence）查找字符串位置

string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串

# 示例

INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为 14

# Oracle中exp,imp的使用详解

# EXP:

完全(需要有特殊权限)：

EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y

用户模式(用户所有对象被输出到文件)：

EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=chengwenit

表模式：

EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=CHENGWENIT TABLES=(CHW_USERS)

# IMP:

完全：

IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y

用户模式：

IMP chwit/chwit BUFFER=64000 FILE=C:\chwit.DMP FROMUSER=chw TOUSER=chwit

表模式：

imp system/manager file=tank log=tank fromuser=chengwenTABLES=(a,b)

# EXP导出实例

# 将数据库TEST完全导出，用户名system 密码manager 导出到D：\daochu.dmp中
exp system/manager@TEST file=d：\daochu.dmp full=y
# 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d：\daochu.dmp owner=（system，sys）
# 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d：\daochu.dmp tables=（table1，table2）
# 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d：\daochu.dmp tables=（table1） query=\" where filed1 like '00%'\"

# IMP导入实例

# 将D：\daochu.dmp 中的数据导入 TEST数据库中。表已存在忽略则在后面加上 ignore=y 就可以了。
imp system/manager@TEST  file=d：\daochu.dmp

# 将d：\daochu.dmp中的表table1 导入
imp system/manager@TEST  file=d：\daochu.dmp  tables=（table1）

# Oracle ADG主从同步 dataguard(windows to linux )

# 前言

用户目前用的Windows两个Oracle实例500G左右的表空间大小。因性能和安全性方面的考虑，需要在目前基础上新增一个从库读写分离，分担分析报表和外部流向溯源系统、UDI、金税、监管接口等查询的性能压力，以及预防在主库异常(硬件故障/被攻击)时进行故障转移。

从库选择Linux安装Oracle 11g.(相同版本、字符集),如果是出事搭建，推荐使用一致的操作系统版本和一致的表空间数据文件存储路径，搭建更简单。

注意：主从同步不能替离线备份，因为基于日志归档的实时主从同步，在主库误操作无法回滚后，从库也实时做了同样的操作。此时部分数据还需要从离线备份中恢复。

# 环境准备

查询主库版本和字符集

-- 查询版本
SELECT * FROM V$VERSION;
-- 查询字符集
select userenv('language') from dual;
--数据库名
select name from v$database;

安装从库，字符集/数据库名，版本一致参考静默安装Oracle，仅安装数据库，参考
主库和从库HOSTS增加解析

10.11.11.241  erpora0
10.11.11.242  erpora1

linser和tns配置 1.1 通信准备(监听和tns配置) %ORACLE_HOME%/NETWORK/ADMIN目录下 listener.ora tnsnames.ora

# 这是我主库的 ip和端口，主库取名为 MAIN，要连接的数据库名为 orcl

WINORA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.11.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERP) ) )

# 这是要通信的备库IP 和端口，取名为 BACK，要连接的数据库名为 orcl

LINORA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERP) ) )

开启强制归档 shutdown immediate; startup mount; alter database force logging; alter database archivelog;

alter database add standby logfile group 4 ('D:\ERPORA\standby_log04.log') size 50M;
alter database add standby logfile group 5 ('D:\ERPORA\standby_log05.log') size 50M;
alter database add standby logfile group 6 ('D:\ERPORA\standby_log06.log') size 50M;
alter database add standby logfile group 7 ('D:\ERPORA\standby_log07.log') size 50M;

# 主库参数配置

create pfile = 'D:\app\Administrator\pfile' from spfile;

*.db_unique_name='ERPORA' *.fal_client='ERPORA' *.fal_server='ERPORAB' *.log_archive_config='dg_config=(ERPORA,ERPORAB)' *.log_archive_dest_1='location=D:\ARCHIVELOG valid_for=(all_logfiles,all_roles) db_unique_name=ERPORA' *.log_archive_dest_2='service=ERPORAB lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=ERPORAB' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_max_processes=4 *.db_file_name_convert='/db/app/oracle/oradata/ERPORA','D:\ERPORA' *.log_file_name_convert='/db/app/oracle/oradata/ERPORA','D:\ERPORA' *.standby_file_management='auto'

--启动主库 shutdown immediate; startup mount pfile='D:\app\Administrator\pfile';

--nomount启动备库 startup nomount pfile='/db/app/oracle/oradata/pfile';

--配置密码文件 orapwd file=./orapwERPORA password=pass entries=2 force=y

--测试密码 sqlplus sys/pass@ERPORA as sysdba sqlplus sys/pass@ERPORAB as sysdba

登录rman

rman target sys/pass@ERPORA auxiliary sys/pass@ERPORAB

开始复制 duplicate target database for standby from active database nofilenamecheck;

主备库分别创建spfile create spfile from pfile='D:\app\Administrator\pfile'; --主库 create spfile from pfile='/db/app/oracle/oradata/pfile'; --备库

配置文件位置

--查询数据文件位置
select name from v$datafile;
-- 如果主库与备库数据文件位置不相同，则需要使用db_file_name_convert来转换。
alter system set db_file_name_convert='/data/oracle/oradata/chwit1','C:\APP\ADMINISTRATOR\ORADATA\SFXT' scope=spfile;
--log_file_name_convert 查看在线日志文件的位置：
select member from v$logfile;
--如果主库与备库在线日志文件位置不相同，则需要使用log_file_name_convert来转换。
alter system set log_file_name_convert='/data/oracle/oradata/chwit1','C:\APP\ADMINISTRATOR\ORADATA\SFXT' scope=spfile;

配置 standby_file_management、fal_client、fal_server

alter system set standby_file_management=auto scope=spfile;
alter system set fal_client='tnssfx' scope=both;
alter system set fal_server='tnschwit1' scope=both;

创建文件夹

mkdir C:\app\Administrator\product\11.2.0\dbhome_1\archivelog

应用新参数文件

--一致性停库
shutdown immediate; 
--创建新的spfile
create spfile from pfile; 
startup;

添加主库和备库的standby日志组

standby日志组个数：配置为redo日志组个数+1，在主库与备库都添加standby日志组。主库可以不添加，如果考虑备库要切成主库就需要一起添加。

--只查询standby日志组：
select * from v$standby_log ;
alter database add standby logfile group 4 ('C:\app\Administrator\oradata\chwit0\stredo04.log') size 50M;
alter database add standby logfile group 5 ('C:\app\Administrator\oradata\chwit0\stredo05.log') size 50M;
alter database add standby logfile group 6 ('C:\app\Administrator\oradata\chwit0\stredo06.log') size 50M;
alter database add standby logfile group 7 ('C:\app\Administrator\oradata\chwit0\stredo07.log') size 50M;

主库静态监听配置

/data/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
)
)
)

SID_LIST_LISTENER = 
(SID_LIST = 
(SID_DESC = 
(GLOBAL_DBNAME=chwit0)
(SID_NAME = chwit0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
) 
) 

ADR_BASE_LISTENER = /u01/oracle

主库tnsnames.ora文件配置

/data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

tnschwit0 =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.132)(PORT = 1521))
 )
 (CONNECT_DATA =
   (SERVICE_NAME = chwit0)
 )
)

tnschwit1 =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.131)(PORT = 1521))
 )
 (CONNECT_DATA =
   (SERVICE_NAME = chwit0)
 )
)

# 备库配置

设置一个密码文件

# 指定standby database的ORACLE_SID
export ORACLE_SID=stddb
# 跳转到dbs文件夹下
cd $ORACLE_HOME/dbs
# 创建密码文件
orapwd file=orapwchwit1 password=sys123

初始化参数文件

--在主库生成初始化参数文件
SQL> create pfile from spfile;

复制主库的参数文件到备库并重命名后修改备库的参数文件内容

chwit0.__java_pool_size=4194304
chwit0.__large_pool_size=4194304
chwit0.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
chwit0.__pga_aggregate_target=343932928
chwit0.__sga_target=515899392
chwit0.__shared_io_pool_size=0
chwit0.__shared_pool_size=192937984
chwit0.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/chwit1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oracle/oradata/chwit1/control01.ctl','/data/oracle/flash_recovery_area/chwit1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chwit0'
*.db_recovery_file_dest='/data/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chwit0XDB)'
*.local_listener='LISTENER_SFXT'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_unique_name=chwit1
log_archive_config='dg_config=(chwit0,chwit1)'
log_archive_dest_1=''LOCATION=/data/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chwit1'
log_archive_dest_2='SERVICE=tnschwit0 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chwit0'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert='C:\APP\ADMINISTRATOR\ORADATA\SFXT','/data/oracle/oradata/chwit1'
log_file_name_convert='C:\APP\ADMINISTRATOR\ORADATA\SFXT','/data/oracle/oradata/chwit1'
fal_client='tnschwit1'
fal_server='tnschwit0'
standby_file_management='AUTO'

mkdir -p /data/oracle/admin/chwit1/adump
mkdir -p /data/oracle/oradata/chwit1
mkdir -p  /data/oracle/flash_recovery_area/chwit1

复制控制文件过来

注意控制文件一般有两个

cp C:\app\Administrator\oradata\chwit0\CONTROL01.CTL
cp C:\app\Administrator\flash_recovery_area\chwit0\CONTROL02.CTL

使用pfile文件创建spfile文件

--登陆到idle数据库
sqlplus / as sysdba
--创建spfile
create spfile from pfile
--启动数据库到nomount状态
startup nomount

配置静态监听

[oracle@standbynode admin]$ vim listener.ora

# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
)
)
)

SID_LIST_LISTENER = 
(SID_LIST = 
(SID_DESC = 
(GLOBAL_DBNAME=chwit0)
(SID_NAME = chwit1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
) 
) 

ADR_BASE_LISTENER = /data/oracle

配置tnsnames.ora文件,直接复制主库的
测试网络连通性

主库与备库都测试执行确认可以正常访问

tnsping tnschwit0
tnsping tnschwit1

在主库测试连接备库

sqlplus sys/sys123@tnschwit0 as sysdba
sqlplus sys/sys123@tnschwit1 as sysdba

# 参数检查

参数	说明
db_unique_name	2个节点需要不一样
compatible	主库与备库兼容性需一致
log_archive_config	配置主库与备库的db_unique_name
log_archive_dest_1	归档日志的路径
log_archive_dest_2	归档日志的路径
log_archive_dest_state_2	enable：启用,defer:禁用
db_file_name_convert	数据文件转换路径
log_file_name_convert	日志文件转换路径
standby_file_management	设置为auto
log_archive_format	日志文件格式，两边需一致

# 使用duplicate创建物理standby

备库端执行

#： 一定要加nocatalog，否则在执行duplicate时会报错
[oracle@primarynode ~]$ rman target sys/sys123@chwit0 auxiliary sys/sys123@chwit1 nocatalog

使用duplicate复制数据库

RMAN> duplicate target database for standby from active database nofilenamecheck;
-- 开始同步数据库(备库执行)
alter database open;  
--开启实时同步
alter database recover managed standby database using current logfile disconnect from session;
--方式二：开启同步（日志切换时才同步）
alter database recover managed standby database disconnect from session;

# 检查是否执行成功

主库状态查看

select    open_mode,      --数据库打开模式，如果实时同步，则为：read only with apply，取消同步则为：read only
          database_role,       --数据库角色，是主库还是备库
          protection_mode,     --保护模式
          protection_level     --保护级别
from  v$database;

# 主库

OPEN_MODE	DATABASE_ROLE	PROTECTION_MODE	PROTECTION_LEVEL
READ WRITE	PRIMARY	MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE

# 备库

OPEN_MODE	DATABASE_ROLE	PROTECTION_MODE	PROTECTION_LEVEL
READ ONLY WITH APPLY	PHYSICAL STANDBY	MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE

# Oracle中创建unique唯一约束(单列和多列)

oracle中的unique约束是用来保证表中的某一类，或者表中的某一类组合不重复。

如下建表时并将其中的fname和lname组合起来建立了一个唯一约束。

　　create table unique_test
　　(id number,
　　fname varchar2(20),
　　lname varchar2(20),
　　address varchar2(100),
　　email varchar2(40),
　　constraint name_unique unique(fname,lname))

建表后增加约束

alter table unique_test
　　add constraint email_unique unique(email);

# Oracle自动备份脚本

set backupfile=crm%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp 
--数据文件名
set logfile=crm%date:~0,4%-%date:~5,2%-%date:~8,2%.log 
--日志文件名
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\expdp coremail2016/123456@orcl directory=WSCDIR schemas=coremail2016 dumpfile=%backupfile%  logfile=%logfile% 
--备份命令
forfiles /p "D:\databack" /d -10  /c "cmd /c echo deleting @file ... && del /f @path"
--删除10天前备份

# Oracle查找重复记录

SELECT *
FROM t_info a
WHERE ((SELECT COUNT(*)
          FROM t_info
          WHERE Title = a.Title) > 1)
ORDER BY Title DESC

# 查找重复记录

-- 查找全部重复记录
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
-- 过滤重复记录(只显示一条)
Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)
-- 注：此处显示ID最大一条记录
-- 查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

# 删除重复记录

-- 删除全部重复记录（慎用,全部删除）
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
-- 重复记录保留一条
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)
-- 注：此处保留ID最大一条记录
-- 删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

# Oracle 汉字转换首字母函数

CREATE OR REPLACE FUNCTION FNC_CWIT_GET_PYM
                          (iv_str IN CHAR)
  RETURN VARCHAR2
  IS
  v_frmstr           VARCHAR2(100) := '（）【】：孢呋羟西漯';
  v_tostr            VARCHAR2(100) := '()[]:包福枪洗罗';
  i_pos              INTEGER;
  lv_ch              VARCHAR2(100);
  lv_retstr          VARCHAR2(1000);
BEGIN
  --依次处理v_str中每个字符
  FOR i IN 1..LENGTH(iv_str)
  LOOP
    lv_ch := SUBSTR(iv_str , i , 1);

    IF ASCII(lv_ch) < 128 THEN--非汉字
      lv_retstr := lv_retstr || lv_ch;
    ELSE--是汉字
      i_pos := INSTR(v_frmstr, lv_ch);
      IF i_pos > 0 THEN
        lv_ch := SUBSTR(v_tostr, i_pos, 1);
      END IF;

      lv_retstr := CASE lv_ch
                   WHEN GREATEST(lv_ch, '匝') THEN lv_retstr || 'Z'
                   WHEN GREATEST(lv_ch, '丫') THEN lv_retstr || 'Y'
                   WHEN GREATEST(lv_ch, '夕') THEN lv_retstr || 'X'
                   WHEN GREATEST(lv_ch, '哇') THEN lv_retstr || 'W'
                   WHEN GREATEST(lv_ch, '他') THEN lv_retstr || 'T'
                   WHEN GREATEST(lv_ch, '撒') THEN lv_retstr || 'S'
                   WHEN GREATEST(lv_ch, '然') THEN lv_retstr || 'R'
                   WHEN GREATEST(lv_ch, '七') THEN lv_retstr || 'Q'
                   WHEN GREATEST(lv_ch, '趴') THEN lv_retstr || 'P'
                   WHEN GREATEST(lv_ch, '哦') THEN lv_retstr || 'O'
                   WHEN GREATEST(lv_ch, '拿') THEN lv_retstr || 'N'
                   WHEN GREATEST(lv_ch, '妈') THEN lv_retstr || 'M'
                   WHEN GREATEST(lv_ch, '廓') THEN lv_retstr || 'L'
                   WHEN GREATEST(lv_ch, '咖') THEN lv_retstr || 'K'
                   WHEN GREATEST(lv_ch, '讥') THEN lv_retstr || 'J'
                   WHEN GREATEST(lv_ch, '哈') THEN lv_retstr || 'H'
                   WHEN GREATEST(lv_ch, '嘎') THEN lv_retstr || 'G'
                   WHEN GREATEST(lv_ch, '发') THEN lv_retstr || 'F'
                   WHEN GREATEST(lv_ch, '讹') THEN lv_retstr || 'E'
                   WHEN GREATEST(lv_ch, '搭') THEN lv_retstr || 'D'
                   WHEN GREATEST(lv_ch, '擦') THEN lv_retstr || 'C'
                   WHEN GREATEST(lv_ch, '八') THEN lv_retstr || 'B'
                   WHEN GREATEST(lv_ch, '阿') THEN lv_retstr || 'A'
                   ELSE lv_retstr || lv_ch
                   END;
    END IF;
  END LOOP;

  RETURN lv_retstr;

EXCEPTION WHEN OTHERS THEN
  RETURN '0';

END FNC_CWIT_GET_PYM;

# Oracle创建自增序列触发器

create table gd_wms_erp_1
(
dtlid varchar(10) not null,
 docno varchar(20) ,
 credate date,
 customerno varchar(20),
 goodsno   varchar(20),
 goodsqty   number(10),
 goodsunit  varchar(10),
 goodsprice number(10,6),
 lotno   varchar(10),
 suflag  varchar(2),
 saflag  varchar(2),
 propdate date,
 valdate date,
 primary key (dtlid)
 )
 
create sequence gd_wms_erp_1_seq
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 10;

create or replace trigger  gd_wms_erp_1_seq_trigger      
before insert on gd_wms_erp_1      
for each row      
begin      
select gd_wms_erp_1_seq.nextval into :new.dtlid from dual;    
end ;

# Oracle密码过期处理

-- 修改密码永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
-- 修改密码
alter user xxerp identified by pass1234

# Oracle数据泵(impdp,expdp)导出导入

oracle 10g增加了data pump提供的是一种基于服务器的数据提取和恢复的实用程序，data pump在体系结构和功能上与传统的export和import实用程序相比有了明显提升。注意：数据泵文件与传统的exp/imp数据转储文件是不兼容的

查看数据库中已创建的directory的两个视图

select * from all_directories; 
select * from dba_directories;

创建directory

create directory test as '/media/test'; 
sql> grant read,write on directory test to system;

数据泵导出

#单个用户方案导出，用得最多的昌此种方式
expdp [用户名]/[密码]@[主机字符窜] schemas=[用户名] directory=test dumpfile=x.dmp logfile=x.log
#数据库全库导出
expdp [用户名]/[密码]@[主机字符窜] full=y directory=test dumpfile=x.dmp logfile=x.log
#用管理员导入多个用户
expdp [用户名]/[密码]@[主机字符窜] schemas=[用户名],[用户名1],[用户名2]…… directory=test dumpfile=x.dmp logfile=x.log

数据泵导入

#单个用户方案导入
impdp [用户名]/[密码]@[主机字符窜] schemas=[用户名] directory=test dumpfile=x.dmp logfile=x.log ignore=y
#数据库全库导入
impdp [用户名]/[密码]@[主机字符窜] full=y directory=test dumpfile=x.dmp logfile=x.log ignore=y
#old用户导入new用户
remap_schema=chwit:chwitnew
# 增加过滤查询条件导出
expdp scott/tiger@back directory=dpdata1 dumpfile=expdp.dmp tables=emp query='where deptno=20';

示例：改变表的owner 从scott 用户导入到system 用户下，表空间从 tablespace1 到 tablespace2

impdp system/manager directory=dpdata1 dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:system remap_tablespace=tablespace1:tablespace2

# Oracle常用正则表达式

REGEXP_LIKE ：与LIKE的功能相似
REGEXP_INSTR ：与INSTR的功能相似
REGEXP_SUBSTR ：与SUBSTR的功能相似

参数	释义
source_string	源串，可以是常量，也可以是某个值类型为串的列
position	从源串开始搜索的位置。默认为1
occurrence	指定源串中的第几次出现。默认值1
match_parameter	文本量，进一步订制搜索

REGEXP_REPLACE ：与REPLACE的功能相似

# 示例

--替换数据库中所有的非数字的字符
SELECT
     REGEXP_REPLACE (tel,'[^0-9]+',  '')  AS A
FROM
     lt_imp_cust_0519b;
--查询value中以1开头60结束的记录并且长度是7位
select * from chwtable where value like '1____60';
select * from chwtable where regexp_like(value,'1....60');
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
--使用like就不是很好实现了。
select * from chwtable where regexp_like(value,'1[0-9]{4}60');
-- 也可以这样实现，使用字符集。
select * from chwtable where regexp_like(value,'1[[:digit:]]{4}60');
-- 查询value中不是纯数字的记录
select * from chwtable where not regexp_like(value,'^[[:digit:]]+$');
-- 查询value中不包含任何数字的记录。
select * from chwtable where regexp_like(value,'^[^[:digit:]]+$');
--查询以12或者1b开头的记录.不区分大小写。
select * from chwtable where regexp_like(value,'^1[2b]','i');
--查询以12或者1b开头的记录.区分大小写。
select * from chwtable where regexp_like(value,'^1[2B]');
-- 查询数据中包含空白的记录。
select * from chwtable where regexp_like(value,'[[:space:]]');
--查询所有包含小写字母或者数字的记录。
select * from chwtable where regexp_like(value,'^([a-z]+|[0-9]+)$');
--查询任何包含标点符号的记录。
select * from chwtable where regexp_like(value,'[[:punct:]]');

# Oracle复制表

-- 复制表结构及其数据：
create table table_name_new as select * from table_name_old
-- 只复制表结构：
create table table_name_new as select * from table_name_old where 1=2;
-- 
create table table_name_new like table_name_old

# Oracle 常用函数（待补充）

# 时间日期函数

# 字符串函数

TRIM 去除收尾空格

# 统计函数

# 分组排序判断函数

# 高级函数(over等)

# MySQL事件创建和修改（类似Oracle job)

写了几个事件定期转储日志，因日志内容增加需修改mysql事件

# 更改事件ALTER EVENT语句，如下所示：

-- 查询事件
SHOW EVENTS FROM studymysql;

-- 修改事件
ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
  event_body

# 创建事件，每分钟向messages表中插入一条新记录。

CREATE EVENT test_event_04
ON SCHEDULE EVERY 1 MINUTE
DO
   INSERT INTO messages(message,created_at)
   VALUES('测试ALTER EVENT语句',NOW());

要使事件每2分钟运行一次，请使用以下语句：

ALTER EVENT test_event_04
ON SCHEDULE EVERY 2 MINUTE;

您还可以通过指定新逻辑来更改事件的主体，如下所示：

ALTER EVENT test_event_04
DO
   INSERT INTO messages(message,created_at)
   VALUES('Message from event',NOW());

# 禁用事件

ALTER EVENT test_event_04
DISABLE;

# oracle系统表v$session、v$sql 查看最近执行了哪些sql语句

数据库 session被一个运行在数据库服务器上或从中间服务器甚至桌面通过 SQL*Net连接到数据库的客户端进程启动，下列各列提供这个客户端的信息

OSUSER：客户端操作系统用户名
MACHINE：客户端执行的机器
TERMINAL：客户端运行的终端
PROCESS：客户端进程的 ID
PROGRAM：客户端执行的客户端程序

# session示例：

--查找你的 session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv( 'SESSIONID' );
--当 machine已知的情况下查找 session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1' ;
--查找当前被某个指定 session正在运行的 sql语句。假设 sessionID为 100
select b.sql_text
from v$session a,v$sqlarea b
where a.sql_hashvalue=b. hash_value and a.sid=  00;
或者
select b.sql_text
from v$session a,v$sqlarea b //v$sqlarea 只包括sql的1000个字符，V$SQLTEXT is all 
where a.sql_hash_value = b.HASH_VALUE and a.TERMINAL = 'BackHam';//BackHam is my computer name;

# V$SQLTEXT中的常用列

HASH_VALUE： SQL语句的 Hash值
ADDRESS： sql语句在 SGA中的地址
SQL_TEXT： SQL文本。
PIECE： SQL语句块的序号

# 执行sql查询示例

--查看消耗资源最多的 SQL：
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC ;
--查看某条 SQL 语句的资源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw( 'CBD8E4B0' );
--查找前10条性能差的sql语句 
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea   
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;
--EXECUTIONS表示同一条SQL语句一共执行了多少次，SORTS表示排序的次数，DISK_READS表示物理读的数量。

v$sql视图包含 cursor级别资料。当试图定位 session或用户以分析 cursor时被使用。PLAN_HASH_VALUE列存储的是数值表示的 cursor执行计划。可被用来对比执行计划。 PLAN_HASH_VALUE让你不必一行一行对比即可轻松鉴别两条执行计划是否相同。

V$SQL中的列说明：

SQL_TEXT： SQL文本的前 1000个字符
SHARABLE_MEM：占用的共享内存大小 (单位： byte)
PERSISTENT_MEM：生命期内的固定内存大小 (单位： byte)
RUNTIME_MEM：执行期内的固定内存大小
SORTS：完成的排序数
LOADED_VERSIONS：显示上下文堆是否载入， 1是 0否
OPEN_VERSIONS：显示子游标是否被锁， 1是 0否
USERS_OPENING：执行语句的用户数
FETCHES： SQL语句的 fetch数。
EXECUTIONS：自它被载入缓存库后的执行次数
USERS_EXECUTING：执行语句的用户数
LOADS：对象被载入过的次数
FIRST_LOAD_TIME：初次载入时间
INVALIDATIONS：无效的次数
PARSE_CALLS：解析调用次数
DISK_READS：读磁盘次数
BUFFER_GETS：读缓存区次数
ROWS_PROCESSED：解析 SQL语句返回的总列数
COMMAND_TYPE：命令类型代号
OPTIMIZER_MODE： SQL语句的优化器模型
OPTIMIZER_COST：优化器给出的本次查询成本
PARSING_USER_ID：第一个解析的用户 ID
PARSING_SCHEMA_ID：第一个解析的计划 ID
KEPT_VERSIONS：指出是否当前子游标被使用 DBMS_SHARED_POOL包标记为常驻内存
ADDRESS：当前游标父句柄地址
TYPE_CHK_HEAP：当前堆类型检查说明
HASH_VALUE：缓存库中父语句的 Hash值
PLAN_HASH_VALUE：数值表示的执行计划。
CHILD_NUMBER：子游标数量
MODULE：在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。
ACTION：在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。
SERIALIZABLE_ABORTS：事务未能序列化次数
OUTLINE_CATEGORY：如果 outline在解释 cursor期间被应用，那么本列将显示出 outline各类，否则本列为空
CPU_TIME：解析 /执行 /取得等 CPU使用时间 (单位，毫秒 )
ELAPSED_TIME：解析 /执行 /取得等消耗时间 (单位，毫秒 )
OUTLINE_SID： outline session标识
CHILD_ADDRESS：子游标地址
SQLTYPE：指出当前语句使用的 SQL语言版本
REMOTE：指出是否游标是一个远程映象 (Y/N)
OBJECT_STATUS：对象状态 (VALID or INVALID)
IS_OBSOLETE：当子游标的数量太多的时候，指出游标是否被废弃 (Y/N)

--查询$sql示例
select
s.sql_text,
b.name,b.position,b.dup_position,
b.value_string
from v$sql s, v$sql_bind_capture b
where s.hash_value=b.hash_value;

# 安装MSDE停到最后几秒不动

# 方法一

不要取消安装，打开任务管理器（CTRL+ALT+DEL），点开进程，找到一个setupre的进程结束掉，就可以继续安装了。

# 方法二

打开window的“任务管理器”（任务栏右键-任务管理器”或alt+ctrl+delete调出），在“任务管理器”进程中找到msiexec.exe进程（一般有3个），找到其中用户名不是system（系统进程）或者内存占用最小的（一般是5000k左右）-右键-结束进程，可快速安装msde数据库

# ms sqlserver sa密码命令行修改

点开始--运行，输入cmd确定
出现的对话框里输入OSQL -E
回车后出现1>后输入EXEC sp_password NULL, 'chengwenit.com', 'sa'
回车后出现2>后输入go

# MSDE/sqlserver2000 安装挂起清除

开始-运行窗口输入regedit，打开注册表编辑器，在HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager中找到PendingFileRenameOperations，删除该键值，关闭注册表编辑器。重新安装SQL Server 2000即可

# mysql添加用户和指定IP授权和回收相关操作

-- 创建用户
CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'], 
[user[IDENTIFIED BY [PASSWORD] 'password']]...
CREATE USER 'chwit'@'%' IDENTIFIED BY 'SXyVYtUkqh0b3rJ';
-- 授权
GRANT ALL ON *.* to itsm@'192.168.168.164' IDENTIFIED BY '*******'; 
GRANT ALL ON chwdms.* to chwit@'%' IDENTIFIED BY 'SXyVYtUkqh0b3rJ'; 

--创建数据库指定字符集
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

-- 回收
--下面的代码实现了取消sss用户的所有权限的功能。
revoke all on *.* from sss@localhost ;

-- 重置密码：
use mysql; 
update user set password=password("") where user=’root’ and host=’localhost’; flush privileges;

# Linux下Oracle11G的job没有执行-last_date为空

公司Oracle数据库从windows迁移到Linux之后，创建了几次Job每次都没有执行，last_date为空，但是next_date会正常更新

首先保证手动执行 dbms_job.run(job num) ，可以执行成功
然后查一下ORACLE默认的定时任务执行范围,这个值安装后默认是0，所有的Job都不会执行

select value from v$parameter where name like '%job_queue_processes%'

设置job执行范围

alter system set job_queue_processes =1000;

# Centos6 安装Mysql

查询是否已经有安装

yum list installed | grep mysql

如果发现有系统自带mysql，先卸载

yum -y remove mysql-libs.x86_64

下载国内 mysql源

wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

安装国内 mysql源

rpm -ivh mysql-community-release-el6-5.noarch.rpm

用yum repolist mysql查一下是否已经有mysql可安装文件

yum repolist all | grep mysql

6.安装mysql 服务器

yum install mysql-community-server

7.启动mysql服务

service mysqld start

8.初始设置root密码

mysql -u root
use mysql;
update user set password=PASSWORD("****") where User='root';

9.创建数据库

create database chwmis;

10.创建一个用户，所有地址都可以访问

grant all privileges on chwmis.* to fsyjx@'%' identified by 'fsyjx+2018';
flush privileges;

11.设置开机自动启动

# chkconfig mysqld on

12.mysql安全设置

mysql_secure_installation

为root用户设置密码；
删除匿名账号；
取消root用户远程登录；
删除test库和对test库的访问权限；
刷新授权表使修改生效。

# Oracle创建同义词授权用户使用

因公司一个项目需要和第三方WMS供应商做对接，开放数据库给对方访问，因考虑安全问题，给WMS开放的用户仅开放中间表的访问权限，通过同义词实现

创建中间表用户

CREATE USER erptowms IDENTIFIED BY erptowms;
GRANT CONNECT TO EPRTOWMS;  --允许连接
GRANT CREATE SESSION TO EPRTOWMS; --允许创建会话
GRANT CREATE SYNONYM TO kq2shr; --允许创建同义词

在我方数据库用户下创建中间表(例：

create table ZZERP_PDD_W(
OWNERID  varchar2(11),--  货主ID  
BILLCODE  varchar2(50),--  单据编号  　
BILLSN  number(10),--  单据序号  　
DATES  varchar2(10),--  日期  　
ONTIME  varchar2(10), --  时间  　
CAOZY  varchar2(11),--  操作员ID  　
SALEMAN  VARCHAR2(20),--  销售员  　
SUMMARIES  varchar2(200),--  摘要  　
GOODSID  varchar2(11),--  商品ID  　
ANGLEID  varchar2(11),--  维度ID  　
NUM  NUMBER(14,2),--  数量  
TAXPRICE  NUMBER(14,4),--  含税价  
TAXAMOUNT  NUMBER(14,2),--  含税金额  
UNIT  varchar2(8),--  单位  
BATCHCODE  varchar2(50),--  批号  　
PRODUCEDATE  varchar2(10),--  生产日期  　
VALDATE  varchar2(10),--  有效期至  　
CREATETIME  varchar2(19),--  创建时间  　
ZT  varchar2(10),--  NULL  　
STORELX  VARCHAR2(2),--  仓库类型  
ERPHW  VARCHAR2(20),--  业务维度  
DJBS  varchar2(3),--  单据标识  
REMARK  varchar2(256),--	备注	
DEPT	varchar2(20))--	部门

授权，下传WMS表，允许WMS select,update -- erp用户下执行

grant select,update on ZZERP_PDD   to erptowms;

授权WMS反馈表，允许WMS select,insert -- erp用户下执行

grant select,insert on ZZERP_PDD_W  to erptowms;

创建同义词 --WMS用户下执行

CREATE OR REPLACE SYNONYM ZZERP_PDD_W FOR GZCW.ZZERP_BSD;

# win10安装MSDE

如果是家庭版系统，先启用管理员账户： net user administrator /active:yes
msde安装目录下sqlunirl.dll 替换 c:windows\syswow64\sqlunirl.dll （无法替换配置文件所属用户组后修改权限再替换）再正常安装即可

# Oracle企业版迁移标准版

因标准版没有 deferred_segment_creation 功能，导入企业版dmp文件会报错在impdp导入时，增加 version = 10.2.0 参数即可

# 用友U8 sqlserver数据库变可疑状态处理

客户咨询无法登录，提示是sa登录失败，但是在服务器配置里面测试连接sql是正常的。打开数据库发现是UFSystem库变成可疑状态了。一般是数据库运行中直接关机的偶发现象。

# 解决方法：

当发生这种操作故障时，可以按以下操作步骤可解决，打开ssms,新建查询，输入以下的命令运行

    --修改数据库为紧急模式
    ALTER DATABASE [UFSystem] SET EMERGENCY;
    
    --使数据库修改为单用户模式
    ALTER DATABASE [UFSystem] SET SINGLE_USER;
    
    --修复数据库日志重新生成，此命令检查的分配，结构，逻辑完整性和所有数据库中的对象错误。当您指定“REPAIR_ALLOW_DATA_LOSS”作为DBCC CHECKDB命令参数，该程序将检查和修复报告的错误。但是，这些修复可能会导致一些数据丢失。
    DBCC CheckDB ([UFSystem], REPAIR_ALLOW_DATA_LOSS)
    
    ---使数据库修改为多用户模式
    ALTER DATABASE [UFSystem] SET MULTI_USER

被标记为“可疑”的数据库已恢复正常状态。

# SQLSERVER批量备份和恢复数据库

一个代账的客户有800多个客户，在一个数据库实例中有800多个数据库，需要进行批量迁移

开启在SQL中执行shell命令配置

USE master
GO
RECONFIGURE --先执行一次刷新，处理上次的配置
GO
EXEC sp_configure 'show advanced options',1 --启用xp_cmdshell的高级配置
GO
RECONFIGURE --刷新配置
GO
EXEC sp_configure 'xp_cmdshell',1  --打开xp_cmdshell,可以调用SQL系统之外的命令
GO
RECONFIGURE
GO

备份


DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @BAKSQL VARCHAR(1000)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

DECLARE CurDBName CURSOR FOR 
    SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute Backup
    SET @FileName = 'E:\BAK\' + @DBName + '_' + @CurrentTime
    SET @BAKSQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@BAKSQL)

    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName

恢复

declare
   @backpath varchar(2000),
   @execsql varchar(2000),
   @datapath varchar(2000),
   @lgname  varchar(64),
   @lgtype varchar(12),
   @lgfilename varchar(64),
   @i int
 begin
   set @backpath='E:\BAK\';
   set @execsql='dir /b '+ @backpath +'\*.BAK';
set @datapath='E:\DATA';
begin
  if not exists (select * from sysobjects where name='backfiles')
    create table backfiles(name varchar(2000));
  end
  truncate table backfiles
  insert into backfiles exec xp_cmdshell @execsql
 
  DELETE backfiles WHERE upper(right(name,3))<>'BAK' OR NAME IS NULL
 
  declare @fileName varchar(100),
          @dbName varchar(100)
  
  declare file_cur cursor for select name from backfiles;
  open file_cur
  fetch next from file_cur into @fileName
  while @@fetch_status=0
  begin
    select @dbName=substring(@fileName,1,charindex('.bak',@filename)-1);

    ----------------------------
    DECLARE @fileListTable TABLE (
        [LogicalName]   NVARCHAR(128), 
        [PhysicalName]   NVARCHAR(260), 
        [Type]     CHAR(1), 
        [FileGroupName]   NVARCHAR(128), 
        [Size]     NUMERIC(20,0), 
        [MaxSize]    NUMERIC(20,0), 
        [FileID]    BIGINT, 
        [CreateLSN]    NUMERIC(25,0), 
        [DropLSN]    NUMERIC(25,0), 
        [UniqueID]    UNIQUEIDENTIFIER, 
        [ReadOnlyLSN]   NUMERIC(25,0), 
        [ReadWriteLSN]   NUMERIC(25,0), 
        [BackupSizeInBytes]  BIGINT, 
        [SourceBlockSize]  INT, 
        [FileGroupID]   INT, 
        [LogGroupGUID]   UNIQUEIDENTIFIER, 
        [DifferentialBaseLSN] NUMERIC(25,0), 
        [DifferentialBaseGUID] UNIQUEIDENTIFIER, 
        [IsReadOnly]   BIT, 
        [IsPresent]    BIT, 
        [TDEThumbprint]   VARBINARY(32) -- remove this column if using SQL 2005 
    ) 
    INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@backpath+@fileName+'''') 
    set @execsql = '';
    set @execsql ='restore database '+'"'+@dbName+'"' + ' from disk='+CHAR(39)+@backpath+@fileName+CHAR(39) +' with ' ;
    -----------------
    set @i = 1
    declare bak_cur cursor for select 
          [LogicalName]
         ,reverse(substring(reverse([PhysicalName]),0,charindex('\',reverse([PhysicalName])))) as filen_ame
         ,[Type] 
       from @fileListTable
    open bak_cur
    fetch next from bak_cur into @lgname,@lgfilename,@lgtype
    while @@FETCH_STATUS = 0
    begin
      /*
      不考虑文件重名的情况，统一恢复到同一目录下 
      */
      set @execsql = @execsql + (case when @i = 1 then ' ' else ', ' end) + ' move '+CHAR(39)+@lgname+CHAR(39)+' to '+CHAR(39)+@datapath+'\'+ @lgfilename +CHAR(39)

      set  @i = @i + 1
      fetch next from bak_cur into @lgname,@lgfilename,@lgtype
    end
    close bak_cur
    deallocate bak_cur

    ----------------------------
    print @execsql
    exec (@execsql)
    delete from  @fileListTable
    fetch next from file_cur into @fileName
  end
  close file_cur
  deallocate file_cur
end

# 自动备份所有sqlserver数据库脚本

使用msde/sqlserver express版本没有带代理服务可以通过计划任务定时运行

备份全部数据库的sql,逻辑是通过SYSDATABASES获取全部数据库名写入游标，循环执行备份到文件SQL脚本

use master
declare @DbName varchar(60)
declare @BackSql varchar(1000)
DECLARE @backupTime DATETIME = CURRENT_TIMESTAMP
declare myCursor cursor for
SELECT [name] FROM SYSDATABASES
where [name] not in ('master','model','msdb','tempdb')
order by [name]
open myCursor
fetch next from myCursor into @DbName
while(@@FETCH_STATUS = 0)
begin

select @BackSql='Backup DATABASE ['+@DbName+'] to disk=''F:\YYBAK\'+@DbName+'-'+CONVERT(VARCHAR(25), @backupTime, 112) +'.bak'' with format'

exec(@BackSql)
fetch next from myCursor into @DbName
end
close myCursor
DEALLOCATE myCursor

转成powershell脚本在定时任务中运行

-S后面是数据库服务器地址，非默认实例则加上 \实例名，-P是密码

sqlcmd -S localhost -U sa -P password -i ./bak.sql -o ./bak.log

备份/ 打包/ 清除

sqlcmd -S localhost -U sa -P CHWIT+sasa,. -i ./YXKbak.sql -o ./bak.log

set zip=C:\Program Files\7-Zip\7z.exe
set timestamp=%date:~0,10%
set dir=E:\temp\

echo "%zip%"
echo "%dir%"
echo "%timestamp%"
::explorer "%arc%\.."

"%zip%" a E:\chwRecycle\ZHGY_YXK_%timestamp%.7z "%dir%"


forfiles /p "E:\\temp" /s /m *.* /d -2 /c "cmd /c del @path" 
forfiles /p "E:\\chwRecycle" /s /m *.* /d -5 /c "cmd /c del @path"

# Oralce删除前备份触发器应用

应用背景: 客户质管部门反馈系统中客户设置了证照的经验范围经常会无故消失，因是接收的维护项目。不了解具体是哪个服务做的删除操作，通过触发器记录删除记录情况

按原表结构创建备份表

create table  gsp_company_managerage_bak as 
select * from  gsp_company_managerage where   1=2;

备份表增加删除时间列

ALTER TABLE gsp_company_managerage_bak ADD deltime DATE;

创建删除后写入备份表触发器

create or replace trigger TRG_GSP_LICENSE_DEL_EVAN
after delete
on gsp_company_managerage
for each row
declare
begin
insert into gsp_company_managerage_bak values(:old.SEQID, :old.COMPANYID,:old.SCOPEDEFID,:old.LICENSEID,:old.MEDICINETYPE,SYSDATE);
end
;

← DAMA数据治理学习笔记常用工具集 →

# 数据库笔记

# oracle表空间相关操作

# 查看表空间的名称及大小

# 查看表空间物理文件的名称及大小

# 查看回滚段名称及大小

# 查看控制文件

# 查看日志文件

# 查看表空间的使用情况

# 查看数据库库对象

# 查看数据库的版本

# 查看数据库的创建日期和归档方式

# 修改表空间数据文件的大小

# 在表空间新增加数据文件

# 重新查询

# Oracle中创建用户和授权

# orache创建用户：

# 分配权限：

# 常用查询

# Oracle查询锁表和解锁

# 快速查询和kill

# Oracle dblink应用

# 查看dblink

# 创建dblink

# 使用db link

# 删除dblink

# Oracle循环语句参考示例

# Oracle job应用

# 创建Oracle Job

# 常用的定时时间

# SQLserver查询锁表和解锁

# Oracle截取字符串

# SUBSTR（string,start_position,[length]） 求子字符串，返回字符串

# 示例

# INSTR（string,subString,position,ocurrence）查找字符串位置

# 示例

# Oracle中exp,imp的使用详解

# EXP:

# IMP:

# EXP导出实例

# IMP导入实例

# Oracle ADG主从同步 dataguard(windows to linux )

# 前言

# 环境准备

# 这是我主库的 ip和端口，主库取名为 MAIN，要连接的数据库名为 orcl

# 这是要通信的备库IP 和端口，取名为 BACK，要连接的数据库名为 orcl

# 主库参数配置

# 备库配置

# 参数检查

# 使用duplicate创建物理standby

# 检查是否执行成功

# 主库

# 备库

# Oracle中创建unique唯一约束(单列和多列)

# Oracle自动备份脚本

# Oracle查找重复记录

# 查找重复记录

# 删除重复记录

# Oracle 汉字转换首字母函数

# Oracle创建自增序列触发器

# Oracle密码过期处理

# Oracle数据泵(impdp,expdp)导出导入

# Oracle常用正则表达式

# 示例

# Oracle复制表

# Oracle 常用函数（待补充）

# 时间日期函数

# 字符串函数

# 统计函数

# 分组排序判断函数

# 高级函数(over等)

# MySQL事件创建和修改（类似Oracle job)

# 更改事件ALTER EVENT语句，如下所示：

# 创建事件，每分钟向messages表中插入一条新记录。

# 禁用事件

# oracle系统表v$session、v$sql 查看最近执行了哪些sql语句

# session示例：

# V$SQLTEXT中的常用列

# 执行sql查询示例

# 安装MSDE停到最后几秒不动

# 方法一

# SUBSTR（string,start_position,[length]）求子字符串，返回字符串