once先森的博客

数据库设计总结

其中replace可以使用regexp_replace替换，加入了正则匹配，使用更加灵活。并且写死的key和value可以通过自定义的type array动态传参，更具灵活性。在前端通过编码配置了公式，数据库可以替换公式占位符后计算结果吗？

declare cursor c1 is select u.usercode,u.logincode from user u where u.valid='1'; n1 number;begin for v1 IN c1 loop update user G set G.Logincode= v1.logincode where g.usercode = v1.usercode; end loop; dbms_output.put_line(n1);...

将Linux下的数据库服务端（reinsure/reinsure@192.168.0.14:WLDB）的数据导出来，然后在导入本地（reinsure/reinsure@192.168.0.31:ZYDEV）。导出数据：在Linux下创建逻辑目录: create or replace directory data_dump_dir as‘/u02/dmp’;用管理员给reinsure用户赋予在指定目录的操作权限： grant read,write on directory data_dump_dir t

SELECT O.OWNER 对象所属者, O.OBJECT_NAME 包名, --O.OBJECT_TYPE , ( CASE WHEN S.PROPERTIES = 27131904 THEN 'FUNCTION' WHEN S.PROPERTIES = 29229056 THEN 'PROCEDURE' ELSE TO_CHAR(S.P...

ALTER USER TEST ACCOUNT UNLOCK

select *from (select sa.SQL_TEXT,sa.SQL_FULLTEXT,sa.EXECUTIONS "执行次数",round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",sa.COMMAND_TYPE,sa.PARSIN...

SELECT REGEXP_REPLACE (字段, '[^0-9]+', '')FROM TEST t ;

Oracle中如果不知道system,sys用户的密码后可用如下方法找回：首先以一个普通用户等入数据库：在SQL*Plus中执行如下命令：SQL>connect/as sysdba (也可以以如下方式连接：SQL>connect internal)SQL>alter user sys identified by sys; (回车后用户sys的密码会更改为sys)同理可以更改system密码SQL>alter user system identified by...

select * from teststart with id = '2'connect by prior id = pid;

--表锁select object_name,machine,s.sid,s.serial#from v$locked_object l,all_objects o ,v$session swhere l.object_id = o.object_id and l.session_id=s.sid;alter system kill session '127,3476';--存储过程锁select * from v$access where owner='用户' AND OBJ...

--启动VARIABLE job NUMBER;beginsys.dbms_job.submit(job => :job,what => 'dmpac_gu_action.trance_job_action();',next_date =>sysdate,interval => null);commit;end;/--停止select * from dba_jobs;select * from dba_jobs_running ;select '.

select (tablespace_name) "表空间名", sum(total_size) "总空间/M", sum(total_free) "剩余空间/M", sum(max_continue) "最大连续空间/M", round(sum(total_free) / sum(total_size) * 100) "剩余百分比/ratio" from ((select tablespace_na...

SELECT TO_DATE('27-OCT-17', 'DD-MON-RR','NLS_DATE_LANGUAGE = American') FROM DUAL;

select 'Alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';

查询该表指定时间内修改前的记录：select * from GUPOLICYCOPYMAIN AS OF TIMESTAMP TO_TIMESTAMP('2019-12-29 17:00:00','yyyy-mm-dd hh24:mi:ss');查询该表近段时间修改的记录：SELECT GUPOLICYCOPYMAIN.Inputdate VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION ...

select DBMS_AW.eval_number('((1+2)*9)/7') from dual;

Oracle数据库表分析脚本 exec dbms_stats.gather_table_stats('数据库用户','表名');

清表腳本DECLARE CURSOR CUR_TABLE IS SELECT TABLE_NAME FROM USER_TABLES ; V_SQL VARCHAR2(400); REC_TABLE CUR_TABLE%ROWTYPE;BEGIN     FOR  REC_TABLE IN CUR_TABLE LOOP              BEGIN            ...