查询表的信息(表、字段、约束、索引)
1、查询出所有的用户表
select * from user_tables 可以查询出所有的用户表
2、查询出用户所有表的索引
select * from user_indexes
3、查询用户表的索引(非聚集索引):
select * from user_indexes where uniqueness=’NONUNIQUE’
4、查询用户表的主键(聚集索引):
select * from user_indexes where uniqueness=’UNIQUE’
5、查询表的索引
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name=’NODE’
6、查询表的主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘P’ AND cu.table_name = ‘NODE’
7、查找表的唯一性约束(包括名称,构成列):
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and cu.table_name=’NODE’
8、查找表的外键
select * from user_constraints c where c.constraint_type = ‘R’ and c.table_name=’STAFFPOSITION’
查询外键约束的列名:
select * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名:
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
9、查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name=’NODE’
10、查询服务器时间
Select To_Char (SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) “nowTime” from dual
11、查询所有已执行、未提交的SQL?
select SQL_TEXT,status from v$sql,v$transaction where LAST_ACTIVE_TIME = START_DATE;
存储过程代码
循环、判断、游标
--处理思路:
----1)遍历所有SLBD债券;
----2)检查包含该债券的设置的行数(=CT_BOND)是否等于所有SLBD组合的数量(=CT_PORT),如果等于则跳过,如果小于则进入第3)步;
----3)遍历所有SLBD组合,如果该组合与该债券查不到设置(CT_EXIST=0)则执行INSERT,否则跳过。
/* 1 - parametre_champ_traitement values ('MTFDVA','SLBD',trim(csf_fund_id),csa_asset_id,' ',' ','O',' ',' ',0,0); */
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';
set serveroutput on;
set serveroutput on size;
declare
cursor c_slbd_fund is
select code_valeur from liste_tris
where code_ensemble_val = 'MAJPOE' and categorie_valeur = 'PORT' and type_tri = 'SLBD' and critere_tri = 'O' ;
cursor c_slbd_asset is
select code_valeur from liste_tris
where code_ensemble_val = 'TKAS' and categorie_valeur = 'VMOB' and type_tri = 'SLBD' and critere_tri = 'O' ;
csf_fund_id liste_tris.code_valeur%type; -- c_slbd_fund
csa_asset_id liste_tris.code_valeur%type;-- c_slbd_asset
N INTEGER :=0;--count the line
N1 INTEGER :=0;--counter
CT_PORT INTEGER; --COUNTER,所有是SLBD的组合的数量
CT_BOND INTEGER;--COUNTER,所有包含该债券的设置的行数
CT_EXIST INTEGER;--COUNTER,看查询结果是否为0
begin
DBMS_OUTPUT.PUT_LINE('----------start----------');
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
SELECT COUNT(CODE_VALEUR) INTO CT_PORT FROM LISTE_TRIS
where code_ensemble_val = 'MAJPOE' and categorie_valeur = 'PORT' and type_tri = 'SLBD' and critere_tri = 'O' ;
open c_slbd_asset;--债券多,所以在第一层循环
loop
fetch c_slbd_asset into csa_asset_id;
exit when c_slbd_asset%notfound;
/*检查包含该债券的设置的行数(=CT_BOND)是否等于所有是SLBD的组合的数量(=CT_PORT),小于则开始后面的循环*/
SELECT COUNT(CODE_TRAITEMENT) INTO CT_BOND FROM parametre_champ_traitement
WHERE CODE_TRAITEMENT='MTFDVA' AND CRITERE_SAISIE_1='SLBD' AND NOM_CHAMP=csa_asset_id;
IF CT_BOND<>CT_PORT THEN
open c_slbd_fund;--组合少,所以在第二层
loop
fetch c_slbd_fund into csf_fund_id;
exit when c_slbd_fund%notfound;
SELECT COUNT(CODE_TRAITEMENT) INTO CT_EXIST FROM parametre_champ_traitement
WHERE CODE_TRAITEMENT='MTFDVA' AND CRITERE_SAISIE_1='SLBD' AND CRITERE_SAISIE_2=csf_fund_id AND NOM_CHAMP=csa_asset_id;
IF CT_EXIST=0 THEN
insert into parametre_champ_traitement values ('MTFDVA','SLBD',trim(csf_fund_id),csa_asset_id,' ',' ','O',' ',' ',0,0);
N:=N+1;
END IF;
end loop;
close c_slbd_fund;
END IF;
end loop;
close c_slbd_asset;
DBMS_OUTPUT.PUT_LINE('----------how many lines inserted----------');
DBMS_OUTPUT.PUT_LINE (N);
DBMS_OUTPUT.PUT_LINE('----------end----------');
DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
导出文本文件、CASE WHEN
/*
1.去掉字段中的回车、换行等字符
replace(replace(replace(INSURANTNAME,chr(9),''),chr(10),''),chr(13),'')
2.每月执行
to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')
3.分隔符 ||'","'|| ,最后一个为 ||'"'
*/
#SQL 导出CSV
set echo off
set feedback off
set newpage none
set verify off
set pagesize 0
set term off
set trims on
set linesize 2000
set heading off
set timing off
set numwidth 38
SPOOL REQJTSJGL-201202-3054-1.csv
SELECT '系统名称、账号、权限、账号状态、员工姓名、员工号、公司名称、最后修改时间' from dual;
SELECT
'集团2015版估值与投资会计核算系统' 系统名称,
OIU.OSE_CODE_UTILISATEUR 账号,
OUTT.OSE_NOM_UTILISATEUR 权限,
'有效' 账号状态,
OIU.OSE_NOM_UTILISATEUR 员工姓名,
' ' as 员工号,
CASE WHEN OIU.OSE_SOCIETE LIKE '%寿险%' THEN '寿险总公司'
WHEN OIU.OSE_SOCIETE LIKE '%产险%' THEN '产险总公司'
WHEN OIU.OSE_SOCIETE LIKE '%AMC%' THEN '资管公司'
WHEN OIU.OSE_SOCIETE LIKE '%资管%' THEN '资管公司'
WHEN OIU.OSE_SOCIETE LIKE '%健康险%' THEN '健康险总公司'
WHEN OIU.OSE_SOCIETE LIKE '%财务共享中心 %' THEN '集团'
ELSE '集团' END 公司名称,
TO_CHAR(TL.DATE_,'YYYY-MM-DD') 最后修改时间
FROM
GP3.OSE_INFO_UTILISATEUR OIU, GP3.DESCRIPTIF_UTILISATEUR DU, GP3.OSE_UTILISATEUR_TYPE OUTT , GP3.OSE_UTILISATEUR ou, GP3.TABLE_LOGBOOK TL, webintake.gp3_logins gl
WHERE
OIU.OSE_CODE_UTILISATEUR = DU.ID_USERNAME
AND ou.OSE_CODE_UTILISATEUR = oiu.OSE_CODE_UTILISATEUR
AND (OIU.OSE_DATE_DEVALIDATION >= DATE '2000-1-1' OR OIU.OSE_DATE_DEVALIDATION=DATE '1752-9-14')
AND DU.CODE_UTILISATEUR_TYPE = OUTT.OSE_CODE_UTILISATEUR_TYPE
AND TL.CRITERE_TRI = OIU.OSE_CODE_UTILISATEUR
AND TL.TYPE_DATE='SYS'
AND TL.TYPE_TRI='USER'
AND TL.CODE_TRAITEMENT='MAJUTI'
AND TL.DATE_ BETWEEN DATE'2000-1-1' AND DATE'2020-12-31'
AND trim(gl.LOGIN) = trim(ou.OSE_USER_NAME)
and gl.INACTIVE = -1
and lower(gl.login) not in ('pamc','gp3adm','gpadmin','fndsoft','tester','debug');
SPOOL OFF
SPOOL REQJTSJGL-201202-3054-2.csv
SELECT '系统名称、账号、权限、账号状态、员工姓名、员工号、公司名称、变更时间、变更内容' from dual;
SELECT
'集团2015版估值与投资会计核算系统' 系统名称,
OIU.OSE_CODE_UTILISATEUR 账号,
OUTT.OSE_NOM_UTILISATEUR 权限,
'有效' 账号状态,
OIU.OSE_NOM_UTILISATEUR 员工姓名,
' ' as 员工号,
CASE WHEN OIU.OSE_SOCIETE LIKE '%寿险%' THEN '寿险总公司'
WHEN OIU.OSE_SOCIETE LIKE '%产险%' THEN '产险总公司'
WHEN OIU.OSE_SOCIETE LIKE '%AMC%' THEN '资管公司'
WHEN OIU.OSE_SOCIETE LIKE '%资管%' THEN '资管公司'
WHEN OIU.OSE_SOCIETE LIKE '%健康险%' THEN '健康险总公司'
WHEN OIU.OSE_SOCIETE LIKE '%财务共享中心 %' THEN '集团'
ELSE '集团' END 公司名称,
TO_CHAR(TL.DATE_,'YYYY-MM-DD') 变更时间,
'原有的概要 : 新的概要 : '||TL.HEURE_SAISIE||')' 变更内容
FROM
GP3.OSE_INFO_UTILISATEUR OIU, GP3.DESCRIPTIF_UTILISATEUR DU, GP3.OSE_UTILISATEUR_TYPE OUTT , GP3.OSE_UTILISATEUR ou, GP3.TABLE_LOGBOOK TL, webintake.gp3_logins gl
WHERE
OIU.OSE_CODE_UTILISATEUR = DU.ID_USERNAME
AND ou.OSE_CODE_UTILISATEUR = oiu.OSE_CODE_UTILISATEUR
AND (OIU.OSE_DATE_DEVALIDATION >= DATE '2000-1-1' OR OIU.OSE_DATE_DEVALIDATION=DATE '1752-9-14')
AND DU.CODE_UTILISATEUR_TYPE = OUTT.OSE_CODE_UTILISATEUR_TYPE
AND TL.CRITERE_TRI = OIU.OSE_CODE_UTILISATEUR
AND TL.TYPE_DATE='SYS'
AND TL.TYPE_TRI='USER'
AND TL.CODE_TRAITEMENT='MAJUTI'
AND TL.DATE_ BETWEEN DATE'2020-1-1' AND DATE'2020-12-31'
AND trim(gl.LOGIN) = trim(ou.OSE_USER_NAME)
and gl.INACTIVE = -1
and lower(gl.login) not in ('pamc','gp3adm','gpadmin','fndsoft','tester','debug');
SPOOL OFF
EXIT
修改表空间名称
1、使用oracle用户登录执行。
$sqlplus / as sysdba
2、执行修改表空间命令如下。
SQL> alter tablespace TEST rename to TEST1;
注:可连续对多个表空间进行重命名
3、确认表空间名已经修改。
SQL>select name from v$tablespace;
SQL>exit
在命令行使用exp导出dump文件
exp customerchat/customerchat@localhost/orcl file=d:\dd.dump log=d:dd.log full=y
加上full=y会导出整个数据库所有用户的数据,不加只会导出当前用户的数据。
在命令行使用exp导入dump文件
imp username/password@localhost:10000/SID file=/database/app/oracle/oradata/ dd.dump log=/database/app/oracle/oradata /dd.log fromuser=user1 touser=user2
表和表结构从Oracle迁移到MySQL
1、需要到的工具
1、Navicat Primium
下载网址,https://www.navicat.com.cn/download 是官方网站。 (这是个收费软件,不过能免费试用 15 天,这足够我们去完成数据表的迁移了,所以不必在意。 )
2、建立连接时需要到的文件 oci.dll 和 sqlplus.exe(sqlplus)
2、操作过程
2.1 创建连接并连接 oracle
下载安装好 Navicat Primium,点击安装文件,每一步都按照原来的选择,一路 NEXT 就好。安装后之后,打开软件,主界面如下:
在主界面上的选项卡上,点击“工具”——>“选项”,之后就可以看到如下的界面:
在弹出框的左侧,点击“其他”——>“OCI”,之后按照图上右边的操作,选择之前准备好的 oci.dll 文件、sqlplus.exe 文件。接下来,就可以创建连接了。
创建数据库连接。点击左上角的“连接”——> 选择 Oracle 然后就可以看到如下截图:
之后按照上面的提示填入相关的信息(可以参考你需要连接的oracle 的连接信息),
连接类型:选择 basic。
主机名或 IP 地址:填 oracle 数据库服务器所在环境的 IP 地址。
用户名和密码:填需要导出的表所在的用户的信息。
连接后之后,点击“连接测试”
确定连接后,你就可以看到你连接到的用户名的信息了
到此,就完成了 oracle 数据库的连接了。
2.2 将数据表从 oracle 传输到 MySQL
完成 2.1 后,在主界面上的选择栏中,点击“工具”——>“数据传输”,之后就可以看到如下界面:
选择“常规”,在数据库对象下,选择需要传输的表;
在最右边目标栏中,选中“连接”,在下拉中选中需要转入的数据库连接,在“数据库”,选择要将数据导入到的数据库:
最后我本次操作完如下:(参考而已)
到此填完了“常规”选项的,接着,点击“高级”,填写(勾选)的信息如下
完成后,点击右下方的“开始”按钮,会弹出如下提示:
点击确定,当运行完成后,它会给出提示
看上面提示信息的最后一行,就可以看出是否成功,上面的图展示的信息明显是成功的,可以看到最后一行的信息是“Finished Successfully” 最后,你就可以到数据表导入到的新地方查看了,查看数据表是否全部迁移到,数据是否正确。祝你好运!!!
————————————————
版权声明:本文为CSDN博主「暂无方向的程序猿2020」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/quf2zy/article/details/79089646