oracle日常积累小知识

1.将Oracle中同一列的多行记录拼接成一个字符串（行转列）
     wm_concat(字段)

示例：需要行转列的数据：

VM_CONCAT函数处理后（可用group by） 

相同功能的函数还有LISTAGG 

示例

2. 动态语句中execute immediate编译不报错，执行时报错

execute immediate ' '; or execute immediate v_sql; 中引号内和变量v_sql中不能有分号

3.查看表空间及剩余表空间大小

--查看所有表空间大小
select tablespace_name, sum(bytes) / 1024 / 1024 || 'M'
  from dba_data_files 
 group by tablespace_name;
 
--已经使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024 || 'M'
from dba_free_space 
group by tablespace_name;
--所以使用空间可以这样计算
select a.tablespace_name, total, free, total - free || 'M' used
  from (select tablespace_name, sum(bytes) / 1024 / 1024  total
          from dba_data_files
         where TABLESPACE_NAME = 'STAGE'
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024  free
          from dba_free_space
         where TABLESPACE_NAME = 'STAGE'
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
--查看表所占空间大小
select T.segment_name, Sum(bytes) / 1024 / 1024 ||'M'
  from User_Extents T
 where T.segment_type = 'TABLE'
   AND T.segment_name LIKE 'REIN%'
 GROUP BY T.segment_name

4.杀进程处理锁表问题

--常用表
select * from v$locked_object
select * from dba_objects
select * from v$session

--查看被锁的表 
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

--查看连接的进程 
SELECT sid, serial#, username, osuser FROM v$session;

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
--杀掉进程 sid,serial#
alter system kill session'210,11562';

5.不配置TNS，直连数据库

PLSQL登录时在database处填写内容：IP+端口+服务名 格式为： IP:端口号/SERVICE_NAME

例如TNS配置信息：

TEST01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.100.35)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST01)
    )
  )

DATABASE处填写：10.130.100.35:1521/TEST01

6.自增分区表示例

CREATE TABLE T_RES_TEST
(
  CO                           VARCHAR2(4) NOT NULL,
  POLNUM                       VARCHAR2(10) NOT NULL,
  DTLNUM                       NUMBER(2) NOT NULL,
  PRDCOD                       VARCHAR2(10) NOT NULL,
  DISUDAT                      VARCHAR2(8),
  SUBMISSION_DATE              VARCHAR2(8),
  COVAMT                       NUMBER(13,2)
)
PARTITION BY RANGE (ESTIMATE_DATE) INTERVAL(NUMTOYMINTERVAL(1,'month')) --(numtodsinterval(1,'day')) 按月分区与按天分区
SUBPARTITION BY LIST(DATA_FLAG)
SUBPARTITION TEMPLATE
   (SUBPARTITION OLD_NEW_BUSINESS VALUES ('NEW_BUSINESS'),
    SUBPARTITION OLD_RENEW VALUES ('RENEW'),
    SUBPARTITION OLD_LAPSE_REFUND VALUES ('LAPSE_REFUND'),
    SUBPARTITION OLD_TRUE_UP VALUES ('TRUE_UP'),
    SUBPARTITION OLD_RESCIND_REFUND VALUES ('RESCIND_REFUND'),
    SUBPARTITION OLD_CLAIM_REFUND VALUES ('CLAIM_REFUND') ) 
(PARTITION OLD VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

set_subpartition_template 子句，作用是重新定义（或新建、清除）复合分区表的 list 或 hash 子分区模板。
执行 set_subpartition_template 操作后，表中已创建的子分区不受影响，本地、全局索引也不受影响。在此之后的分区操作（例如 add、merge 操作）将使用新的模板。

 select table_name,partition_name from user_tab_partitions where table_name='T_RES_TEST'; --查看分区表信息

7.for循环嵌套插入表数据示例

DECLARE
  V_MINAGE NUMBER;
  V_BIGAGE NUMBER;
  V_PROD   VARCHAR2(20);
BEGIN
  FOR I IN (SELECT * FROM AA) LOOP
    V_PROD   := I.PRODCUT;
    V_MINAGE := I.MIN_AGE;
    V_BIGAGE := I.BIG_AGE;
    BEGIN
      FOR J IN V_MINAGE .. V_BIGAGE LOOP
        INSERT INTO BB (PRODUCT, AGE) VALUES (I.PRODCUT, J);
        COMMIT;
      END LOOP;
    END;
  END LOOP;
END;

效果显示：

AA表数据：

BB表数据：

循环调度存储过程示例：

DECLARE
  V_DATE DATE;
BEGIN
  FOR I IN (SELECT LAST_DAY(TO_DATE((201900 + LEVEL) || '01', 'yyyymmdd')) V_DATE
              FROM DUAL
            CONNECT BY LEVEL <= 10) LOOP
    V_DATE := I.V_DATE;
    PKG_REIN_TEST.SP_MAIN(V_DATE);
  END LOOP;
END;