ITPub博客

InnoDB buffer pool instances:  Tune innodb_buffer_pool_instances (InnoDB Disk I/O) to increase read/write threads, and divide InnoDB buffer pool in regions for more performanceThe number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-g...

SELECT table_name, data_free * 100 / data_length , data_free, data_length FROM information_schema.TABLES WHERE table_schema='dbschemename'  Some delete often table will have much fragments, if it's too high and the table data is not too much, could defragment. For INNODB table do...

Configurations:# Set buffer pool size to 50-80% of your computer's memoryinnodb_buffer_pool_size=256Minnodb_additional_mem_pool_size=20M## Set the log file size to about 25% of the buffer pool sizeinnodb_log_file_size=64Minnodb_log_buffer_size=8Mmemory usageinnodb_buffer_pool_size+ k...

pager grep "lock(s)"show engine innodb status;

 4种事务隔离级别：读数据一致性及允许的并发副作用脏读幻读Read uncommitted）是是Read committed）否是Repeatable read）...

搞懂oracle字符集作为一个ORACLE DBA,在工作中会经常处理由于字符集产生的一些问题。但是当真正想写一些这方面的东西时，却突然又没有了头绪。发了半天呆，还是决定用两个字符集方面的例子作为切入点，倒不失为一个头绪，说不定在实验的过程中，问题就会一个接着一个的浮现出来。现在，让我们切入正题。我用的数据库是oracle10.2.0.3,数据库字符集是al32utf8。客户端就是同一台机器的windows xp.下面是演示的例子：SQL> drop table test purge;Table dropped.SQL> create table test(col1 number(1),col2 varchar2(10));Table creat

export: mysqldump database [table] > /tmp/filenameimport: mysql database < /tmp/filenamecan add "max_allowed_packet=104857600" under [mysqld] in /etc/my.cnf file to resolve the "Got a packet bigger than 'max_allowed_packet' bytes" problem.There is some problems with this import method.If the mysqldump version is low, the triggers and procedure can not be export. You can upgrade the mysqldump or use remote new version mysqldump to export.trigger is default, procedure nee

When there is no name-resolve, the mysql connection will be slow.Add 'skip-name-resolve' under '[mysqld]' section in /etc/my.cnf will resolve this problem.[@more@]

Oracle escape string only valid to "_", "%" and itself.For example, use "" as escape string,like 'abc%' escape '', will encounter oracle ORA-01424 errorSo when support the escape string, must take care.Should Transfer the single '' with other string.[@more@]

When using not in, not like, <>, not between in sql, you should pay attention, if the field is permit to null, the null value record can not be selected, you should add not null to let the null record shown. But the not exists is the except, it can select the null record, because the sql is always not exists(select field1 from table1 where table1.field1=table2.field1), if it table2's field1 is null, of course the equation is not true.[@more@]

The recyclebin is just a virtual storage area, a data dictionary. When drop table, the real space on the tablespace this table take up would not deleted in deed. But if the space is not enough, Oracle will delete the space auto.[@more@]

truncate 能释放表空间以及索引空间，但是前一次扩展的索引空间并不一定能记录到数据字典中， 故最好rebuild index.[@more@]

free space 大于db file的大小遇到一个奇怪的问题， db file free space的大小大于file的大小。导致在toad下看到的使用率为负的select sum(bytes) from dba_free_space where file_id=8;result: 35672489984 (这个大小已经达到了数据文件的max)select bytes from dba_data_files where file_id=8;result: 10171187200由于oracle bug 5083393 导致， solution:purge tablespace TS_NAME;exec dbms_space_admin.tablespace_rebuild_bitmaps('TS_NAME');[@more@]

When you use form in html, you can have two value "post" or "get" for method get: With the HTTP "get" method, the form data set is appended to the URI specified by the action attribute (with a question-mark ("?") as separator) and this new URI is sent to the processing agent. post: With the HTTP "post" method, the form data set is included in the body of the form and sent to the processing agent. If use get, your action is "test.html?test=1&

move oracle from '/opt' to '/disk'>shutdowncp all files;update environment.update pfile,change control files, background_dump_dest, core_dump_dest, user_dump_destsqlplus "/as sysdba"> startup mount pfile='new'>alter database rename file 'old' to 'new'>alter database openok[@more@]

1. Use the analytic function is the best way.row_number() over ( partition by col1 order by col2 )for example: get records between 100 and 150, order by tname.select tname,tabtype from ( select tname,tabtype,row_number() over ( order by tname ) rn from tab)where rn between 100 and 150;2. use rownumselect tname,tabtype from ( select tname,tabtype,rownum rn from tab where rownum <= 150)where rn >= 100;Use rownum, you can not order the whole records when using order by, it will get the f

When the argument list is too long, the rm command can not work, it is limit to os.solution 1:rm dir, recreate the dir.solution 2:find /path/to -name "*.png" -exec rm {} ;[@more@]

When you put .html file on the document root, put .pl& .pm file in a cgi-bin directory under the document root. The user can get the .pl & .pm file through http://host/documentroot/cgi-bin/aaa.pl, so we should add AddHandler cgi-script .pl orAddType application/x-httpd-cgi .pl .pm in apache configure file, then user request http://host/documentroot/cgi-bin/aaa.pl, it can not work, it must through ScriptAlias.[@more@]

1.create table TEST( num1 number(10), num2 number(10), num3 number(10))insert into TEST values(2,4,2).update TESTHUA set num1=num1+2,num2=num2+8,num3=num2/num1;select * from TESToracle's result:4,12,2when oracle execute num3,the value of num1 and num2 didn't change.mysql's result:4,12,3when mysql execute num3,the value of num1 and num2 changed already.num1 early than num2 because num1's position is bofore num2It means that oracle update the field data at the same time butthe mysql not,It update

oneselect A.NAME_1,B.NAME_2,C.NAME_3 from A left join B on A.ID=B.ID left join C on B.ID_1= C.ID_1;twoselect P.NAME_1,P.NAME_2,C.NAME_3 from (select A.NAME_1,B.NAME_2,B.ID_1 from A left join B on A.ID=B.ID) P left join C on P.ID_1= C.ID_1;[@more@]