Oracle
文章平均质量分 55
chncaesar
健身 搬砖 偶尔写个代码
展开
-
Oracle Database Compression 3 - Hybrid Columnar Compression
Hybrid Columnar CompressionHybrid Columnar Compression is a feature of the Enterprise Edition of Oracle Database 11g that is dependent on the underlying storage system. Only Exadata storage, ZFS sto原创 2013-12-15 00:10:56 · 941 阅读 · 0 评论 -
ORA-00845: MEMORY_TARGET not Supported on This System
ORA-00845: MEMORY_TARGET not supported on this systemCause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.Action: Refer to原创 2013-12-15 00:10:08 · 737 阅读 · 0 评论 -
Oracle 11g Automatic Memory Management
In 11g, Oracle database manages the PGA and SGA by default. memory_target decides the overall memory size. In this case, parameters concerning PGA and SGA will be like:show parameter target;NAME原创 2013-12-15 00:10:28 · 2430 阅读 · 0 评论 -
学习dbms_xplan_display_awr
用于显示保存在AWR中历史执行计划。用户需要select on DBA_HIST_SQL_PLAN,DBA_HIST_SQLTEXT,V$DATABASE的权限。请确保AWR已经运行。例子:SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('xxxx'));dbms_xplan.display_cursor只能显示保留在cursor cache中的执原创 2013-12-22 22:03:23 · 3849 阅读 · 0 评论 -
Oracle 11g的递归with语句
废话不多说,直接上例子感受一下:with emps (employee_id, name, job_id, salary, lvl) as ( select employee_id, first_name || ', '|| last_name name, job_id, salary, 1 as lvl from employees where manager_id is nul原创 2014-01-06 22:58:26 · 6750 阅读 · 0 评论 -
字符串型字段的Frequency histogram解读
create table t3(c1 varchar2(10) not null);insert into t3select to_char(round(dbms_random.value(1,10))) from dual connect by level commit;exec dbms_stats.gather_table_stats(user, 'T3', meth转载 2014-01-06 22:23:20 · 1150 阅读 · 0 评论 -
Transaction table及TX锁
每一个block header都分配了一部分空间作为transaction table。initrans参数控制了初始大小。Oracle 11gR2里,initrans的默认值是2。 transaction table的大小,即其同时允许对该block加锁的事务数,与block中剩余空间(不仅仅是block header)大小成正比。对于并发数比较高的系统,可以将initrans调高,以避免b原创 2013-12-23 23:25:10 · 1207 阅读 · 0 评论 -
Oracle histograms -- interpreting frequency histogram
http://jonathanlewis.wordpress.com/2010/09/20/frequency-histograms-2/原文请自备梯子。Frequency histogram的概念请看官方文档:http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#i41591Jonath翻译 2013-12-26 21:27:59 · 1359 阅读 · 0 评论 -
Oracle VPD
VPD = Virtual Private Database。同义词有RLS : Row Level Security, FGAC: Fine Grained Access Control。用于行级访问控制。假设有需求,只有用户'SCOTT'能访问emp表所有记录,其他人只能访问manager以下员工的记录。CREATE FUNCTION emp_policy(schema_in IN原创 2014-01-20 13:17:15 · 5794 阅读 · 0 评论 -
Oracle ASSM
ASSM是Automatic Segment Space Management的缩写。与ASSM相对是Manul Segment Space Management。只有在Locally Managed Tablespace中可以使用ASSM,system tablespace不可以使用ASSM。Locally managed tablespace和ASSm是11gR2的默认配置。In a lo原创 2014-01-18 18:56:57 · 3815 阅读 · 0 评论 -
Oracle IOT
Index Organized table by itself is a B-tree index. Index key is the primary key and the rest of columns are index values. The rows are stored in the primary key order. IOT provides fast access to原创 2014-01-18 20:07:19 · 1354 阅读 · 0 评论 -
Oracle数据库字符集介绍
本文内容部分来源于http://stackoverflow.com/questions/6509751/inserting-national-characters-into-an-oracle-nchar-or-nvarchar-column-does-not-wOracle服务器: select * from NLS_database_PARAMETERS where parame...原创 2014-02-14 16:49:15 · 1130 阅读 · 0 评论 -
Oracle Index 和null 研究
Indexing null values安装关系数据库理论, null表示未知,Oracle b-tree index是不包含null的。考虑如下表:create table tt (t_id number, t_name varchar2(10));create index tt_idx on tt(t_name);select * from tt where t_name i原创 2014-03-05 21:33:53 · 893 阅读 · 0 评论 -
SQL*Plus常见命令
spool和屏幕显示spool /home/oracle/out.txt创建文件,并将执行结果输出到文件。屏幕上也会显示。假如文件已经存在,将覆盖。spool /home/oracle/out.txt append追加模式spool: 显示当前spool 状态Spool off; 关闭spoolset termout off: 执行SQL脚本文件时,不要输出到屏幕。原创 2013-12-20 11:54:26 · 729 阅读 · 0 评论 -
nls_comp和nls_sort
Oracle官方文档Globalization Support的Linguistic Sorting and String Searching一章有详细解释nls_sort: 决定了Oracle 排序的方法,影响了ORDER BY, GROUP BY, comparison =, =, IN, BETWEEN, LIKE, MIN/MAX, GREATEST/LEAST, INSTR等函数原创 2014-03-11 23:05:19 · 1501 阅读 · 0 评论 -
ORA-00001 : Unique Constraint Violated Caused by DATE VS Timestamp(6)
Create table tt1 (dt date primary key);Create table tt2(dt timestamp(6) primary key);Insert into tt2 values('25-NOV-13 04.18.47.138746000 PM');Insert into tt2 values('25-NOV-13 04.18.47.66 PM')原创 2013-12-15 00:09:58 · 842 阅读 · 0 评论 -
Oracle Sequence Nocache
默认情况下,创建Sequence时,缓冲(cache)是20.意即:20次.nextval才会触发一次对sys.seq$的更新(update)操作。这样就能提高SQL执行性能。当用户指定nocache时,如:create sequence test_seq nocache;每一次test_seq.nextval都会触发一次sys.seq$更新(update)。我们可以比较下cach原创 2013-12-15 00:09:16 · 1403 阅读 · 0 评论 -
12c 初探--连接,启动/关闭,创建用户,数据字典等
EnvironmentWindows 7 64, Virtualbox :Oracle Linux 6.4 64bit ---Unbreakableenterprise kernelOracle 12.1This article covers1. Connecting to the root database.2. Getting containers informat原创 2013-12-15 00:11:14 · 852 阅读 · 0 评论 -
Oracle nologging
The size of a redo log generated for an operation in NOLOGGING mode is significantly smaller than the log generated in LOGGING mode.The NOLOGGING clause causes minimal redo information to be gener原创 2013-12-15 00:10:49 · 493 阅读 · 0 评论 -
Oracle Raw 数据类型
Raw直接保存了二进制,在不同平台上传输时,即使字符集不同,也不需要转换。传送的都是二进制信息。与varchar2类似,Raw(10)表示能存储最长10字节。Raw作为SQL数据类型,最长为2000。作为PL/SQL类型,最长32767。声明Raw型数据:create table test_raw ( c1 raw(10));insert into test_raw原创 2013-12-30 14:22:14 · 1113 阅读 · 0 评论 -
Oracle Create Table as Select
CTAS employs the direct path load, in other words, it skips loading data into buffer cache.PGA consumptionConsider two scenarios:a) Create table target as select * from source;The result o原创 2013-12-16 13:03:44 · 5637 阅读 · 0 评论 -
Deferred Constraint Check
Two ways to accomplish such goal:Constraints are created deferrableAlter session set constraints = deferred.create table p(p_id number primary key);create table c(c_id number primary key,p原创 2013-12-14 15:38:21 · 807 阅读 · 0 评论 -
Oracle Histogram -- 潜在危险
原文:http://allthingsoracle.com/histograms-part-1-why/在这篇文章里,作者Jonathan Lewis给出一个例子,结合Oracle计算直方图的原理,解释了直方图几点缺陷:1. 取样默认模式下,Oracle只取表中一部分数据作为样本计算直方图。假如数据倾斜非常厉害,如90%以上都是某个值,另一个值很少出现。取样时,没有将极少值取到。就翻译 2013-12-30 17:04:20 · 607 阅读 · 0 评论 -
SQL Projection
SQL Projection is the collective name for the columns that are Selected and returned from a query. In other words, the columns goes behind select in a query. In Oracle, a whole row is first fetched, t原创 2013-12-17 13:37:42 · 4119 阅读 · 0 评论 -
Oracle Query Result Cache
IntroductionThe server result cache is a memory pool within the shared pool.When a query executes, the database looks in the cache memory to determine whether the result exists in the cache. If th原创 2013-12-17 10:20:56 · 1762 阅读 · 0 评论 -
Oracle 12c - Data Redaction
EnvVirtualbox + Oracle Linux 64bit 6.4 + Oracle database 12.1IntroductionA new security feature is intorudced in 12c, one of top-10 favourite new features of Tom Kyte. It's also known as data ma原创 2013-12-17 13:30:00 · 1857 阅读 · 0 评论 -
Oracle RBA
Redo Byte Address (RBA) An RBA points to a specific location in a particular redo thread. It is ten bytes long and has three components: Redo log file sequence number (4 bytes)Block number w原创 2013-12-17 13:33:27 · 702 阅读 · 0 评论 -
12c -- Pattern Matching
Refer to:http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/misc/sql_pattern/sql_pattern.htmlChapter 18 SQL for Pattern MatchingBoth provide comprehensive examples to this f转载 2013-12-17 13:30:30 · 571 阅读 · 0 评论 -
Login Oracle Instance Even When sysdba Cannot Do So
This blog is based on:http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-sosqlplus -prelim "/as sysdba"When archiver is stuck, users with DBA role is not转载 2013-12-17 13:34:24 · 880 阅读 · 0 评论 -
Extracting DDLs from Oracle
DBMS_METADATAThis example extracts DDLs of every tableand index from scott, run it in sqlplus:set pagesize 0 ---Doesnot display column names.set long 90000 ---9000bytes of cache in loc原创 2013-12-17 13:30:59 · 604 阅读 · 0 评论 -
ORA-00904/ORA-00942和ORACLE 大小写区分
Oracle数据库中各种对象(包括表,列,索引等)默认不区分大小写,在数据字典中一律用大写。假如用户在建表(包括外部表)时,将列名/表名用双引号括起,这些都成为大小写区分的数据库对象。这时select 时需要严格按照建表时的大小写才能成功,否则返回ORA-000904或ORA-00942错误。某些软件可以帮助用户自动产生Create table语句,这时需要将双引号去掉。否则会产生上述原创 2013-12-17 13:31:18 · 1601 阅读 · 0 评论 -
Oracle Blocking Issue with Lock Table in Exclusive Mode
select ses.SID, substr(SQ.SQL_TEXT,0),sq.LAST_LOAD_TIME,ses.schemaname,SES.LOCKWAIT, SES.BLOCKING_SESSION_STATUS,BLOCKING_SESSION,SES.STATE, SES.EVENT, SES.EVENT#,SES.SECONDS_IN_WAIT,(select o原创 2013-12-17 13:32:06 · 976 阅读 · 0 评论 -
Oracle Join Methods
过程通过一个例子来说明SQL_ID gtmmr9gaxqhfr, child number 0-------------------------------------SELECT /*+ USE_HASH(d e) */e.ename, d.dname FROM emp e, dept d wheree.deptno=d.deptnoPlan hash value: 1123原创 2014-01-06 14:08:46 · 658 阅读 · 0 评论