Orcale的简单使用

1、Orcale中sql的使用

• 查询UACSAPP.MYTEST表中1天前的数据，只显示前三条

  select  * FROM UACSAPP.MYTEST WHERE TIME < SYSDATE - 1 and ROWNUM<3;
  

• 数据库连接数相关查询

  select count(*) as newsum from v$process ;    --当前的数据库连接数
  select value from v$parameter where name ='processes';  --数据库允许的最大连接数
  select count(*) as sunse from v$session;   --当前的session连接数
  select count(*) as bingfa from v$session where status='ACTIVE';   --并发连接数
  

• 基础增删改查语句

  insert into tablename values(3,3,3,3,NOW());
  delete from tablename where carid=3
  update tablename set carnum=4 where carid=3
  select * from tablename
  

• sql查询两条记录的时间差（笛卡尔积）

  参考链接：https://www.cnblogs.com/wangxinblog/p/7835010.html

• 删除表中主键约束语句格式: alter table 表名 drop CONSTRAINT 主键约束名;(主键约束名在Constraints中查看，不成功则给约束名加“”)

• 添加主键约束语句格式： alter table 表名 add CONSTRAINT 主键约束名 primary KEY (主键字段);（不成功则给约束名加“”)

• 其他

  like '%%'看起来好像是全查数据，实则是除了null值的所有数据，nvl(字段,替换的值)，以下sql解决这个问题
  SELECT * FROM table WHERE nvl(CRANE_NO,' ') like '%%'
  注意上面sql的nvl(CRANE_NO,' ') like '%%'，查询时遇到CRANE_NO字段的值为null时，替换成' '，注意两个单引号之间是有空格的，或者可以替换成自己想要的字符
  

  参考链接：https://www.cnblogs.com/pzw23/p/13973429.html

2、Oracle 解除密码180天密码过期的方法

1. 进入sqlplus模式（客户端sql执行也可以，一般执行2和3即可）

   sqlplus / as sysdba;
   

2. 查看用户密码的有效期设置(一般默认的配置文件是DEFAULT,LIMIT的参数默认是180)

   SELECT * FROM dba_profiles WHERE profile=’DEFAULT’ AND resource_name=’PASSWORD_LIFE_TIME’;
   

3. 将密码有效期由默认的180天修改成“无限制”，修改之后会立即生效（LIMIT字段改为UNLIMITED）

   ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
   

4. 帐户再改一次密码

   alter user userName identified by oldPwd;
   

5. 修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；已经被提示的帐户必须再改一次密码（user 是用户名，password是密码）

    $sqlplus / as sysdba
       sql> alter user smsc identified by password；//password可以写原来的原来的密码 ----不用换新密码
   

6. 连接密码错误次数过多时，会被锁定，需要执行以下两句（user 是用户名）

   alter user user account unlock;
       commit;
   

3、定时清除表数据（1分钟）

create or replace procedure PROCE_text1 is
V_SQL VARCHAR2(100);
begin
V_SQL := 'truncate table WRH$_ACTIVE_SESSION_HISTORY';
  execute immediate V_SQL;
commit;
end PROCE_text1;

VARIABLE jobno number;
begin
    DBMS_JOB.SUBMIT(
        :jobno,
       'PROCE_text1;',
        Sysdate,
        'sysdate+1/24'
    );
commit;
end;
select job, next_date, next_sec, failures, broken from user_jobs;         --查询是否创建成功
select * from v$parameter where name='job_queue_processes';        --查询是否创建成功

4、Orcale触发器的使用

1. • 删除名为a_delete的触发器：drop trigger a_delete;
   • 查询所有已经建好的序列：select * from user_sequences;
   • 删除序列：DROP SEQUENCE seq_TEST;
   • 新建触发器时，after不好使，就用before

2. 触发器的sql写完后需要进行【编译】，【编译】后才能生效！！！ 表名、字段名有时候（DBever中需要）需要“”才能生效，例如"SIGANG".a_delete。

3. update 创建触发器，SIGANG.DBTEST 表随着SIGANG.DBLINKTEST表数据的更改而更改

   //（tig_test是触发器名称，当前实例意思：当DBLINKTEST表的age改变时，更改DBTEST中【name与DBLINKTEST当前改变行name一致】的那一行的age）
   create or replace trigger tig_test
   after update
   on SIGANG.DBLINKTEST
   for each row
   begin
   update SIGANG.DBTEST set age=:new.age where name=:new.name;
   end;
   

4. delete 创建触发器，SIGANG.DBTEST 表随着SIGANG.DBLINKTEST表数据的删除而删除

   //（tig_test2是触发器名称，当前实例意思：当DBLINKTEST表的某一行删除时，删除DBTEST中【name与DBLINKTEST当前删除行name一致】的那一行数据）
   create or replace trigger tig_test2
   after delete on SIGANG.DBLINKTEST
   for each row
   declare
   begin
   delete from SIGANG.DBTEST where SIGANG.DBTEST.name=:old.name;
   end tig_test2;
   

5. insert 创建触发器，SIGANG.DBTEST 表随着SIGANG.DBLINKTEST表数据的新增而新增

   //（tig_test2是触发器名称，当前实例意思：当DBLINKTEST表的新增一行时，DBTEST中也新增的一行数据，填充name和age的值，其他为空）
   create or replace trigger tig_test3
   before insert on SIGANG.DBLINKTEST
   for each row
   declare
   begin
   insert into SIGANG.DBTEST(name,age) values(:new.name,:new.age);
   end tig_test3;
   

6. （在新增数据时）删除表【 SIGANG.作业日志】3天前的所以数据，写入时间是表中的字段，tig_test3是触发器名字

   create or replace trigger tig_test3
   after insert on SIGANG.作业日志
   for each row
   declare
   begin
   DELETE FROM sigang.作业日志 WHERE 写入时间 < SYSDATE - 3;
   end tig_test3;
   

7. 变量定义与使用，取【SQ作业顺序号.nextval】赋值给sn，删除【作业记录】中的作业顺序号为sn的数据

   CREATE OR REPLACE TRIGGER "SIGANG".作业记录_b_insert
   before insert on 作业记录 for each row
   declare
     sn   number(8);
   begin
     select SQ作业顺序号.nextval into sn from dual;
     delete from 作业记录 where 作业顺序号=sn;
     :new.作业顺序号 := sn;
     --select 作业顺序号.nextval into :new.作业顺序号 from dual;
   end;
   

8. 周一至周五9——18点能修改表格

   CREATE OR REPLACE TRIGGER Test2
   BEFORE INSERT OR DELETE OR UPDATE ON SIGANG."MyTest"
   FOR EACH ROW
   BEGIN
      IF(TO_CHAR(SYSDATE,'DAY') IN ('星期六','星期天'))
      OR(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '9:00' AND '18:00')
      THEN RAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest');
    -- update SIGANG."MyTest" set AGE = '22222' where name = '66';--也可反向写：去掉not，在then后面，写自己的sql
      END IF;
      END;
   

9. 新增数据时，删除指定表中，30天之前的数据，一次删除10000条

   CREATE OR REPLACE TRIGGER DeleteHis1
   after insert on UACSAPP.UACS_CRANE_SCHEDULE_ORDER
   for each row
   declare
   begin
   DELETE FROM UACSAPP.UACS_CRANE_SCHEDULE_ORDER WHERE REC_TIME < SYSDATE - 30 and rownum <= 10000;
   end DeleteHis1;
   commit;
   

10. 触发器主键自增（ 如果没有对WHEN (new.nid is null)的判断，则不管有没有指定nid都会被替换为序列的值）

    //创建序列，seq_test是序列名称
    CREATE SEQUENCE seq_test
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXvalue -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    CACHE 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
      
    CREATE OR REPLACE TRIGGER tg_test
    BEFORE INSERT ON test FOR EACH ROW WHEN (new.nid is null)
    begin
    select seq_test.nextval into:new.ID from UACSAPP.TEST WHERE rownum=1;
    end;
    

5、Orcale被锁表的处理办法

1、登录数据库SYS用户
2、执行下面sql
select session_id from v$locked_object;--1、查询被锁的会话ID
SELECT sid, serial#, username, osuser FROM v$session where sid = 2;--2、查询上面会话的详细信息：
ALTER SYSTEM KILL SESSION 'SID,SERIAL';--3、将上面锁定的会话关闭：SID,SERIAL替换为上一句sql查询出来的数据
3、另一种处理sql
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;   --查询锁表的数据                                                                                  
 alter system kill session '213,21';--解除表的锁，213,21替换为上一句sql查询出来的
 alter system kill session '135,50116';--解除表的锁
 alter system kill session '79,3857';--解除表的锁
 commit;

6、报错：ORA-00823，导致ORCALE数据库无法启动

例如执行了以下两句
alter system set sga_max_size=1000M scope=spfile;
alter system set sga_target=10000M scope=spfile;
原因：sga_max_size参数值<sga_targe参数值
解决办法：
1、登录
/ as sysdba
2、查看sga参数
show parameter sga;
3、创建pfile文件（路径为ORCALE安装路径，寻找spfile.ora文件）
create  pfile from spfile='D:\app\root\virtual\product\12.2.0\dbhome_1\database\SPFILEUACS.ORA';
4、修改刚创建的文件参数，sga_target的数值要小于sga_max_size的数值
5、使用pfile文件启动（INITUACS.ORA是新的pfile文件名称）
startup nomount pfile='D:\app\root\virtual\product\12.2.0\dbhome_1\database\INITUACS.ORA';
6、关闭数据库
shutdown immediate;
7、启动数据库
startup;

7、Oracel12c的安装方法

1. 创建用户与数据库：进入到SQL Plus，在【请输入用户名：】后面输入：sys/manager as sysdba
2. 创建用户的操作：create user uacsapp identified by uacsapp11;
3. uacsapp是用户名，uacsapp11是密码，以下出现的uacsapp都是用户名
4. 为刚创建的用户解锁：alter user uacsapp account unlock;
5. 授予新用户创建权限：grant create session to uacsapp ;
6. 授予新用户数据库管理员权限：grant dba to uacsapp ;
7. 管理员权限视情况给授予用户其它权限： 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 uacsapp ;
8. 用户就创建成功了，到https://localhost:5500/em登录试试，登录成功。不过不能以管理员权限登录。使用Oracel12c自带的SQL Developer进行数据库连接测试、或简单使用

​ Oracel12c的安装方法参考链接：https://jingyan.baidu.com/article/b87fe19e51b95e521835689f.htmlOracel12c

​ 创建数据库的方法参考链接：https://blog.csdn.net/carrotluotian/article/details/102571786

8、Orcale中DBlink的使用方法

服务端Orcale访问客户端Orcale数据库（一般走步骤123即可）：

1. 在服务端数据库tnsnames.ora文件中添加（133.3.3.247是客户端IP，slabyard是客户端数据库名称。WMS类似与别名，与DClink命令相对应）

   WMS =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 133.3.3.247)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = slabyard)
       )
     )
   

2. 在服务端Orcale执行sql建立DBlik（WMSlink是DBlike名称，sigang是客户端数据库用户名，software是客户端数据库密码，WMS与配置文件中相对应）：

   create public database link WMSlink connect to sigang identified by "software" USING 'WMS'
   

3. 执行查询语句（MYLINK是要查询的客户端的数据库的表名称，WMS是DBlink的名称）：

   select * from MYLINK@WMSlink
   

4. 如果创建dblink的用户没有对应的数据库权限，通过以下sql授权（sigang是用户名）：

   grant create public database link,create database link to sigang；
   

5. 删除dblink的sql：

   drop public database link WMSlink;
   

6. 查询已存在的DBlink的sql：

   select * from dba_db_links;
   

9、主键恢复从0开始

​ （2~4依次执行才能生效）

1. Oracle中一般将自增sequence重置为初始1时，都是删除再重建，这种方式有很多弊端，依赖它的函数和存储过程将失效， 需要重新编译。不过还有种巧妙的方式，不用删除，利用步长参数，先查出sequence的nextval，记住，把递增改为负的这个值（反过来走），然后再改回来。假设需要修改的序列名：seq_name
2. select sequence_owner,sequence_name from dba_sequences where sequence_owner=‘UACSAPP’ --查询UACSAPP数据库中存在的序列，找到自己需要修改的，替换后面的seq_name
3. alter sequence UACSAPP.FIND_SADDLE_STRATEGY_SEQ INCREMENT BY -9 --让序列号减少9，9=当前最大ID-1
4. SELECT UACSAPP.FIND_SADDLE_STRATEGY_SEQ.NEXTVAL FROM DUAL --查询下一个序列号
5. alter sequence UACSAPP.FIND_SADDLE_STRATEGY_SEQ INCREMENT BY 1 --让序列号从1开始

10、win下Oracle完全卸载详解

1. 停止使用Oracle的服务：停用oracle服务，进入计算机管理，在服务中，找到oracle开头的所有服务，右击选择停止
2. 运行卸载Oracle数据库程序：一般情况运行Oracle自带的卸载程序，如使用 Universal Installer 工具卸载。
3. 删除使用Oracle的服务：开始菜单中，找到Universal Installer，运行Oracle Universal Installer，单击卸载产品，在产品清单窗口中，单击全部展开，除了OraDb11g_home1外，勾选其他项目，单击删除，根据软件提示单击下一步最终完成卸载。
4. 删除注册表中Oracle相关项：在命令窗口，输入regedit，打开注册表，依次展开HKEY_LOCAL_MACHINE\SOFTWARE，找到oracle，删除之。依次展开HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services中，删除所有oracle开头的项。依次展开HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，删除所有oracle开头的项；扩展删除（以下不是必须的注册表删除项），如果安装不成功可以自己也把这些删除了在HKEY_CLASSES_ROOT，删除以Ora、Oracle、Orcl或EnumOra为前缀的键删除HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs中所有以oracle开头的键。删除HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI中除Microsoft ODBC for Oracle注册表键以外的所有含有Oracle的键。
5. 删除Oracle环境变量：右键点击我的电脑–>属性–>高级–>环境变量,删除环境变量ORACLE_HOME、TNS_ADMIN等环境变量，删除PATH中等环境变量有关Oracle的设定的路径信息
6. 删除“开始”菜单中Oracle目录:打开资源管理器，在地址栏中输入“%userprofile%\「开始」菜单\程序”回车，删除安装的Oracle目录然后再到地址栏中输入“%allusersprofile%\「开始」菜单\程序”回车，删除安装的Oracle目录
7. 重新启动计算机:重启电脑，我就不说了吧。
8. 删除Program Files\Oracle目录：如果在Program Files\Oracle目录存在，则删除Program Files\Oracle目录。
9. 删除Oracle安装目录:删除Oracle的安装目录app等目录。
10. 以上参考出处：http://jingyan.baidu.com/article/922554468d4e6b851648f4e3.htmlhttp://blog.itpub.net/30029358/viewspace-1341890/

11、oracle中向timeStamp类型字段插入当前时间

​

UPDATE  WMS_CRANE_ORDER SET CRANE_NO = '1_1' ,CMD_STATUS = 'A',UPD_TIME = to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
 WHERE FROM_STOCK_NO = 'Z62A3-02-1'

12、oracle查询00：00到8：00的所有数据，不管日期

SELECT * FROM UACS_YLK_STATEMENT
WHERE to_char(SAVE_TIME, 'hh24') >= 0 and to_char(SAVE_TIME, 'hh24') < 8
hh24修改为yyyy，即是查询年的时间段的数据

13、oracle配置Net Configu Assistant

1. 安装orcale，只用于连接其他服务器orcale则仅【安装数据库软件】即可，参考链接：https://jingyan.baidu.com/article/b87fe19e51b95e521835689f.html
2. 打开Net Configu Assistant，本地网络服务名配置 -》 添加 -》slabyard（数据库名称）-》TCP-》（主机名）10.69.4.4-》是，进行测试-》更改登录-》用户名sigang密码software 。测试成功就行。

14、视图

//创建触发器
create or replace trigger UACS_YARDMAP_STOCK_DEFINE_TAG
BEFORE UPDATE on UACS_YARDMAP_STOCK_DEFINE
  FOR EACH ROW
  BEGIN
 IF :OLD.STOCK_STATUS ='0' AND :NEW.STOCK_STATUS='2' THEN :NEW.LAST_CHANGE_TIME := SYSDATE;
 ELSIF :OLD.STOCK_STATUS ='2' AND :NEW.STOCK_STATUS='0' THEN :NEW.LAST_CHANGE_TIME := ''; 
END IF;
END; 
//创建物化视图
create materialized view  "UACSAPP"."V_YARDCOIL_STRAND_TIME" ("BAY_NO","STOCK_NO", "MAT_NO", "IN_TIME","CURRENTTIME","STRAND_TIME") refresh force on demand start with sysdate next SYSDATE + 5/1440 AS
SELECT BAY_NO ,STOCK_NO ,MAT_NO ,IN_TIME,CURRENTTIME, ROUND(TO_NUMBER(CURRENTTIME-IN_TIME),1) AS STRAND_TIME FROM
  (SELECT BAY_NO ,STOCK_NO ,MAT_NO,to_date(to_char(sysdate,'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss') AS CURRENTTIME,to_date(to_char(LAST_CHANGE_TIME,'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss') AS IN_TIME
      FROM UACS_YARDMAP_STOCK_DEFINE WHERE STOCK_TYPE IN('0','4','5') AND STOCK_STATUS ='2')
      ORDER BY DECODE(BAY_NO,'CP3',1,'QZR',2,'DCL',3,'ROF',4,'QCS',5),STOCK_NO ASC;
 //删除物化视图
drop materialized view V_YARDCOIL_STRAND_TIME;