v_sql_time := lpad(trunc( ceil((v_beg_time - sysdate) * 24 * 60 * 60)/3600),2,'0')||':'||
lpad(trunc(mod(ceil((v_beg_time - sysdate) * 24 * 60 * 60),3600)/60),2,'0')||':'||
lpad(round(mod(ceil((v_beg_time - sysdate) * 24 * 60 * 60),60),2),2,'0')
sp_etl_run_log(v_proname,v_sql_time,v_step,v_sql_code,v_run_msg,v_row_count,v_beg_time,sysdate,'sp_fin_trans_realtime');
oracle 两个时间相减默认的是天数
oracle 两个时间相减默认的是天数*24 为相差的小时数
oracle 两个时间相减默认的是天数*24*60 为相差的分钟数
oracle 两个时间相减默认的是天数*24*60*60 为相差的秒数
--MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>select months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual;
MON_BETW
---------
-60
Oracle计算时间差表达式
--获取两时间的相差豪秒数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh34-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh34-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL;
--获取两时间的相差秒数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh34-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh34-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;
--获取两时间的相差分钟数
select ceil(((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh34-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh34-mi-ss'))) * 24 * 60) 相差分钟数 FROM DUAL;
--获取两时间的相差小时数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh34-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh34-mi-ss')) * 24) 相差小时数 FROM DUAL;
--获取两时间的相差天数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh34-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh34-mi-ss'))) 相差天数 FROM DUAL;
----------------------------------------
注：天数可以2个日期直接减，这样更加方便
----------------------------------------
--获取两时间月份差
select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 +
EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) months
from dual;
--------------------------------------
注：可以使用months_between函数，更加方便
--------------------------------------
--获取两时间年份差
select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;
t.name='SP_RPT_MIS_COM_DAY_V3_S3' order by 4;
select *
from all_dependencies t
where t.name='SP_RPT_CREDIT_PAY_INFO_SUBDIV';
select * from V$SQL_BIND_CAPTURE ;
select * from all_procedures;
select * from user_arguments where procedure_name='SP_TRUNCAT'
现需要提取存储过程信息，因为数据源为oracle8i，可是8中没有提供诸如user_procedues这样的view来提供存储过程信息，只能在user_arguments这个view中可以找到存储过程的一些信息，
可是user_arguments不包括所有的存储过程，例如没有参数的存储过程，而且如果这个存储过程是在一个包（package）中时，到哪里才能找到这个存储过程的信息啊！
--【】v$process 该视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息
服务进程分两类，一是后台的，一是dedicate/shared server
pid, serial# 这是oracle分配的PID
spid 这才是操作系统的pid
program 这是服务进程对应的操作系统进程名
--【】v$session 该视图主要提供的是一个数据库connect的信息，
<1>主要是client端的信息
machine 在哪台机器上
terminal 使用什么终端
osuser 操作系统用户是谁
program 通过什么客户端程序，比如TOAD
process 操作系统分配给TOAD的进程号
logon_time 在什么时间
username 以什么oracle的帐号登录
command 执行了什么类型的SQL命令
sql_hash_value SQL语句信息
<2>有一些是server端的信息：
paddr 即v$process中的server进程的addr
server 服务器是dedicate/shared
还有其它一些信息，可以理解为是client/server共享的信息，主要是针对这个session而言的
create or replace function func_get_json_extract(pcharjson varchar2, --json字段值
pcharsub varchar2, --要取的map对
pnum number default 1, --第几次出现
psepchar varchar2 default '"', --value引号
psepfield varchar2 default ':' --key分隔符
)
return varchar2 IS
v_charjson varchar2(4000) := pcharjson;
v_charsub varchar2(4000) := psepchar||pcharsub||psepchar||psepfield||psepchar;
v_subval varchar2(4000);
begin
select substr(v_charjson,
i.poscod+i.len,
posend-(poscod+len)) into v_subval
from (select instr(v_charjson,v_charsub,1,pnum) as poscod,
length(v_charsub) as len,
instr(v_charjson,v_charsub,1,pnum)+length(v_charsub) as posbeg,
instr(v_charjson,'"',instr(v_charjson,v_charsub,1,pnum)+length(v_charsub),1) as posend,
v_charjson
from dual t) i;
return v_subval;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('请检查参数重试！');
end;
alter session set nls_date_language='american' ; 修改该命令，让字符串格式，能顺利导入到 日期格式中
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
select to_char(sysdate,'month','nls_date_language=American') from dual;
安全-权限-Oracle自带用户
①Sysdba和dba的区别
新建时给的是系统权限sysdba,
后面使用到该用户的时候不能以normal状态登录plsql，
因为没有给用户赋予创建session的权限。只要用sys登录
grant connect,resource to xwm 给用户赋权就行了
dba是Oracle里的一种对象，Role 和User一样，是实实在在存在在Oracle里的物理对象，而sysdba是指的一种概念上的操作对象，在Oracle数据里并不存在。
所以说这两个概念是完全不同的。
dba是一种role对应的是对Oracle实例里对象的操作权限的集合，而sysdba是概念上的role是一种登录认证时的身份标识而已。
SYSDBA不是权限，当用户以SYSDBA身份登陆数据库时，登陆用户都会变成SYS。
sysdba身份登陆可以打开，关闭数据库，创建SPFILE，对数据库进行恢复操作等，而这些是DBA角色无法实现的。
Oracle自带用户
以下这些账户有的为了管理目的、有些为了数据库额外的功能、有些为了示例而存在。如果你启用了一些比较偏的功能还会自动新增一些用户，比如标签安全性、数据库保险箱啊等等。
这些账户每个你Google一番都能讲一个故事。。。
账户和访问表没有直接的关系，权限才是，拿SELECT ANY TABLE（查看任何表）为例，看看谁能查看任何表：
SQL> select grantee from dba_sys_privswhere privilege='SELECT ANY TABLE';
oracle中的帐户分为两类:
一类是必需的帐户
一类是存储各种应用的帐户
【】SYS 安装时用户指定/CHANGE_ON_INSTALL
描述：数据库管理帐户/执行数据库的管理任务，实例的数据字典都在SYS下/超级用户，老大
Recreation Script：$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.
Safe To Remove：No
Purpose：Anaccount used to perform. database administration tasks. Password is created atinstallation or database creation time.
【】SYSMAN OEM_TEMP/manager
描述：企业管理器帐户
Recreation Script：Createdas part of the dbconsole or Enterprise Manager build.
Safe To Remove：Yes
Purpose：Theaccount used to perform. Oracle Enterprise Manager database administrationtasks. The SYS and SYSTEM accounts can also perform. these tasks. Password iscreated at installation or database creation time.
【】SYSTEM 安装时用户指定
描述：数据库管理帐户/用于执行数据库管理任务，有少量的实例对象/权限也非常高，但是不具备以下权限：ALTER DATABASE LINK/ ALTERPUBLIC DATABASE LINK/ EXEMPT ACCESSPOLICY/ EXEMPT IDENTITY POLICY/ SYSDBA/ SYSOPER。其中SYSDBA和SYSOPER还能启动关闭数据库实例，所以SYSTEM当然不能启动关闭数据库实例了。
Recreation Script：$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.
Safe To Remove：No
Purpose：A defaultgeneric database administrator account for Oracle databases. For productionsystems, Oracle recommends creating individual database administrator accountsand not using the generic SYSTEM account for database administrationoperations. Password is created at installation or database creation time.
【】ANONYMOUS ANONYMOUS
描述：访问http的匿名用户帐户/用于访问ORACLE XML DB知识库的帐户。
Recreation Script：$ORACLE_HOME/rdbms/admin/catqm.sql
Safe To Remove：Yes
Purpose：Accountthat allows HTTP access to Oracle XML DB. It is used in place of theAPEX_PUBLIC_USER account when the Embedded PL/SQL Gateway (EPG) is installed inthe database. EPG is a Web server that can be used with Oracle Database. Itprovides the necessary infrastructure to create dynamic applications. See alsoXDB.
【】CTXSYS CTXSYS
描述：interMedia Text用户，是MEDIATEXT的用户，有CONNECT\RESOURCE\DBA权限
Recreation Script：$ORACLE_HOME/ctx/admin/ctxsys.sql
Safe To Remove：Yes
Purpose：Theaccount used to administer Oracle Text. Oracle Text enables the building oftext query applications and document classification applications. It providesindexing, word and theme searching, and viewing capabilities for text.
【】DBSNMP DBSNMP
描述：目录集成平台用户/具有NNECT\RESOUCEHE\SNMPAGENT 权限的角色，可用CATNSMP.SQL脚本删除此用户和角色
Recreation Script：$ORACLE_HOME/rdbms/admin/catsnmp.sql
Safe To Remove：Yes– run $ORACLE_HOME/rdbms/admin/catnsnmp.sql
Purpose：Theaccount used by the Management Agent component of Oracle Enterprise Manager tomonitor and manage the database. Password is created at installation ordatabase creation time.
【】DIP DIP
描述：目录集成平台的帐户（DIRECTORY INTEGERATION PLATFORM)
Recreation Script：$ORACLE_HOME/rdbms/admin/catdip.sql
Safe To Remove：Yes
Purpose：Theaccount used by the Directory Integration Platform. (DIP) to synchronize thechanges in Oracle Internet Directory with the applications in the database.
【】EXFSYS EXFSYS
描述：表达式过滤器帐户（EXPRESSIO FILTER)
Recreation Script：$ORACLE_HOME/rdbms/admin/exfsys.sql
Safe To Remove：Yes
Purpose：Theaccount used internally to access the EXFSYS schema, which is associated withthe Rules Manager and Expression Filter feature. This feature enables thebuilding of complex PL/SQL rules and expressions. The EXFSYS schema containsthe Rules Manager and Expression Filter DDL, DML, and associated metadata.
【】MDDATA MDDATA
描述：oracle空间数据帐户
Recreation Script：$ORACLE_HOME/md/admin/catmd.sql
Safe To Remove：Yes
Purpose：Theschema used by Oracle Spatial for storing Geocoder and router data. See alsoSPATIAL_CSW_ADMIN_USR , SPATIAL_WFS_ADMIN_USR and MDSYS.
【】MDSYS MDSYS
描述：oracle空间数据媒体管理员/空间数据（SPATIAL)\媒介（INTERMEDIA)\音频（AUDIO)\视频（VIDEO)和图像管理员帐户
Recreation Script：$ORACLE_HOME/ord/admin/ordinst.sql
Safe To Remove：Yes
Purpose：TheOracle Spatial and Oracle Multimedia Locator administrator account. See alsoSPATIAL_CSW_ADMIN_USR , MDDATA and SPATIAL_WFS_ADMIN_USR.
【】MGMT_VIEW MGMT_VIEW
描述：不拥有任何对象，仅用于查询 SYSMAN 用户对象。安装OMS时自动创建的。
Recreation Script：$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager
Safe To Remove：Yes
Purpose：Anaccount used by Oracle Enterprise Manager Database Control. Password israndomly generated at installation or database creation time. Users do not needto know this password.
【】OLAPSYS MANGER
描述：用于创建olap元数据/用于创建OLAP元数据的用户。包括OLAP_DBA\RESOURCE\CONNECT角色
Recreation Script：$ORACLE_HOME/olap/admin/amdsys.sql
Safe To Remove：Yes
Purpose：Theaccount that owns the OLAP Catalog (CWMLite). This account has been deprecated,but is retained for backward compatibility.
【】ORDPLUGINS ORDPLUGINS
描述：ORACLE INTERMEDIA和VIDEO的用户名，有CONNECT和RESURCE角色，与第三方连接
Recreation Script：$ORACLE_HOME/ord/admin/ordinst.sql
Safe To Remove：Yes
Purpose：TheOracle Multimedia user. Plug-ins supplied by Oracle and third-party, formatplug-ins are installed in this schema. Oracle Multimedia enables OracleDatabase to store, manage, and retrieve images, audio, video, DICOM formatmedical images and other objects, or other heterogeneous media data integratedwith other enterprise information. See also ORDSYS and SI_INFORMTN_SCHEMA.
【】ORDSYS ORDSYS
描述：image管理员/ORACLE INTERMEDIA和VIDEO的用户名，有CONNECT和RESOURCE角色
Recreation Script：$ORACLE_HOME/ord/admin/ordinst.sql
Safe To Remove：Yes
Purpose：TheOracle Multimedia administrator account. See also ORDPLUGINS and SI_INFORMTN_SCHEMA.
【】OUTLN OUTLN
描述：拥有connect和resource角色
Recreation Script：$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.
Safe To Remove：No
Purpose：Theaccount that supports plan stability. Plan stability prevents certain databaseenvironment changes from affecting the performance characteristics ofapplications by preserving execution plans in stored outlines. OUTLN acts as arole to centrally manage metadata associated with stored outlines.
【】SCOTT Tiger
描述：简单的样例帐户
Recreation Script：$ORACLE_HOME/rdbms/admin/utlsampl.sql
Safe To Remove：Yes
Purpose：Anaccount used by Oracle sample programs and examples.
【】HR hr
描述：简单的样例帐户
Recreation Script：$ORACLE_HOME/demo/schema/human_resources/hr_main.sql
Safe To Remove：Yes– run $ORACLE_HOME/demo/schema/drop_sch.sql
Purpose：Theaccount that owns the Human Resources schema included in the Oracle SampleSchemas. See also BI, OE, SH, IX and PM.
【】SI_INFORMTN_SCHEMA oracleineterMedia,video用户
描述：静止图像标准浏览帐户
Recreation Script：$ORACLE_HOME/ord/admin/ordinst.sql
Safe To Remove：Yes
Purpose：Theaccount that stores the information views for the SQL/MM Still Image Standard.See also ORDPLUGINS and ORDSYS.
【】WK_TEST WK_TEST
描述：同wksys/管理ORACLE 10g ULTRASEARCH的帐户，和WKSYS有一样的作用
Recreation Script：$ORACLE_HOME/ultrasearch/admin/wk0csys.sql
Safe To Remove：Yes
Purpose：Theinstance administrator for the default instance, WK_INST. After unlocking thisaccount and assigning this user a password, then the cached schema passwordmust also be updated using the administration tool Edit Instance Page. UltraSearch provides uniform. search-and-location capabilities over multiplerepositories, such as Oracle databases, other ODBC compliant databases, IMAPmail servers, HTML documents managed by a Web server, files on disk, and more.See also WKSYS
【】WKPROXY
描述：ORACLE 10g与代理服务器有关的帐户
Recreation Script：$ORACLE_HOME/ultrasearch/admin/wk0csys.sql
Safe To Remove：Yes
Purpose：Anadministrative account of Application Server Ultra Search.
【】WKSYS WKSYS
描述：同wk_test/管理ORACLE 10g AS ULTRASEARCH帐户，与WK_TEST有一样的作用
Recreation Script：$ORACLE_HOME/ultrasearch/admin/wk0csys.sql
Safe To Remove：Yes
Purpose：AnUltra Search database super-user. WKSYS can grant super-user privileges toother users, such as WK_TEST. All Oracle Ultra Search database objects areinstalled in the WKSYS schema. See also WK_TEST
【】WMSYS WMSYS
描述：工作空间管理帐户/工作实景管理的帐户
Recreation Script：$ORACLE_HOME/rdbms/admin/owmctab.plb
Safe To Remove：Yes
Purpose：Theaccount used to store the metadata information for Oracle Workspace Manager.
【】XDB CHANGE_ON_INSTALL
描述：ORACLE 10G XML DB帐户
Recreation Script：$ORACLE_HOME/rdbms/admin/catqm.sql
Safe To Remove：Yes
Purpose：Theaccount used for storing Oracle XML DB data and metadata. See also ANONYMOUS.
【】ORACLE_OCM
描述：Oracle预定义的非管理员用户
Recreation Script：$ORACLE_HOME/rdbms/admin/catocm.sql
Safe To Remove：Yes
Purpose：Thisaccount contains the instrumentation for configuration collection used by theOracle Configuration Manager.
【】BI
描述：
Recreation Script：$ORACLE_HOME/demo/schema/bus_intelligence/bi_main.sql
Safe To Remove：Yes-run$ORACLE_HOME/demo/schema/drop_sch.sql
Purpose：Theaccount that owns the Business Intelligence schema included in the OracleSample Schemas. See also HR, OE, SH, IX and PM.
【】OE
描述：
Recreation Script：$ORACLE_HOME/demo/schema/order_entry/oe_main.sql
Safe To Remove：Yes– run $ORACLE_HOME/demo/schema/drop_sch.sql
Purpose：Theaccount that owns the Order Entry schema included in the Oracle Sample Schemas.See also BI, HR, SH, IX and PM.
【】IX
描述：
Recreation Script：$ORACLE_HOME/demo/schema/info_exchange/ix_main.sql
Safe To Remove：Yes– run $ORACLE_HOME/demo/schema/drop_sch.sql
Purpose：Theaccount that owns the Information Transport schema included in the OracleSample Schemas. See also BI, HR, OE, SH and PM.
【】PM
描述：
Recreation Script：$ORACLE_HOME/demo/schema/product_media/pm_main.sql
Safe To Remove：Yes– run $ORACLE_HOME/demo/schema/drop_sch.sql
Purpose：Theaccount that owns the Product Media schema included in the Oracle SampleSchemas. See also BI, HR, OE, SH and IX.
【】SH
描述：
Recreation Script：$ORACLE_HOME/demo/schema/sales_history/sh_main.sql
Safe To Remove：Yes– run $ORACLE_HOME/demo/schema/drop_sch.sql
Purpose：Theaccount that owns the Sales History schema included in the Oracle SampleSchemas and is only available for Enterprise Edition installations. See alsoBI, HR, OE, IX and PM.
【】APEX_030200
描述：
Recreation Script：$ORACLE_HOME/apex/apexins.sql
Safe To Remove：Yes
Purpose：Partof the Oracle Application Express Suite - (Oracle APEX, previously named OracleHTML DB) which is a freeware software development environment. It allows a fastdevelopment cycle to be achieved to create web based applications. The accountowns the Application Express schema and metadata. See also APEX_PUBLIC_USER andFLOW_FILES.
【】APEX_PUBLIC_USER
描述：
Recreation Script：$ORACLE_HOME/apex/apexins.sql
Safe To Remove：Yes
Purpose：Partof the Oracle Application Express Suite - (Oracle APEX, previously named OracleHTML DB) which is a freeware software development environment. It allows a fastdevelopment cycle to be achieved to create web based applications. Thisminimally privileged account is used for Application Express configuration withOracle HTTP Server and mod_plsql. See also APEX_030200 and FLOW_FILES.
【】APPQOSSYS
描述：
Recreation Script：$ORACLE_ADMIN/rdbms/admin/catqos.sql
Safe To Remove：Yes
Purpose：Usedfor storing/managing all data and metadata required by Oracle Quality ofService Management.
【】FLOWS_FILES
描述：
Recreation Script：$ORACLE_HOME/apex/apexins.sql
Safe To Remove：Yes
Purpose：Partof the Oracle Application Express Suite - (Oracle APEX, previously named OracleHTML DB) which is a freeware software development environment. It allows a fastdevelopment cycle to be achieved to create web based applications. This accountowns the Application Express uploaded files. See also APEX_030200 andAPEX_PUBLIC_USER.
【】OWBSYS
描述：
Recreation Script：$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql
Safe To Remove：Yes
Purpose：Theaccount for administrating the Oracle Warehouse Builder repository. Access thisaccount during the installation process to define the base language of therepository and to define Warehouse Builder workspaces and users. A datawarehouse is a relational or multidimensional database that is designed forquery and analysis. See also OWBSYS_AUDIT.
【】OWBSYS_AUDIT
描述：
Recreation Script：$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql
Safe To Remove：Yes
Purpose：Thisaccount is used by the Warehouse Builder Control Center Agent to access the heterogeneousexecution audit tables in the OWBSYS schema.
【】SPATIAL_CSW_ADMIN_USR
描述：
Recreation Script：$ORACLE_HOME/md/admin/sdocswpv.sql
Safe To Remove：Yes
Purpose：TheCatalog Services for the Web (CSW) account. It is used by the Oracle SpatialCSW cache manager to load all record type metadata, and record instances fromthe database into the main memory for the record types that are cached. Seealso SPATIAL_WFS_ADMIN_USR, MDDATA and MDSYS.
【】SPATIAL_WFS_ADMIN_USR
描述：
Recreation Script：$ORACLE_HOME/md/admin/sdowfspv.sql
Safe To Remove：Yes
Purpose：TheWeb Feature Service (WFS) account. It is used by the Oracle Spatial WFS cachemanager to load all feature type metadata, and feature instances from thedatabase into main memory for the feature types that are cached. See alsoSPATIAL_CSW_ADMIN_USR , MDDATA and MDSYS.
【】XS$NULL
描述：
Recreation Script：$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.
Safe To Remove：No
Purpose：Aninternal account that represents the absence of a user in a session. BecauseXS$NULL is not a user, this account can only be accessed by the Oracle Databaseinstance. XS$NULL has no privileges and no one can authenticate as XS$NULL, norcan authentication credentials ever be assigned to XS$NULL.
【】LBACSYS
描述：
Recreation Script：$ORACLE_HOME/rdbms/admin/catlbacs.sql
Safe To Remove：Yes
Purpose：Theaccount used to administer Oracle Label Security (OLS). It is created only whenthe Label Security custom option is installed.
【】ORDDATA
描述：
Recreation Script：$ORACLE_HOME/ord/admin/ordisysc.sql
Safe To Remove：Yes
Purpose：Thisaccount contains the Oracle Multimedia DICOM data model.
【】TSMSYS
描述：Oracle?10g的所有目录上的特点及与此相关的观点/表的新特征是储存在TSMSYS用户下
Recreation Script：
Safe To Remove：
Purpose：
【】DMSYS
描述：Oracle数据挖掘账号
Recreation Script：
Safe To Remove：
Purpose：
