随笔分类 - SQL
常用SQL脚本
摘要:create or replace procedure prc_user_xtzx_match(p_flag out varchar2) IS xingming_match_loginname varchar(100); sfzh_match_loginname varchar(100); BEGIN FOR c_row IN (SELECT * FROM t_user_xtzx) LOOP...
阅读全文
摘要:create or replace procedure prc_table_count(p_flag out varchar2) AS TCOUNT number; SCOUNT number; COUNTSQL VARCHAR2(500); BEGIN TCOUNT:= 0; SCOUNT:= 0; FOR c_row IN ( select table...
阅读全文
摘要:--UPDATE_TIME 是时间,直接转成字符串 SELECT to_char(MAX(UPDATE_TIME), 'yyyy-mm-dd hh24:mi:ss') as lastUpdateTime, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as accountTime FROM QLK_AREA; -- LASTUPDAT...
阅读全文
摘要:--更名 ALTER TABLE T_LOGSRV_SERVICE RENAME TO T_LOGSRV_SERVICE_20170418_BAK; --创建同样的表 CREATE TABLE T_LOGSRV_SERVICE AS SELECT * FROM T_LOGSRV_SERVICE_20170418_BAK WHERE 1=2;
阅读全文
摘要:--视图(可查看拥有者、对象名称、创建时间、上次修改时间) SELECT t.OBJECT_NAME, t.CREATED, t.LAST_DDL_TIME FROM user_objects t order by t.LAST_DDL_TIME desc; SELECT t.OWNER, t.OBJECT_NAME, t.CREATED, t.LAST_DDL_TIME FROM all_ob...
阅读全文
摘要:CREATE OR REPLACE FUNCTION GET_WS_DIC_VALUE(dcode IN VARCHAR2) RETURN VARCHAR2 as dname VARCHAR2(200); BEGIN SELECT NAME INTO dname from PSN_WS_DIC_RELATED_RANGE WHERE code = dcode; RETURN dname; ...
阅读全文
摘要:1、查看系统配置游标数 select value from v$parameter where name = 'open_cursors'; 2、查看游标使用情况 select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'XFTEST' and o....
阅读全文
摘要:--管理语句 --当前用户权限 select * from user_role_privs; --当前用户表空间 select username,default_tablespace from user_users; --查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)s select * from V$PWFILE_USERS; --查询语句 SELECT TO_C...
阅读全文
摘要:--创建表空间 create tablespace imark datafile 'E:\oracle\product\10.2.0\oradata\orcl\imark.dbf' size 500M autoextend on next 100M maxsize unlimited logging extent management local autoallocate segment ...
阅读全文
摘要:create database link DBLINK_IMARK_RAC connect to imark identified by imarkDB12345 using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.7.114)(PORT = 1521)) (ADDRESS = (PROTOCO...
阅读全文
摘要:--存储过程,查询A表,向B表插入数据 create or replace procedure prc_sg_sjtj_config(p_flag out varchar2) IS BEGIN FOR c_row IN (SELECT T.UNIT_NAME, T.DATA_NAME, T.TABLE...
阅读全文