Category Archives: DB LIFE
MySQL由浅入深–更新ing
Enjoy your Study Time!
版权@TracyLLing
MySQL AUTO_INCREMENT
AUTO_INCREMENT属性
1.每张表只能有一个数据列为自动增长列,且必须是索引(常使用PRIMARY KEY或UNIQUE索引,但非唯一索引也ok)。
2.必须有NOT NULL约束条件(即使没有明确声明,MySQL会自动把该数据列设置为NOT NULL)。
3.最近生成的序号值可以通过调用LAST_INSERT_ID()函数获得,可以在后续的语句里引用AUTO_INCREMENT值。
4.可以通过插入一个序列号值大于当前计数器值的数据行的方法让计数器跳过一个区间。
MyISAM中的AUTO_INCREMENT
1.MyISAM序列默认从1开始编号,或者通过在create table语句中的AUTO_INCREMENT=n来设置初始值。最多只能有一个AUTO_INCREMENT数据列。 Continue reading
Day Day UP
1. Linux 包括基本shell、命令、vim等(《鸟哥Linux私房菜》),还包括基本操作系统原理(《现代操作系统》《深入理解计算机系统》)
2. C++(《C++ Primer》《Effective C++》《编程之美》)
3. Python(《Python学习手册》《Python核心编程》)
4. MySQL(《高性能MySQL》,MySQL5.6参考手册, InnoDB Plugin参考手册)
5. 数据挖掘(《数据挖掘》)
6. 分布式(Google的核心论文,leveldb|Hadoop,MapReduce)
7. 经济学原理第5版
8. 英语口语以及词汇
9. 名著一月一本
10. 象棋|围棋
11. 识地图
Python连接oracle的几种方式
- 基本连接–使用Oracle tns alias
connection = cx_Oracle.connect("tp/tp@ocn_test") #查看tns alias命令 cmd>tnsping ocn_test TNS Ping Utility for Linux: Version 9.2.0.8.0 - Production on 27-SEP-2011 10:47:48 Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved. Used parameter files: /opt/……/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.36.19)(PORT = 1520))) (CONNECT_DATA = (SID = ocntest))) OK (10 msec)
- 用户输入密码连接
pwd = getpass.getpass() connection = cx_Oracle.connect("tp",pwd,"ocn_test")
- 用户直接在Python命令中输入连接账号信息,格式如python script.py tp/tp@ocn_test
connection = cx_Oracle.connect(sys.argv[1])
- 使用Easy Connect语法,通过Drive连接数据库
connection = cx_Oracle.connect('tp','tp','10.20.36.19:1521/ocntest') #or connection = cx_Oracle.connect('tp/tp@10.20.36.19:1521/ocntest')
- 先使用DSN构成TNSNAME
tns_name = cx_Oracle.makedsn('10.20.36.19','1521',' ocntest ') connection = cx_Oracle.connect('tp','tp',tns_name)
- 登陆as SYSDBA
connection = cx_Oracle.connect('tp/tp@ocn_test', mode=cx_Oracle.SYSDBA) #or as SYSOPER connection = cx_Oracle.connect('tp/tp@ocn_test', mode=cx_Oracle.SYSOPER)
Linux变量
1. 按照生存周期来划分可以将Linux变量分为:
▪ 永久的:需要修改配置文件/etc/profile
▪ 临时的:直接使用export声明即可,在关闭shell后失效
2. 变量设置:
▪ 在/etc/profile文件中添加变量–对所有用户永久生效
# vi /etc/profile export CLASSPATH=./JAVA_HOME/lib;$JAVA_HOME/jre/lib
注:修改文件后如需立即生效需运行# source /etc/profile Continue reading
python-oracle9i单表访问SQL统计信息
#!/usr/bin/env python # -*- encoding: utf8 -*- # Author: tracylling@gmail.cn # Created: 2011-8-16 import cx_Oracle import os import sys import re os.environ['NLS_LANG'] ='AMERICAN_AMERICA.ZHS16GBK' #input format : python oracle9i_liling.py -t table_name -o owner >> file_path/file_name.txt OWNER = sys.argv[4] TABLE_NAME = sys.argv[2] TABLE_NAME1 = TABLE_NAME.upper() OWNER1 = OWNER.upper() print("------------Welcome To Oracle 9i Statistics Tools----------------") print("") #--login--# def db_connect(): try: conn = cx_Oracle.connect('tp/tp@ocn_test') return conn except cx_Oracle.DatabaseError,exc: error,=exc.args print "db_connect error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--table information--# def table_info(cursor): try: cursor.execute(""" select owner,segment_name,bytes / 1024 / 1024 From dba_segments where segment_name = :table_name and segment_type = 'TABLE' and owner = :owner """,table_name = TABLE_NAME1,owner = OWNER1) for column_1,column_2,column_3 in cursor.fetchall(): print "*********************Table Info*********************" print "Version Table Name Owner Name Size(MB)" print "-"*52 print "%s %12s %16s %12d" %(conn.version,TABLE_NAME1,OWNER1,column_3) except cx_Oracle.DatabaseError, exc: error, = exc.args print "table_info error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--index infomation--# def index_info(cursor): try: print "" print "***********Index Info***********" print "INDEX_NAME SIZE(MB)" print "-"*30 cursor.execute(""" select owner,segment_name, bytes / 1024 /1024 From dba_segments where segment_name IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME = :table_name) and segment_type = 'INDEX' and owner = :owner """,table_name = TABLE_NAME1,owner = OWNER1) for column_1,column_2,column_3 in cursor.fetchall(): print "%-25s %d" % (column_2,column_3) except cx_Oracle.DatabaseError, exc: error, = exc.args print "index_info error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--SQL--# def sql_join(cursor): dict = {} #存放SQL连接并归一化后结果 dict_table = {} #存放全词匹配表名的SQL语句 count = {} #存放归一化语句条数,做除数 match_join='(\s*:[A-Za-z0-9]+\s*,?\s*)+' match_table = '[^A-Za-z0-9_]' + TABLE_NAME + '[^A-Za-z0-9_]' rx = re.compile(match_table,re.IGNORECASE) print "" print "***********SQL EXECUTIONS Info***********" try: #print "start" cursor.execute(""" select distinct hash_value From stats$sqltext """) hash_val = cursor.fetchall() for i in hash_val: cursor.execute(""" select sql_text from stats$sqltext where hash_value = :hash_value order by piece """,i) sql_piece = cursor.fetchall() sql_text = '' for j in sql_piece : sql_text = sql_text + "%s" %(j) sql_text = sql_text.replace('\n\r',' ') sql_text = " ".join(sql_text.split()) #SQL语句拼接并去除多余的空格 if rx.search(sql_text) is not None: #匹配表名,存储进映射KV关系 k是hash_val,v是对应完整的SQLTEXT dict[i] = sql_text.upper() for key in dict.keys(): dict[key] = re.sub(match_join,' :1 ',dict[key]) #归一化 #print dict[key] cursor.execute("""SELECT hash_value,case when max_exec > min_exec and min_exec > 0 then trunc((max_exec - min_exec) / decode(trunc(max_time)-trunc(min_time),0,1,trunc(max_time)-trunc(min_time))) when max_exec = min_exec and min_exec > 0 then max_exec else 0 end as exec_daily, trunc(max_time)-trunc(min_time) diff_days, to_char(min_time, 'yyyy-mm-dd') as begin_snap, to_char(max_time, 'yyyy-mm-dd') as end_snap fROM (select A.*, row_number() OVER(partition by hash_value order by max_time desc) rn from (select s.hash_value, min(t.snap_time) over(partition by hash_value order by s.snap_id) as min_time, max(t.snap_time) over(partition by hash_value order by s.snap_id) as max_time, min(s.executions) over(partition by hash_value order by s.snap_id) as min_exec, max(s.executions) over(partition by hash_value order by s.snap_id) as max_exec from stats$sql_summary s, stats$snapshot t where s.snap_id = t.snap_id and s.Hash_value = :arg) a)B WHERE B.RN =1 """,key) executions = cursor.fetchall() #print executions for line in executions: sql_hash_val = line[0] exec_daily = line[1] snap_time = line[4] if exec_daily is not None: if dict_table.has_key(dict[key]): dict_table[dict[key]] = dict_table[dict[key]] + exec_daily count[dict[key]] = count[dict[key]] + 1 else : dict_table[dict[key]] = exec_daily count[dict[key]] = 1 #else: #exec_daily = 0 print "" for keys in dict_table: dict_table[keys] = dict_table[keys]/count[keys] #print count[keys] #print dict_table[keys] for i in sorted(dict_table.items(),key=lambda e:e[1],reverse=True): print "** SQL EXECUTIONS: %d **" %i[1] print "%s" %i[0] print "" except cx_Oracle.DatabaseError, exc: error, = exc.args print "sql_join error" print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message #--main--# def main(cursor): db_table_info = table_info(cursor) db_index_info = index_info(cursor) db_sql_join = sql_join(cursor) if __name__ == '__main__': conn = db_connect() cursor = conn.cursor() main(cursor)
python中的一些格式
#一般循环格式: while <test1>: <statements1> if <test2> : break #Exit loop now,skip else if <test3> : continue #Go to top of loop now,to test1 else : <statements2> #Run if we didn't hit a 'break' #for循环格式: for <target> in <object> : <statements> else : <statements>
Python&&cx_Oracle
Python版本:python2.7.2
Instantclient版本:Version 10.2.0.4
cx_Oracle版本:cx_Oracle-5.0.1
下载软件及工程目录:/home/oracle/LL/python/software(/workstation)
1. 查看系统版本选择合适版本
uname -a
Linux inc-dba-ccbu-36-18 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:56:44 EST 2007 x86_64 x86_64 x86_64 GNU/Linux
2. 下载安装python2.7.2 – 稳定版
# wget http://www.python.org/ftp/python/2.7.1/Python-2.7.1.tar.bz2
# tar -jxvf Python-2.7.1.tar.bz2
# cd Python-2.7.1
# ./configure (默认安装在/usr/local/lib/python2.7,–prefix可指定)
# make && make install