2016年03月_bitko

原创关于alter table move

alter table move主要有两方面的作用1.用来移动table到其他表空间2.用来减少table中的存储碎片，优化存储空间和性能案例1：移动table到其他表空间--alter table moveSQL> create table t tablespace system as select * from all_objects;Table cre

2016-03-30 23:17:11 12169

原创高水位线

一.oracle的逻辑存储管理oracle在逻辑存储上分四个粒度：表空间，段，区和块1.块:是粒度最小的存储单位，标准块的大小是8k,oracle每一次io操作也是按块来进行就是说,oracle从数据文件读取数据时，是读取多少个块，而不是多少行.每一个block里面有可能包含掉个row.2.区：由一系列相邻的块组成，

2016-03-29 23:27:08 2861

原创 oracle聚簇表

oracle支持两种类型的聚簇：索引聚簇和哈希聚簇一.索引聚簇表的原理聚簇：如果一些表有一些共同的列，则将这样一组表存储在相同的数据块中聚簇还表示把相关的数据存储在同一个块上。利用聚簇，一个块可能包含多个表的数据。概念上就是说如果两个表或多个表经常做连接操作，就可以预先把需要的数据也存储在一起。聚簇还可以用于单个表，可以按某个列将数据分组存储。简单的说，簇就是一组表，由一组

2016-03-29 22:01:14 1803

原创关于oracle中varchar2的长度范围

varchar2有两个最大长度：一个是在字段类型4000；一个是在PL/SQL中变量类型32767.准确的说是在schema级varchar的长度类型限制为4000案例:CREATE OR REPLACE FUNCTION VAR_TEST RETURN VARCHAR2 IS T_COL VARCHAR2(32767); --plsql变量的varchar2长度范围为1到3

2016-03-29 21:02:21 1219

原创逻辑结构操作(表空间，数据文件，段，块，区)

1.查询表空间信息SQL> select * from v$tablespace;2.查询表空间及数据文件的信息SQL> select file_name,tablespace_name from dba_data_files;3.创建表空间，并设置数据文件为自动扩展SQL> create tablespace mytbs1 datafile '/oracle/app/

2016-03-28 22:24:19 680

原创丢失数据文件恢复操作(有备份)

恢复的前提条件1.必须是在归档模式下2.必须拥有一个可用的全备，并且拥有该全备以后知道数据文件丢失以后所有的归档日志文件，及数据文件丢失后的联机重做日志文件场景：SQL> create tablespace tbs1 datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf' size 5m;Tablespace

2016-03-28 21:16:47 395

原创 ORA-01843:无效的月份

案例：CREATE TABLE EMP(EMPNO NUMBER(4)NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)); IN

2016-03-28 21:01:23 335

原创 oracle单引号和双引号的区别

双引号1.表示其内部的字符串严格区分大小写2.用于特殊字符或关键字3.不受标识符规则限制4.会被当成一个列来处理5.当出现在to_char的格式字符串中时，双引号有特殊的作用，就是讲非法的字符包装起来，以避免出现日期格式转化错误案例1(区分大小写)：a.CREATE TABLE "t"AS SELECT * FROM dba_all_tables;

2016-03-28 20:47:47 2515

原创标量子查询改写

SELECT A.CONS_NO, A.CONS_NAME, (SELECTSUM(T.PURCH_PQ) FROMGAS_CA.A_CARD_METER_PAY T WHERE CONS_NO = A.CONS_NO AND T.PURCH_GAS_TIME > TO_DATE('201

2016-03-27 01:27:19 442

原创异常信息定位具体数据行

下午在执行一个dml操作时候，出现单行返回多行的异常信息，但不能知道具体是那列数据的问题，因为dml语句本身比较复杂，就想通过循环的方式找出异常数据行。只想到了一种游标的方法来定位，应该还有更好的方法。DECLARE CURSOR C_CUR IS SELECT * FROM EMP1 ORDER BY EMPNO DESC; C_CUR_ROW C_CUR%RO

2016-03-26 00:10:31 570

原创游标使用效率对比

游标的三种处理方式1.单条处理open 游标;LOOP FETCH 游标 INTO变量;EXIT WHEN 条件;END LOOP;CLOSE 游标; 2.批量处理open 游标;FETCH 游标 BULK COLLECT INTO集合变量;CLOSE 游标; 3.隐式游标for x in (sql语句) loop...-

2016-03-25 23:20:50 2938

原创管理achive log

一.概念：归档日志是联机重做日志组文件的一个副本，包含redo记录和一个唯一的log sequence number.对日志组的一个日志文件进行归档，如果改组其中一个损坏，则另一个可用的日志将会被归档归档日志的用途a.恢复数据库b.更新standby数据库c.使用logminer提取历史日志相关信息二.日志的两种模式1.非归档模式，即创建数据库时默认缺省的日志管理模

2016-03-24 22:18:12 378

原创 for update [of] 语句

for update 是把所有的表都锁定for update of根据of后表的条件锁定相对应的表--1.锁定整个表SELECT *FROM emp for UPDATE;--2.锁定表的单行记录SELECT *FROM emp a WHERE a.empno='7369'for UPDATE;--3.锁定两个表SELECT *FROM emp

2016-03-24 21:21:42 1390

原创游标使用总结

一.游标概念游标是sql的一个内存工作区，由系统或用户以变量的形式定义。其作用就是临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在硬盘的表中调到计算机内存中进行处理，最终将处理结果显示出来或写回到数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。游标分为两种类型:显式游标和隐式游标。通常用到的select..into语句，一次只能从数据库中提取一行数据，

2016-03-24 21:18:54 487

原创 oracle字符集的查看和修改(以修改redhat库字符集为例)

一.oracle字符集相关概念影响oracle数据库字符集的最重要参数是NLS_LANG参数。其格式如下：NLS_LANG=language_temitory.charset由三个部分组成(语言,地域和字符集)，每个成分控制了NLS子集的特性Language:指定服务器消息的语言，影响提示消息是中文还是英文Territory:指定服务器的日期和数字格式Charrset:指定

2016-03-23 22:20:40 889

原创 ORA-00918 未明确定义列，with t as 后面只能跟select

今天在with t as 作为临时表时报了这个错误，查了下原来是列名重复造成的WITH t AS (SELECT a.*,1 empno FROM emp a) SELECT * FROM t;定义为不同的列即可。另外 with t as 后面必须紧跟select语句，而不態使用update，delete，merge 等语句如果with t as 后面跟的是union

2016-03-23 21:50:07 2634

原创 varchar，varchar2，nvarchar，nvarchar2

1、varchar/varchar2 varchar是长度不固定的，比如说，你定义了varchar(20),当你插入abc，则在数据库中只占3个字节。 varchar同样区分中英文，这点同char。 varchar2基本上等同于varchar，它是oracle自己定义的一个非工业标准varchar，不同在于，varchar2用null代替var

2016-03-22 22:32:28 2310

原创日志文件管理

一.日志管理策略1.合理设置日志组的个数，如果日志组过少，LGWR将不得不因为检查点操作未完成或者日志组尚未归档而等待，在Oracle的警告文件或跟踪文件中也会出现以下信息，chheckpoint not complete,redo log groupnot archived2.日志文件复用，即一个日志组内包含多个日志文件，且存放在不同的磁盘3.日志文件存放到合理的位置a.

2016-03-21 23:25:40 475

原创 regexp_substr()函数

1.SELECT regexp_substr('1,2,12,13','[^,]+',1,3) FROM dual;字符：^ --匹配行首字符：+ --匹配一次或多次REGEXP_SUBSTR(source_string, --需要进行正则处理的字符串pattern, -

2016-03-21 22:41:38 3886

原创电话号码处理,附错误类型(无效字符，plsql该sql中不允许使用函数)

--电话号码处理PROCEDURE PRO_DM_MOBILE_SZKF(OUT_CODEOUT VARCHAR2(4000),OUT_MSGOUT VARCHAR2(4000))IS S_SQL VARCHAR2(4000); T_SQL VARCHAR2(4000);BEGIN UPDATE C_RESIDENTCUSTOMER A SET A.TEL

2016-03-21 22:24:09 921

原创 redo log & archive

在oracle当中，事务对数据库所做的修改以重做记录的形式保存在重做日志缓存中。在事务提交时，由LGWR进程将缓存中该事物相关的重做记录全部写入重做日志文件。这时，事务认为已经成功提交，这种机制称为‘快速提交’1.1.redo log结构重做日志具有以下特征a.记录对数据所做的更改b.提供恢复机制c.可以划分成组d.至少需要两个组日志组(redo log

2016-03-17 22:47:19 1376

原创 oracle Long数据类型总结

--概述SELECT *FROM t_20160315;1.存储可变长字符串，最大长度为2G2.对于超出一定长度的文本，基本上用long型存储，比如数据字典表中的许多字段SELECT *FROM All_Tab_Cols a WHERE a.data_type='LONG';3.long型主要用于不需要字符串搜索操作的长串数据，如果需要字段搜索就需要使用varchar2

2016-03-15 21:55:50 19583

原创 oracle日志存放位置dump_dest

oracle的日志文件有以下几种警告日志，trace日志，audit日志，redo日志，归档日志a.警告日志：也就是alert log,使用初始化参数 show parameter backgroud_dump_dest查看b.trace日志：一般存放session追踪的信息，使用show parametr user_dump_dest查看c.audit日志：审计的信息，对应系统初

2016-03-15 21:53:26 3417

原创控制文件(controlfile)

一.概述a.存放相关信息:控制文件记载了数据库的物理结构等重要的信息，如日志文件和数据文件信息。b.控制文件是用来维护数据库完整性的重要文件c.oracle正式使用控制文件来建立实例与数据库的连接d.当实例发生故障时，控制文件中记录的信息可以用来进行实例恢复二.控制文件内容控制文件的体现：在数据库mount阶段，oracle会按照初始化参数文件中controlfiles

2016-03-15 21:46:21 2109

原创 dbms_lock.sleep,日期格式

一． dbms_lock.sleep执行ORACLE_HOME/rdbms/admin/dbmslock.sql来创建dbms_lock;在dba身份下grant execute on dbms_lock to username;测试代码a.BEGIN DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE,'yyyymmddhh24miss')

2016-03-14 21:32:23 694

原创 function,procedure(异常处理)

1.函数和存储过程的区别a.标识符不同 function,procedureb.函数中一般不用变量形参，用函数名直接返回函数值；而过程如有返回值，必须用变量形参返回c.过程无类型，不能给过程名赋值；函数有类型，最终要将函数值传送给函数名d.函数在定义时一定要进行函数的类型说明，过程则不进行过程的类型说明e.函数的调用出现在表达式中，过程调用，由独立调用语句完成f.过程一般会

2016-03-08 22:59:05 1427

原创 oracle表名，字段名的长度限制

可以看出 ,user_tables这个表，table_name的字段长度为30，查看该视图的构造可以造出table_name的来源，sys.obj$，而且这个表是不能更改的。

2016-03-07 23:13:39 9189

原创 SYS_CONTEXT函数，获取当前连接信息

selectSYS_CONTEXT('USERENV','ACTION') ACTION,SYS_CONTEXT('USERENV','AUDITED_CURSORID') AUDITED_CURSORID,SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,SYS_CONTEXT('USER

2016-03-07 23:00:36 1697

原创 user_tab_cols和user_tab_columns的区别

user_tab_cols比user_tab_columns多几列HIDDEN_COLUMN VARCHAR2(3)VIRTUAL_COLUMN VARCHAR2(3)SEGMENT_COLUMN_ID NUMBERINTERNAL_COLUMN_ID NOT NULL NUMBERQUALIFIED_COL_NAME VARCHAR2(4000)而且存储内容多了隐藏字

2016-03-07 22:56:27 12689

原创 oracle判断是否为数字或数字型字符串

问题背景，to_number(...)中只能接受数字如122或数字型字符串'122'，接受'aaa'会报错“无效字符”。1.函数方法CREATE ORREPLACE FUNCTION IS_NUMBER(IN_PRICEIN VARCHAR2)RETURN NUMBER IS N_PRICE NUMBER; N_IS NUMBER;BEGIN N_

2016-03-07 22:38:50 1756

原创关于blob,clob，varchar2

blob:二进制大对象(binary large object)，用来存储大量二进制数据，在oracle中，通常视频音频图片等信息就用blob现将文件信息转化为二进制信息来存储blob可存储的最大大小为4GBclob:大字符对象(character large object)，用来存储大量文本数据，文章或者是较长的文字就用clob来存储，可存储的最大大小为4GB.简单的说就是，clob

2016-03-05 22:30:16 1543

原创 oracle自治事务(AT)

AT是由主事务(MT)调用但是独立于它的事务。当AT被调用时，mt被挂起,在AT的内部，一系列的dml可以被执行并且commit或rollback.并且由于commit和rollback的独立性，他的commit和rollback并不影响mt的执行效果。在at结束执行之后MT重新获得对事务的控制权使用例子--自治事务procedureCREATEOR REPLACE

2016-03-03 23:20:45 395

原创 DBMS_UTILITY.GET_CPU_TIME和DBMS_UTILITY.GET_TIME

1.计算密集型(cpu-bound)DECLARE N PLS_INTEGER := 0; A1 PLS_INTEGER; A2 PLS_INTEGER; A3 PLS_INTEGER; A4 PLS_INTEGER;BEGIN A1 := DBMS_UTILITY.GET_CPU_TIME; A3 := DBMS_UTILITY.GET

2016-03-02 23:18:44 487

原创 sqlplus的登录设置(glogin.sql)

SQL*PLUS启动时会自动查找运行两个脚本glogin.sql和login.sqlglogin.sql是sqlplus的全局登录profile,是oracle系统自带的脚本,其路径是固定的$ORACLE_HOME/sqlplus/admin。当用户启动sqlplus时,会从这个固定的路径加载glogin.sql,一般来说我们不用关心glogin.sql。login.sql是用户登

2016-03-02 21:35:04 707

原创参数文件

一.参数文件oracle提供了大概200多个初始化参数，但大多数参数都具有默认值，所以参数文件实际上只存放了非默认的初始化参数。SQL> select ISSES_MODIFIABLE,ISSYS_MODIFIABLE, ISMODIFIED from v$parameter where name='sort area size';a.查看参数的详细信息isses_modifi

2016-03-01 23:28:54 727

原创面试总结-1

1.编程题plsql实现CREATE OR REPLACE PROCEDURE GET(AIN VARCHAR2, B INVARCHAR2, C OUTVARCHAR2) IS N_COUNT C_CONS.CONS_ID%TYPE

2016-03-01 18:55:23 276

gumengkai的博客