oracle
大海之粟
认真负责
展开
-
带有双引号的表如何重命名
select RENAME " || TABLE_NAME || " TO || upper(TABLE_NAME) || ; from all_all_tables where owner=SYSTEM原创 2010-05-05 16:48:00 · 784 阅读 · 0 评论 -
oracle执行计划查看
--查询指定sql_id的执行情况 select * from table(dbms_xplan.display_cursor('ghnamt41nmgbt'));-- 以前执行的 select * from table(dbms_xplan.display_awr('83bvz3hr8s5p7'));--查询缓存中有的执行sqlselect * fro原创 2017-01-15 17:43:03 · 1451 阅读 · 0 评论 -
oracle11g分区创建
create table test_emp( ID NUMBER(20) not null, EMP_CODE VARCHAR2(30), EMP_name varchar2(30) CREATED_TM DATE not null, )partition by range (CREATED原创 2017-01-13 11:41:38 · 1276 阅读 · 0 评论 -
查询oracle相关表结构
--查询索引SELECT T.INDEX_NAME,LISTAGG(t.column_name,',') WITHIN GROUP (ORDER BY t.index_name) FROM USER_IND_COLUMNS T, USER_INDEXES I WHERE T.INDEX_NAME = I.INDEX_NAME AND T.TABLE_NAME转载 2016-12-23 11:48:27 · 320 阅读 · 0 评论 -
oracle包体中的储存过程与函数
1.首先创建包头:create or replace package pa_test_cursor is -- Author : ADMINISTRATOR -- Created : 2012-8-30 下午 02:52:24 -- Purpose : -- Public type declarations function get原创 2012-08-31 13:51:08 · 1067 阅读 · 0 评论 -
oracle 字符串拼接
<br />create or replace procedure proc_query_prior_department<br />is<br />Cursor department_list_nosub is<br />select dep.id,dep.name,dep.parent from department dep where org_id=7;<br />union_mc varchar2(200);<br />begin<br /> delete from ma_departmen原创 2011-04-27 17:46:00 · 745 阅读 · 0 评论 -
oracle 函数
<br />create or replace function ma_department(departId in number) return varchar2 is<br /> <br /> union_mc varchar2(500);<br />begin<br /> if departId>0 then<br /> <br /> for cur in (select t.parent,t.name from department t where parent is not null原创 2011-05-23 16:53:00 · 477 阅读 · 0 评论 -
oralce function
<br />create or replace function f_getPro(type_id_value in number, activity_id_value in number) return varchar2 is<br /> Result varchar2(10000);<br />begin<br /> declare cursor c_cur is <br /> select provice from t_bjoa_market_province where type_id = t原创 2010-11-05 10:49:00 · 722 阅读 · 0 评论 -
oracle 直接导出到excel
<br />利用sql直接导数到excel的两种方法<br />第一种:<br />set feedback off heading off verify off trimspool off<br />set pagesize 0 linesize 1000<br />define fil= 'D:/ORACLE_output.csv'<br />prompt *** Spooling to &fil<br />spool &fil<br />@d:/get_tables.sql<br /> <br />s原创 2010-06-12 16:11:00 · 686 阅读 · 0 评论 -
oracle查询及性能
表被锁+++++++++++++++++++++++++++++++++++++++++++++++++++--锁表查询SELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id = o.object_转载 2017-02-23 15:10:58 · 449 阅读 · 0 评论