Which two statements are true about InnoDB auto-increment locking? A. The auto-increment lock can be a table-level lock. B. InnoDB never uses table-level locks. C. Some settings for innodb_autoinc_lock_mode can help reduce locking. D. InnoDB always protects auto-increment updates with a table-level lock. E. InnoDB does not use locks to enforce auto-increment uniqueness.easy2hide start{reply_to_this=true}答案:ACeasy2hide end ...继续阅读 (88)

You are using replication and the binary log files on your master server consume a lot of disk space. Which two steps should you perform to safely remove some of the older binary log files? A. Ensure that none of the attached slaves are using any of the binary logs you want to delete. B. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files. C. Execute the PURGE BINARY LOGE NOT USED command. D. Remove all of the binary log files that have a modification date earlier than today. E. Edit the .index file to remove the files you want t ...继续阅读 (31)

You are using replication and the binary log files on your master server consume a lot of disk space. Which two steps should you perform to safely remove some of the older binary log files? A. Ensure that none of the attached slaves are using any of the binary logs you want to delete. B. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files. C. Execute the PURGE BINARY LOGE NOT USED command. D. Remove all of the binary log files that have a modification date earlier than today. E. Edit the .index file to remove the files you want t ...继续阅读 (35)

You have a login-path named “adamlocal” that was created by using the mysql_config_editor command. You need to check what is defined for this login_path to ensure that it is correct for you deployment. You execute this command: $ mysql_config_editor print –login-path=adamlocal What is the expected output of this command? A. The command prints all parameters for the login-path. The password is printed in plain text. B. The command prints all parameters for the login-path. The password is shown only when you provide the –password option. C. The command prints all parameter for the login-pat ...继续阅读 (39)

A database exists as a read-intensive server that is operating with query_cachek_type = DEMAND. The database is refreshed periodically, but the resultset size of the queries does not fluctuate. —-Note the following details about this environment: A web application uses a limited set of queries. The Query Cache hit rate is high. All resultsets fit into the Query Cache. All queries are configured to use the Query Cache successfully. The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent user ...继续阅读 (51)

Which query would you use to find connections that are in the same state for longer than 180 seconds? A. SHOW FULL PROCESSLIST WHEER Time > 180; B. SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) ); C. SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE < (DATE_SUB (NOW ( ), INTERVAL 180 SECOND) ); D. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 180; ...继续阅读 (59)

Consider the Mysql Enterprise Audit plugin. You are checking user accounts and attempt the following query: Mysql> SELECT user, host, plugin FROM mysql.users; ERROR 1146 (42S02): Table ‘mysql.users’ doesn’t exist Which subset of event attributes would indicate this error in the audit.log file? A. NAME=”Query” STATUS=”1146” SQLTEXT=”select user,host from users”/> B. NAME=”Error” STATUS=”1146” SQLTEXT=”Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/> C. NAME=”Query” STATUS=”1146” SQLTEXT=” Error 1146 (42S02): Table ‘mysql.users’ doesn’t exist”/> D. NAME=”Error” STATUS=”1146” ...继续阅读 (60)

Mysqldump was used to create a single schema backup; Shell> mysqldump –u root –p sakila > sakila2013.sql Which two commands will restore the sakila database without interfering with other running database? A. Mysql> USE sakila; LOAD DATA INFILE ‘sakila2013.sql’; B. Shell> mysql –u root –p sakila sakila2013.sql C. Shell> mysql import –u root –p sakila sakila2013.sql D. Shell> mysql –u root -p –e ‘use sakila; source sakila2013.sql’ E. Shell> mysql –u root –p –silent < sakila2013.sql ...继续阅读 (31)

ROW-based replication has stopped working. You investigate the error log file and find the following entries: 2013-08-27 14:15:47 9056 [ERROR] Slave SQL: Could not execute Delete_rows event on table test.t1; Can’t find record in ‘t1’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log 56_master-bin. 000003, end_log_pos 851, Error_code: 1032 2013-08-27 14:15:47 9056 [warning] Slave: Can’t find record in ‘t1’ Error_code: 1032 2013-08-27 14:15:47 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE STA ...继续阅读 (25)

When designing an InnoDB table, identify an advantage of using the BIT datatype Instead of one of the integer datatypes. A. BIT columns are written by InnoDB at the head of the row, meaning they are always the first to be retrieved. B. Multiple BIT columns pack tightly into a row, using less space. C. BIT (8) takes less space than eight TINYINT fields. D. The BIT columns can be manipulated with the bitwise operators &, |, ~, ^, < <, and >>. The other integer types cannot. ...继续阅读 (42)

You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are notified that users are getting the following error: mysql> CALL film_in_stock (40, 2, @count); ERROR 1449 (HY000): The user specified as a definer (‘bon’@’localhost’) does not exist How would you identify all stored procedures that pose the same problem? A. Execute SELECT * FROM mysql.routines WHERE DEFINER=’bob@localhost’;. B. Execute SHOW ROUTINES WHERE DEFINER=’bob@localhost’. C. Execute SELECT * FROM INFORMATION_SCHEMA. ROUTINES WHERE DEFINER=’bob@localhost’;. D. Execute SELECT * FROM INFORMA ...继续阅读 (47)

Consider the following statement on a RANGE partitioned table: ALTER TABLE orders DROP PARTITION p1, p3; What is the outcome of executing the above statement? A. Only the first partition (p1) will be dropped as only one can be dropped at any time. B. All data in p1 and p3 partitions are removed, but the table definition remains unchanged. C. A syntax error will result as you cannot specify more than one partition in the same statement. D. All data in pi and p3 partitions are removed and the table definition is changed. ...继续阅读 (42)

A simple master-to-slave replication is currently being used. The following information is extracted from the SHOW SLAVE STATUS output: Last_SQL_Error: Error ‘Duplicate entry ‘8’ for key ‘PRIMARY’ ‘ on query. Default database: ‘mydb’. Query: ‘insert into mytable VALUES (‘8’ , ‘George’) ‘ Skip_Counter: 0 Retrieved _Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8 Auto _Position: 1 You execute a “SHOW CREATE TABLE mytable” on the slave: CREATE TABLE ‘mytable’ ( ‘ID’ int(11) NOT NULL DEFAULT ‘0’, ‘name’ char(10) DEFAULT NULL, PRIMARY KEY (‘ID’) ) The table mytable on the slave contains the ...继续阅读 (31)

A simple master-to-slave replication is currently being used. The following information is extracted from the SHOW SLAVE STATUS output: Last_SQL_Error: Error ‘Duplicate entry ‘8’ for key ‘PRIMARY’ ‘ on query. Default database: ‘mydb’. Query: ‘insert into mytable VALUES (‘8’ , ‘George’) ‘ Skip_Counter: 0 Retrieved _Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8 Auto _Position: 1 You execute a “SHOW CREATE TABLE mytable” on the slave: CREATE TABLE ‘mytable’ ( ‘ID’ int(11) NOT NULL DEFAULT ‘0’, ‘name’ char(10) DEFAULT NULL, PRIMARY KEY (‘ID’) ) The table mytable on the slave contains the ...继续阅读 (34)

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如：explainselectsurname,first_name form a,bwherea.id=b.idEXPLAIN列的解释：table：显示这一行的数据是关于哪张表的 type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引 key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好 ref：显示索引的哪一列被使用了，如果可能的话，是一个常数 rows：MYSQL认为必须检查的用来返回请求数据的行数 Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论， ...继续阅读 (26)

processlist命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句，两种方式使用这个命令。 1.      进入mysql/bin目录下输入mysqladmin processlist; 2.      启动mysql，输入show processlist; 如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。 mysql> show processlist -> ; +----+-------+----------------------+------+---------+------+-------+------------------+ | Id | User  | Host                 | db   | Command | Time | State | Info             | +----+-------+----------------------+------+---------+------+-------+------------------+ | 34 | ucjmh | 192.168.56.1 ...继续阅读 (39)

mysql> create table users(num int not null, id varchar(30) not null, password varchar(30) not null, primary key(num)); Query OK, 0 rows affected (0.00 sec) mysql> insert into users values(1, 'admin', 'ad1234'); Query OK, 1 row affected (0.00 sec) mysql> insert into users values(2, 'wh1ant', 'wh1234'); Query OK, 1 row affected (0.00 sec) mysql> insert into users values(3, 'secuholic', 'se1234'); Query OK, 1 row affected (0.00 sec) mysql> select * from users where id=0; +-----+-----------+----------+ | num | id        | password | +-----+------- ...继续阅读 (28)

今天在itpub看到一个问题 说是使用dbms_stats.import_table_stats并不会导入统计信息 然后做了一个实验如下 SQL> create table test as select * from dba_objects; Table created. SQL> create index idx_test on test(object_id); Index created. SQL> begin dbms_stats.gather_table_stats(ownname          => 'SCOTT', tabname          => 'TEST', estimate_percent => 100, degree           => 2, cascade          => true); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> create table test_temp as select * from test; Table created. SQL> create index idx_test_temp on ...继续阅读 (17)

今天在itpub看到一个问题 说是使用dbms_stats.import_table_stats并不会导入统计信息 然后做了一个实验如下 SQL> create table test as select * from dba_objects; Table created. SQL> create index idx_test on test(object_id); Index created. SQL> begin dbms_stats.gather_table_stats(ownname          => 'SCOTT', tabname          => 'TEST', estimate_percent => 100, degree           => 2, cascade          => true); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> create table test_temp as select * from test; Table created. SQL> create index idx_test_temp on ...继续阅读 (26)

oracle11g推出以后有了一种新的LOBS的存储模式叫做SecureFile LOBs 与之区别 把以前使用的叫做basicFile Lobs 根据官方文档上的说法 secure是哪哪都好。提供了压缩 重复消除 加密等新的功能 但是basicFile Lobs依然是default的 secureFime的用法是通过在LOB存储子句后添加SECUREFILE关键字来创建 最简单的一种写法： SQL> CREATE TABLE images ( 2 id NUMBER, 3 i_data CLOB 4 ) 5 LOB(i_data) STORE AS BASICFILE; Table created SQL> CREATE TABLE images2 (id NUMBER, 2 i_data CLOB 3 ) 4 LOB(i_data) STORE AS SECUREFILE; Table created. ——————————————————————————— Securefile列标明了是否为SecureFile类型的LOB SQL> SELECT TABLE_NAME,SEGMENT_NAME,INDEX_NAME,SECUREFILE FROM DBA_LOBS WHERE TABLE ...继续阅读 (128)

oracle11g推出以后有了一种新的LOBS的存储模式叫做SecureFile LOBs 与之区别 把以前使用的叫做basicFile Lobs 根据官方文档上的说法 secure是哪哪都好。提供了压缩 重复消除 加密等新的功能 但是basicFile Lobs依然是default的 secureFime的用法是通过在LOB存储子句后添加SECUREFILE关键字来创建 最简单的一种写法： SQL> CREATE TABLE images ( 2 id NUMBER, 3 i_data CLOB 4 ) 5 LOB(i_data) STORE AS BASICFILE; Table created SQL> CREATE TABLE images2 (id NUMBER, 2 i_data CLOB 3 ) 4 LOB(i_data) STORE AS SECUREFILE; Table created. ——————————————————————————— Securefile列标明了是否为SecureFile类型的LOB SQL> SELECT TABLE_NAME,SEGMENT_NAME,INDEX_NAME,SECUREFILE FROM DBA_LOBS WHERE TABLE ...继续阅读 (93)

[oracle@ucjmh ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 09:01:40 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set serveroutput on SQL> DECLARE — declare fixed array TYPE arry_num IS VARRAY(10) OF NUMBER; arry_top arry_num; v_n int; BEGIN — init array arry_top := arry_num(1,2,3); dbms_output.put_line(arry_top.LIMIT()); dbms_output.put_line(arry_top.count ...继续阅读 (34)

[oracle@ucjmh ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 09:01:40 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set serveroutput on SQL> DECLARE — declare fixed array TYPE arry_num IS VARRAY(10) OF NUMBER; arry_top arry_num; v_n int; BEGIN — init array arry_top := arry_num(1,2,3); dbms_output.put_line(arry_top.LIMIT()); dbms_output.put_line(arry_top.count ...继续阅读 (35)

早晨在大阪工作的学长发信息过来要想用SQL实现对日本邮政编码分类的规则.本来学长只需要做概要设计即可,详细设计和开发都是外包给越南人做,但是担心越南的童鞋方法不能够满意,索性就直接把关键的步骤都自己实现掉,想想也是为了世界和平做了不少贡献; 规则是这样的: 1.日本邮编是7位的; 2.最终的结果集只需要两列(前缀 个数); 3.第一优先级是尾数为[00]的邮政编码; 4.第二优先级是前5位数字相同的邮政编码,只展现出现次数大于10次的结果,如果不足10次的邮政编码进入下一优先级统计; 5.第三优先级是前4位数字相同的邮政编码,规则同上; 6.第四优先级是前3位数字相同的邮政编码,规则同上; 7.第五优先级是剩余其它的邮政编码; -- 测试的数据,生成20W个邮政编码; DROP TABLE TBPOSTTEST; CREATE TABLE TBPOSTTEST AS SELECT LPAD(ROUND(DBMS_RANDOM.VALUE (1, 9999999)) , 7, 0) postcode FROM DUAL CONNECT BY LEVEL <= 200000; -- 实现的sql语句; SELECT PREFIX, COUNT FROM ( WITH P0P0 AS (SELECT '____00' AS "PREFIX", COUNT(*) c0 FRO ...继续阅读 (22)

早晨在大阪工作的学长发信息过来要想用SQL实现对日本邮政编码分类的规则.本来学长只需要做概要设计即可,详细设计和开发都是外包给越南人做,但是担心越南的童鞋方法不能够满意,索性就直接把关键的步骤都自己实现掉,想想也是为了世界和平做了不少贡献; 规则是这样的: 1.日本邮编是7位的; 2.最终的结果集只需要两列(前缀 个数); 3.第一优先级是尾数为[00]的邮政编码; 4.第二优先级是前5位数字相同的邮政编码,只展现出现次数大于10次的结果,如果不足10次的邮政编码进入下一优先级统计; 5.第三优先级是前4位数字相同的邮政编码,规则同上; 6.第四优先级是前3位数字相同的邮政编码,规则同上; 7.第五优先级是剩余其它的邮政编码; -- 测试的数据,生成20W个邮政编码; DROP TABLE TBPOSTTEST; CREATE TABLE TBPOSTTEST AS SELECT LPAD(ROUND(DBMS_RANDOM.VALUE (1, 9999999)) , 7, 0) postcode FROM DUAL CONNECT BY LEVEL <= 200000; -- 实现的sql语句; SELECT PREFIX, COUNT FROM ( WITH P0P0 AS (SELECT '____00' AS "PREFIX", COUNT(*) c0 FRO ...继续阅读 (33)

PROMPT CREATE OR REPLACE PACKAGE pkg_session CREATE OR REPLACE PACKAGE pkg_session IS TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30); PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2); PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER); PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE); FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ; FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER; FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE; PRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS); PRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS); PRAGMA RES ...继续阅读 (26)

PROMPT CREATE OR REPLACE PACKAGE pkg_session CREATE OR REPLACE PACKAGE pkg_session IS TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30); PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2); PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER); PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE); FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ; FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER; FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE; PRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS); PRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS); PRAGMA RES ...继续阅读 (25)

在itpub里看到一个很有意思的sql 一个表中记录了各个地点的报警开始时间，结束时间。 现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。 要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。 create table T_DEMO ( id VARCHAR2(32) not null, place_id VARCHAR2(32), s_time DATE, e_time DATE )； insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('1', '1', to_date('21-08-2014 01:24:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:24:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('2', '1', to_date('21-08-2014 01:25:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:25:01', ...继续阅读 (26)

在itpub里看到一个很有意思的sql 一个表中记录了各个地点的报警开始时间，结束时间。 现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。 要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。 create table T_DEMO ( id VARCHAR2(32) not null, place_id VARCHAR2(32), s_time DATE, e_time DATE )； insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('1', '1', to_date('21-08-2014 01:24:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:24:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('2', '1', to_date('21-08-2014 01:25:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:25:01', ...继续阅读 (18)

Oracle数据库中表数据导出为TXT格式使用SPOOL命令把结果输出到文本中;使用编写的C语言的脚本;------------------------------- 使用SPOOL命令把结果输出到文本中 --------------------------------- 1.编写脚本为/tmp/orauldr.sql;set echo on            -- 是否显示执行的命令内容 set feedback off       -- 是否显示提示符,即多少条记录打一个点 set heading off        -- 是否显示字段的名称 set verify off         -- 是否显示替代变量被替代前后的语句 set trimspool off      -- 去字段空格 set pagesize 1000      -- 页面大小 set linesize 50        -- linesize设定尽量根据需要来设定，大了生成的文件也大 define fil= '/tmp/exp.txt' prompt *** Spooling to &fil spool &fil select col1 || ',' || username || ',' || ',' ...继续阅读 (31)