oracle及pl/sql常用记录
1. 复制表结构及其数据： create table table_name_new as select * from table_name_old 2. 只复制表结构： create table table_name_new as select * from table_name_old where 1=2; 或者： create table table_name_new like table_name_old 3. 只复制表数据： 如果两个表结构一样： insert into table_name_new select * from table_name_old 如果两个表结构不一样： insert into table_name_new(column1,column2...) select column1,column2... from table_name_old 用truncate、delete都可以清空表中数据。
SQL如下：
truncate table [表名];
delete from [表名];
delete与truncate的区别：
delete：会产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments。
truncate： 是DDL操作，不产生rollback，速度快。
三、PL/SQL的文本导入工具
工具---文本导入工具，可导入txt，csv等。
文本内第一行写标题，最好和表相对应，工具会自动识别。详细内容
四、pl/sql执行一些耗时的操作时，可能会卡住，鼠标会一直转圈，无法得知进度，此时可以另开了个pl/sql，用sql去查看表的状态来判断执行进度。
比如一次性导入几十万数据的操作，可能需要几十分钟分钟执行，期间你不知道是卡住了还是正常执行，另起一个pl/sql去count一下数据量，做个简单的判断。
五、根据rowid删除重复记录
delete from table_name t1 where rowid not in( select min(rowid) from table_name t2 where t2.id is not null having count(id) >1 group by t2.id)
六、decode使用 连接符号“||”字段拼接
select t.*, h.area_code, a.area_name, DECODE(h.verify_code,1,'部分缴费',2,'全部缴费',3,'欠费多缴',4,'不欠费预存') from ZFBPOS_HN_SERIAL t,SERIAL_HISTORY_2018 h,AREA_REF a where 'ALI'||t.ali_serial_no=h.old_serial_no and h.area_code=a.area_id and to_char(h.tran_date,'yyyymm')<'201806';
七、windows下sqlplus管理员无密码登录数据库
首先，开始菜单，cmd，弄出来黑色窗口
sqlplus /nolog connect /as sysdba //赋给用户连接权限 grant create session to username;
八、oracle连接数
在查看数据的连接情况很有用，写完程序一边测试代码一边查看数据库连接的释放情况有助于分析优化出一个健壮的系统程序来。
1、查看当前的数据库连接数
select count(*) from v$process ; --当前的数据库连接数
2、数据库允许的最大连接数
select value from v$parameter where name ='processes'; --数据库允许的最大连接数
3、修改数据库最大连接数
alter system set processes = 300 scope = spfile; --修改最大连接数:
4、关闭/重启数据库
shutdown immediate; --关闭数据库
startup; --重启数据库
5、查看当前有哪些用户正在使用数据
select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address
order by cpu_time/executions desc; --查看当前有哪些用户正在使用数据
6、 --当前的session连接数
select count(*) from v$session --当前的session连接数
7、当前并发连接数
select count(*) from v$session where status='ACTIVE'; --并发连接数
v$process：
这个视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息
服务进程分两类，一是后台的，一是dedicate/shared server
pid, serial# 这是oracle分配的PID
spid 这才是操作系统的pid
program 这是服务进程对应的操作系统进程名
v$session：
这个视图主要提供的是一个数据库connect的信息，
主要是client端的信息，比如以下字段：
machine 在哪台机器上
terminal 使用什么终端
osuser 操作系统用户是谁
program 通过什么客户端程序，比如TOAD
process 操作系统分配给TOAD的进程号
logon_time 在什么时间
username 以什么oracle的帐号登录
command 执行了什么类型的SQL命令
sql_hash_value SQL语句信息
有一些是server端的信息：
paddr 即v$process中的server进程的addr
server 服务器是dedicate/shared
九、Oracle session连接数和inactive的问题记录
十一、筛选统计记录大于2条的数据
select a.c,a.b from (select t.DEVICE_IMEI c,count(*) as b from device_info t GROUP BY t.DEVICE_IMEI ) a where a.b > 1;
十二、查询字段不是纯数字
SELECT * FROM device_info t WHERE LENGTH(0+t.DEVICE_IMEI) != LENGTH(t.DEVICE_IMEI);//这个原理是0+字段后长度等不等于原长度，但是如果首数字是0的话，就不管用了，例如003，原长度3加零之后，长度变为1
SELECT * FROM device_info t WHERE (t.DEVICE_IMEI REGEXP '[^0-9.]')!=0;
十三、统计所有表的数据量
select t.table_name,t.num_rows from user_tables t ORDER BY NUM_ROWS DESC;
十四、有则更新无则插入
MERGE INTO ali_elec_notice_bill_result a USING ( SELECT '3536185368' as calcid FROM dual ) b ON (a.calcid = b.calcid) WHEN MATCHED THEN UPDATE SET a.remark = '已结清清' WHEN NOT MATCHED THEN INSERT (a.CALCID,a.CUST_NO,a.T_PQ,a.RCVBL_AMT,a.RCVBL_YM,a.REMARK) VALUES ('3536185368','5024002955','236','13216','201903','已结清');
MERGE INTO ali_elec_notice_query_result a USING ( SELECT '3140080836' as cust_no,to_date('20190314','yyyyMMdd') as query_date FROM dual ) b ON (a.cust_no = b.cust_no and a.query_date = b.query_date) WHEN MATCHED THEN UPDATE SET a.custname = '111' WHEN NOT MATCHED THEN INSERT (a.CUST_NO,a.QUERY_DATE,a.CUSTNAME,a.ADDRESS,a.PREPAY_BAL,a.EXCHG_AMT,a.BATCH_NO,a.ORG_NO,a.ORG_NAME) VALUES ('3140080836',to_date('20190314','yyyyMMdd'),'11','111',116.12,0.00,'34689105721','41412','111');
十五、查询n到m条数据
SELECT * FROM ( SELECT 表名.*, ROWNUM AS CON FROM 表名 WHERE ROWNUM <= M AND 其它查询条件 ORDER BY 排序条件 ) WHERE CON >=N;
十六、----
