数据库和Oracle
文章平均质量分 65
dingxy
保持微笑,保持平和的心,
做好计划,身体力行,不空想,
想好就做,不犹豫,
凡事都有得失,平淡对待
尽量关心朋友和亲人,不疏远,不孤立
过好每个周末
计划每年的假期和旅游计划
展开
-
Oracle query to filter out duplicate records
Oracle query to filter out duplicate records原创 2022-08-12 05:56:13 · 402 阅读 · 0 评论 -
How to Fix ‘ORA-12505, TNS:listener does not currently know of SID given in connect descriptor‘
from :How to fix 'ORA-12505' | Chartio TutorialTo properly resolve this error and connect to the appropriate Oracle database, we’ll need to expound a bit on how Oracle behaves and, therefore, what is causing this issue in the first place.SIDs vs SE..转载 2022-03-12 07:43:20 · 103 阅读 · 0 评论 -
DB query performance comparison and tuning
exmaple 1:----Status date updateselect jde2date(wrinsdte),jde2date(wrupmj), wruser from AA.f1217where wrnumb in (selectfanumbfrom AA.f1201where faasid in('209995'));...原创 2020-02-14 12:00:30 · 180 阅读 · 0 评论 -
Oracle Date Functions
Oracle Date Functions转载 2010-12-22 14:39:00 · 615 阅读 · 0 评论 -
Oracel Store procedure Exception handling
other references :Oracle Exception Handling:http://psoug.org/reference/exception_handling.htmlOracle/PLSQL Topics: Exception Handling:http://www.techonthenet.com/oracle/exceptions/http:/原创 2012-09-06 11:14:04 · 1217 阅读 · 0 评论 -
关于oralce时间比较的一个简单query
select ACTUAL_SETTLEMENT_DATE,to_char(CREATED_TMSTMP+ interval '480' MINUTE,'yyyy-mm-dd hh24:mi:ss'),to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24') hours,to_char(CREATED_TMSTMP+ interva原创 2012-12-13 18:38:48 · 673 阅读 · 0 评论 -
oracle 密码 重置,
D:\app\Administrator\product\11.2.0\dbhome_1\BIN>D:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 20 13:52:36 2012Copyr原创 2012-12-20 14:02:38 · 488 阅读 · 0 评论 -
window下命令行启动oracle服务(转载自eygle)
from http://www.cnblogs.com/yfsun1/archive/2008/07/06/1236886.html一、独立启动: Microsoft Windows 2000 [Version 5.00.2195](C) 版权所有 1985-2000 Microsoft Corp.######################################转载 2012-12-20 14:16:08 · 621 阅读 · 0 评论 -
To create tablespaces
set echo off;spool ./dba_create_tablespace.log;--------------------------------------------------------------------------- DB script executed by DBA USER to create database tablespaces----原创 2012-12-21 11:27:29 · 589 阅读 · 0 评论 -
oracle 递归:一个月内的每一天
1. 201212月每一天SELECT TO_DATE('20121201','YYYYMMDD')+LEVEL-1 calendardateFROM DUAL CONNECT BY LEVEL该月总天数LEVEL : from 1 to 本月总天数2.求某个月总天数select to_char(last_day(to_date('20130原创 2013-01-08 16:28:42 · 3842 阅读 · 0 评论 -
JDBC Types Mapped to Java Object Types
http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.htmlhttp://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html#table1http://mail-archives.apache.org/mod_mbox/原创 2013-01-25 14:13:21 · 953 阅读 · 0 评论 -
Oracle用Start with...Connect By子句递归查询
http://blog.csdn.net/Caesar_Gu/article/details/2055985Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。创建示例表:CREATE TABLE TBL_TEST( ID NUMBER, NAME VARCHAR2(100 BYTE), PID NUMB转载 2012-08-03 14:29:08 · 503 阅读 · 0 评论 -
Oracle basic alternation
1. add columnalter table transactionadd (SERVICE_LEVEL_CODE NUMBER,CRYPTOGRAPHY_INDEX VARCHAR2(20))2. renmae columnalter table sales rename column order_date to date_of_order;3. dr原创 2012-07-12 16:56:18 · 461 阅读 · 0 评论 -
ORA-12518: TNS:listener could not hand off client connection
ORA-12518: TNS:listener could not hand off client connection转自http://blog.sina.com.cn/s/blog_5904ea280100g614.htmlORA-12518: TNS:listener could not hand off client connectionCause: The p转载 2012-05-25 15:21:00 · 9509 阅读 · 4 评论 -
oracle 数组定义
oracle 数组定义转载 2010-11-22 15:58:00 · 1477 阅读 · 1 评论 -
procedure, function, array,cursor
procedure, function, array,cursor原创 2010-11-22 16:17:00 · 587 阅读 · 0 评论 -
Create table
Create table原创 2010-11-23 15:20:00 · 529 阅读 · 0 评论 -
unix 创建新的user
添加用户:useradd -d /export/home/test -g sys -G adm -m-u 100-s /bin/bash test命令介绍:useradd:-u uid -g gid用户所属主组 -G gid,gid用户所属原创 2011-09-28 17:38:50 · 779 阅读 · 0 评论 -
To get the "month" from a DATE datatype
To get the "month" from a DATE datatype you simply:to_char( dt_column, 'mm' ) -- returns 01..12to_char( dt_column, 'MON' ) -- returns JAN, FEB, ... DEC in your languageto_char( dt_column, 'Mon原创 2011-12-23 11:28:44 · 906 阅读 · 0 评论 -
Oracle 自动生成编号(实现sqlserver自增长字段)
oracle自动编号在access中有自动编号的数据类型,MSSQL和MYSQL也都有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值,而oracle没有自动增长的数据类型,我们需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段,可以预见的是,有此功能,我们可以把数据从ACCESS、MSSQL或MYSQL迁移到oracle了!create sequen转载 2011-12-23 09:50:19 · 1212 阅读 · 0 评论 -
规范化-数据库设计原则
陈 博, 浙江大学计算机科学与技术学院研究生蒋 韬, IBM 上海软件开发中心工具开发组的软件工程师摘要IBM 为社区提供了 DB2 免费版本 DB2 Express-C,它提供了与 DB2 Express Edition 相同的核心数据特性,为构建和部署应用程序奠定了坚实的基础。关系型数据库是当前广泛应用的数据库类型,关系数据库设计是对数据进行组织化和结构转载 2011-12-28 12:10:48 · 540 阅读 · 0 评论 -
SQL practice :"Case when" & "join"
1. Case whenselect table_name,(CASE WHEN owner='SYS' THEN 'The owner is SYS' WHEN owner='SYSTEM' THEN 'The owner is SYSTEM' ELSE 'The owner is another value' END) ownerDescfrom all_原创 2012-03-09 16:32:16 · 1087 阅读 · 0 评论 -
比较Oracle SQL中的IN & EXISTS
from;http://www.blogjava.net/terry-zj/archive/2006/04/18/41662.html在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢?1 性能上的比较比如Select * from T1 where x in ( select y from T2 )执行的过程相当于:select *转载 2013-02-16 16:52:49 · 429 阅读 · 0 评论 -
Microsoft SQL Server Management Studio -- Restore database in a difference server
Reference : http://msdn.microsoft.com/en-us/library/ms177429.aspxhttp://msdn.microsoft.com/en-us/library/aa337562.aspx1.Go to the source database server, back up the database, save the backup fi原创 2013-05-31 17:03:40 · 1076 阅读 · 0 评论 -
SQL SERVER – Enable xp_cmdshell using sp_configure
1. How to enable & check enable status of xp_cmdshell command --- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured原创 2013-09-27 17:55:37 · 945 阅读 · 0 评论 -
begin transaction/rollback transaction
http://msdn.microsoft.com/en-us/library/ms188929.aspx原创 2014-09-05 14:55:59 · 2160 阅读 · 0 评论 -
Restore SQL Server database in same SQL server with different name
Description : Iava原创 2014-08-12 17:18:15 · 533 阅读 · 0 评论 -
Cascade delete - SQL Server
refer to : http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-serveruse chs2ALTER TABLE FCCSERVICECALLDETAIL ADD CONSTRAINT FK_FCCSERVICECALLDETAIL_FCCSERVICECA原创 2014-09-03 16:11:22 · 782 阅读 · 0 评论 -
Transaction log is full. How do I shrink it?
https://support.myeasyprojects.net/kb/a163/transaction-log-is-full_-how-do-i-shrink-it.aspxMsg 9002, Level 17, State 2, Line 58The transaction log for database 'chs2' is full. To find out原创 2014-09-03 13:18:56 · 818 阅读 · 0 评论 -
NVARCHAR & VARCHAR
1.difference between varchar & nvarcharchar [ ( n ) ]Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes.原创 2015-03-26 15:44:07 · 501 阅读 · 0 评论 -
Limiting user access to your database tables
Common data are shared by 2 different systems . as such, try to create a sharing database by 2 systems.but systems have different access right on tables. like one system are not able to access the o转载 2015-09-08 09:13:49 · 477 阅读 · 0 评论 -
Database catalog and schema
reference : https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx1. Database Schema :A database schema is a way to logically group objects such astables, views, stored procedures转载 2016-01-20 15:05:24 · 1766 阅读 · 0 评论 -
EXECUTE IMMEDIATE _ Oracle example
set serveroutput on;DECLARE v_table_name varchar(100):='abc'; sql_stmt varchar(255):='default'; cnt NUMBER:=0; cursor tbl is select table_name from TBP_tables_tmp;BEGIN update TBP_ta...原创 2018-09-18 17:44:19 · 199 阅读 · 0 评论 -
T SQL functions
1.Left Padding2.Right Padding3.Case when(case when LEN(EXCHANGERATE) then REPLICATE('0', (8 - LEN(EXCHANGERATE))) + EXCHANGERATE else substring(EXCHANGERATE,0,9)原创 2014-06-30 18:08:33 · 621 阅读 · 0 评论 -
SSIS Package Development - Issues and Solutions
SSIS Package Development - Issues and Solutions1.Load Data from flat file(CSV file) to temp tableexample to follow :1) http://www.sql-server-performance.com/2013/temp-tables-ssis/2) http原创 2014-03-31 16:21:16 · 2710 阅读 · 0 评论 -
SSIS basic knowledge - logging, script task & file existence checking
Task to do : 0.Check the presence of the flat file,if file does exist, go on to 2, or go the end by wring log to physical log file1. Read a flat file and bulk insert into to temp table for furthe原创 2013-08-21 14:47:38 · 2513 阅读 · 0 评论 -
TSQL : Compare variable with integer when variable is nothing
IF((@FCCPROCESSSTATUS --(@FCCPROCESSSTATUS OR (@FOREIGNAMOUNT IS NOT NULL) )BEGINPrint 'DCCFlow : ' + cast( @DCCFLOWVERSION as varchar(10)) + '!!'ENDELSEBEGIN Print 'DCCFlow : not原创 2013-08-28 18:19:32 · 821 阅读 · 0 评论 -
Cursor Versus While Loop
http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/http://www.mssqltips.com/sqlservertip/1216/executing-a-tsql-batch-multiple-times-using-go/1.Cursor Example : DECLARE @n原创 2013-09-30 10:56:05 · 672 阅读 · 0 评论 -
Comparing cursor vs. WHILE loop performance in SQL Server 2008(ZThttp://stackoverflow.com/questions)
Other reference : http://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursorFrom http://www.techrepublic.com/blog/datacenter/comparing-cur转载 2013-09-19 10:44:53 · 1098 阅读 · 0 评论 -
T_SQL : to execute local .sql file
Reference : http://shunge.blog.51cto.com/2944070/6937041. Use .bat to execute .sql : XXX.batosql -S"127.0.0.1" -U"sa" -P"sa" -d"Northwind" -i"%CD%\1.sql"osql -S"127.0.0.1" -U"sa"原创 2013-09-27 18:00:54 · 956 阅读 · 0 评论