ITPub博客

1、创建分片表create table cell_fragment(id int,name varchar(10))FRAGMENT by EXPRESSION(id > 5 and id < 10) IN niosdbs,(id <= 5) IN tpddbs;2、生成测试数据insert into cell_fragment values(1,'1111');insert into cell_fragment values(4,'44');insert into cell_fragment values(8,'81111');insert into cell_fragment values(9,'91111');insert into cell_fragment values(2,'21111');insert into cell_fragment values(3,'31111');insert into cell_fragment values(10,'31111');insert into cell_fragment values(

例如表aa，有a,b,c,d 4个字段，要取每行中b,c,d 3个字段的最大值A B C D1 2 3 42 3 5 13 7 3 5取出结果如下：1 42 53 7测试和实现如下：create table aa(a int,b int,c int,d int,constraint pk_aa primary key( a ))insert into aa values(1,2,3,4);insert into aa values(2,3,5,1);insert into aa values(3,7,3,5);commit;select * from aa;select aa.a, max(tmp.bb) from aa, (select a, b as bb from aa union all select a, c as bb from aa union all s

--创建测试表create table AAHXL( ID NUMBER, NAME VARCHAR2(100), DT TIMESTAMP(6))--创建存储过程create or replace procedure pro_ins_aahxl is li_id int;-- lti_dt timestamp;begin select nvl(max(id), 0) + 1 into li_id from aahxl;-- select to_char(sysdate, 'yyyyy-mm-dd hh:mi:ss') into lti_dt from dual; insert into aahxl (id, name, dt) values (li_id, 'name' || li_id, sysdate); commit;end pro_ins_aahxl;--创建jobDECLARE job NUMBER; begin sys.dbms_job.submit(job => job,

informix数据库中的时间运算select first 1 today,current,current -1 units year,current -1 units day,current -1 units hour,current -1 units minute,current -1 units second from systablesselect first 1 extend(current,year to day),extend(current,year to second),extend(current,year to year),extend(current,hour to second) from systablesselect first 1 extend(current,year to day), extend(current,year to day) - 1 units day,extend(current,year to second),extend(current,year to year),extend(current,hour to s

touch /opt/informix/chunks/niosdbs_hxlchmod 660 chunks/niosdbs_hxl onspaces -a niosdbs -p /opt/informix/chunks/niosdbs_hxl -s 1000 -o 0onspaces -d niosdbs -p /opt/informix/chunks/niosdbs_hxl -o 0oncheck -pe niosdbs[@more@]

oracle登陆有两种认证方式：os认证和密码文件认证Password File/Operating System Authentication1、os认证需要把登陆用户加入到os相应的用户组Operating System Group UNIX User Group Windows User GroupOSDBA dba ORA_DBAOSOPER oper ORA_OPER本机CONNECT / AS SYSDBACONNECT / AS SYSOPER远程CONNECT /@net_service_name AS SYSDBACONNECT /@net_service_name AS SYSOPER2、密码文件认证，密码文件在不同的os平台下，路径和默认的名称不同。ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users force =yREMOTE_LOGIN_PASSWORDF

分区表以及分区索引测试的场景建立如下四个独立的tablespace；然后建立四个相应的table和index。测试那种配置下性能最优。由于环境限制，table或index分区都没有独立的tablespace，但是每种情形测试条件都是相同的，以保证测试结果的可比性table index tablespace不分区 ,不分区 , tp_tno_ino不分区 ,global hash分区 ,tp_tno_ighhash分区 ,local hash分区 ,tp_th_ilhhash分区 ,global hash分区 ,tp_th_ighcreate tablespace tp_tno_ino datafile '/export/home/oracle/oradata/tp_tno_ino.dbf' size 3g;create tablespace tp_tno_igh dat

easy connectCONNECT username/password@host[:port][/service_name]SQL> conn oracle/oracle@10.0.8.31:1521/dbprim已连接。SQL> conn oracle/oracle@10.0.8.31:1521/dbprim已连接。SQL> show userUSER 为 "ORACLE"SQL> conn oracle/oracle@10.0.8.31/dbprim已连接。SQL>--database security b14266audit 可以存在sys.aud$ 也可以存在os上dbms_fga安全：物理安全，os安全，组件最小化，用户锁定，网络安全 ip限定 监听加密，权限最小化，密码规则复杂化，审计ddl dml，DBA_USERSALL_USERSUSER_USERSDBA_TS_QU

CREATE DATABASE [test20090821] ON PRIMARY (NAME = N'test20090821_Data', FILENAME = N'f:sql_dbtest20090821_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'test20090821_Log', FILENAME = N'f:sql_logtest20090821_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)alter database test20090821 set OFFLINE RESTORE DATABASE test20090821 FROM disk='F:SQL_DBVimicro_Ax30_SP4_FORMAL_02_2300' WITH REPLACE, RECOVERY ,move 'Vimicro_Ax30_SP4_FORMAL_Data' to 'F:SQL_DBtest20090821_Data.MDF', move 'Vimicro_Ax30_SP

发自焦雪：您好！华为赛门铁克公司（www.huaweisymantec.com）邀请您本周六10：00来参加我们的面试，地点：海淀区上地七街信息产业基地信息路7号数字传媒大厦一层迪欧咖啡，并请携带您的毕业证、学位证、英语等级证、身份证（4证）的原件和复印件，联系电话：62972109，咖啡厅：62976006（如无法参加请务必回复此短信）面试过程稍后上传……面试过程：总共分为5轮10点等待技术面试（2轮）技术一面，技术二面技术面试完了就是免费午餐下午集体面试（3面，后面的都没有参加就通知回来等消息了，估计没戏了）hr4面老总5面总结：这次技术面试没

今天2009-8-12老婆怀孕10w+2d，预约海妇今天下午BC检查，因为上次检查无胎心胎芽，所以在我的坚持下，10w以后再检查，什么胎芽胎心该有的都应该有了，也不再受那些所谓的妇科专家吓唬了，说实话这次还是比较忐忑的，昨晚没睡好，一直在安慰担心失眠的老婆，其实我也很担心，但不能说出来，怕老婆更担心，我们做好了最坏的打算，我请不了假，我建议让岳母陪着老婆一起去检查，老婆说没事，她很坚强，挂的是今天下午第一个号，13：30老婆传来喜讯，bc检查结果胎芽2.8cm，有胎心，活胎。听得出电话那头的老婆都要喜极而泣了，当时老婆跟小孩一

--view sequence synonymsview也是可以update的，只要你有update的权限，不过有很多约束例如：view不能含有 set distinct group by，not null列没有default值，decode等等都不能update deletecreate table AAATTT( ID CHAR(5) not null, NAME CHAR(10), NOTE CHAR(30), XINGMING VARCHAR2(10), XINGM VARCHAR2(10), NUM NUMBER(5,3), NUM2 NUMBER(4), NUM3 NUMBER(*,3), DAT DATE, TIMLZ TIMESTAMP(6) WITH LOCAL TIME ZONE, TIMZ TIMESTAMP(6) WITH TIME ZONE);create view aaat a

cluster多用在 经常需要连立的表上优点：减少io；节省存储空间cluster key column 一定要有合理的区分度，最好一个key 的row能存储在一个data block中CREATE CLUSTER emp_dept (deptno NUMBER(3))SIZE 600TABLESPACE usersSTORAGE (INITIAL 200KNEXT 300KMINEXTENTS 2MAXEXTENTS 20PCTINCREASE 33);CREATE TABLE emp (empno NUMBER(5) PRIMARY KEY,ename VARCHAR2(15) NOT NULL,. . .deptno NUMBER(3) REFERENCES dept)CLUSTER emp_dept (deptno);CREATE TABLE dept (deptno NUMBER(3) PRIMARY KEY, . . . )CLUSTER emp_dept (deptno);CRE

--分区表Range partitioningHash partitioningList partitioningComposite range-hash partitioningComposite range-list partitioning--range分区；可以有多个字段组成partition keyCREATE TABLE sales( invoice_no NUMBER,sale_year INT NOT NULL,sale_month INT NOT NULL,sale_day INT NOT NULL )PARTITION BY RANGE (sale_year, sale_month, sale_day)( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)TABLESPACE tsa,PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)TABLESPACE tsb,PARTITION sales_q3 VALUES LESS THAN (1

--index management--load data first,create index after--创建索引的规则1、query less then 15% rows of a large table2、在多表连接字段上建立index3、小表不需要建立index4、在相对唯一的字段上建立b-tree index，集势小的字段上建立bitmap index5、WHERE COL_X > -9.99 * power(10,125) 来代替 WHERE COL_X IS NOT NULL（假设col_x number类型）6、多字段复合index，注意字段顺序，只有匹配字段顺序的才可以利用索引7、同一个表的index的数量，要权衡select update的性能，在只读表上可以多建index，在频繁更新的表上index反而影

b14231/15 manage tableTable Compression 用于只读表提高io性能，减少cacheNOLOGGING 用于create table as select /ctasParallelizing 用于ctas 多处理器的情形--临时表，对所有session可见，不过数据只对session可见CREATE GLOBAL TEMPORARY TABLE transaction-specific (the default) or session-specific,DELETE ROWS transaction-specificPRESERVE ROWS session-specific--事务级的临时表CREATE GLOBAL TEMPORARY TABLE admin_work_area(startdate DATE,enddate DATE,class CHAR(20))ON COMMIT DELETE ROWS;--transaction-specif

--create shcema 组合create table /ceate view /grant语句，可以回滚CREATE SCHEMA AUTHORIZATION scottCREATE TABLE dept (deptno NUMBER(3,0) PRIMARY KEY,dname VARCHAR2(15),loc VARCHAR2(25))CREATE TABLE emp (empno NUMBER(5,0) PRIMARY KEY,ename VARCHAR2(15) NOT NULL,job VARCHAR2(10),mgr NUMBER(5,0),hiredate DATE DEFAULT (sysdate),sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(3,0) NOT NULLCONSTRAINT dept_fkey REFERENCES dept)CREATE VIEW sales_staff ASSELECT empno, ename, sal, commFROM empWHERE deptno =

--asm automatic storage managementdisk group failure disk groupredundancy: normal(2 mirroring) high(3 mirroring) extent(unprotect)--disk failure rebalance 调整 ASM_POWER_LIMIT 参数来调整rebanlance的速度V$ASM_OPERATION --监控rebalance的速度ASM_DISKSTRING --/dev/rdsk/* /dev/rdsk/*s[34]ALTER DISKGROUP...MOUNTALTER DISKGROUP...ADD DISK% sqlplus /nologSQL> CONNECT / AS sysdbaConnected to an idle instance.SQL> STARTUPASM instance startedcrsctl check cssdcrsctl localconfigV$ASM_DISKGROUPV$ASM_DIS

--oracle managed filesDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_NDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST = '/u01/oradata'DB_RECOVERY_FILE_DEST_SIZE = 20GDB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'总结：oracle managed files多用在开发或者测试环境，管理方便。包括各种文件 data file,control file,redo log file,archive log,backup pieces.设置file dest；然后设置dest size限制，create file的时候只要制定路径即可，不需要命名（不过不建议这么自动命名，建议文件

--undo作用1、Roll back transactions when a ROLLBACK statement is issued2、Recover the database3、Provide read consistencyAnalyze data as of an earlier point in time by using Oracle Flashback Query4、Recover from logical corruptions using Oracle Flashback features--init 参数UNDO_MANAGEMENT auto/manualUNDO_TABLESPACE undotbs_1UNDO_RETENTION in secondsalter tablespace *** RETENTION GUARANTEE--Migrating to Automatic Undo ManagementDECLAREutbsiz_in_MB NUMBER;BEGINutbsiz_in_MB := DBMS_UNDO_ADV.RBU_