分类目录归档:数据

ORA-01439:要更改数据类型,则要修改的列必须为空

假设字段有数据,则改为varchar2(40)执行时会弹出:“ORA-01439:要更改数据类型,则要修改的列必须为空”,这时要用下面方法来解决这个问题:

/*修改原字段名name为name_tmp*/
alter table tb rename column name to name_tmp;
/*增加一个和原字段名同名的字段name*/
alter table tb add name varchar2(40);
/*将原字段name_tmp数据更新到增加的字段name*/
update tb set name=trim(name_tmp);
/*更新完,删除原字段name_tmp*/
alter table tb drop column name_tmp;

总结:
1、当字段没有数据或者要修改的新类型和原类型兼容时,可以直接modify修改。
2、当字段有数据并用要修改的新类型和原类型不兼容时,要间接新建字段来转移。

ORACLE常用性能监控SQL

Temp表空间上进程的查询

1
2
3
4
5
select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text
  from v$sort_usage a,v$session b,v$sqltext c
 where a.session_addr = b.saddr
   and b.sql_address = c.address
 order by a.tablespace,b.sid,b.serial#,c.address, c.piece;

查看表锁

1
select * from sys.v_$sqlarea where disk_reads>100;

监控事例的等待

1
2
3
4
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4 ;

回滚段的争用情况

1
2
3
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

查看前台正在发出的SQL语句

1
2
3
4
5
select user_name,sql_text
   from v$open_cursor
   where sid in (select sid from (select sid,serial#,username,program
   from v$session
   where status='ACTIVE'));

数据表占用空间大小情况

1
2
3
4
select segment_name,tablespace_name,bytes,blocks
from user_segments
where segment_type='TABLE'
ORDER BY bytes DESC ,blocks DESC;

查看表空间碎片大小

1
2
3
4
select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*
          (100/sqrt(sqrt(count(blocks)))),2) FSFI
   from dba_free_space
   group by tablespace_name order by 1;

查看表空间占用磁盘情况

1
2
3
4
5
6
7
8
9
10
11
select
         b.file_id                                 文件ID号,
         b.tablespace_name                         表空间名,
         b.bytes                                 字节数,
         (b.bytes-sum(nvl(a.bytes,0)))                 已使用,
         sum(nvl(a.bytes,0))                         剩余空间,
         sum(nvl(a.bytes,0))/(b.bytes)*100         剩余百分比
         from dba_free_space a,dba_data_files b
         where a.file_id=b.file_id
         group by b.tablespace_name,b.file_id,b.bytes
         order by b.file_id;

查看Oracle 表空间使用率

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT D.TABLESPACE_NAME, 
       SPACE || 'M' "SUM_SPACE(M)"
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)"
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
          "USED_RATE(%)"
       FREE_SPACE || 'M' "FREE_SPACE(M)"
  FROM SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE
                 SUM (BLOCKS) BLOCKS 
            FROM DBA_DATA_FILES 
        GROUP BY TABLESPACE_NAME) D, 
       SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
            FROM DBA_FREE_SPACE 
        GROUP BY TABLESPACE_NAME) F 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL                                                           --如果有临时表空间 
SELECT D.TABLESPACE_NAME, 
       SPACE || 'M' "SUM_SPACE(M)"
       USED_SPACE || 'M' "USED_SPACE(M)"
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)"
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
  FROM SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE
                 SUM (BLOCKS) BLOCKS 
            FROM DBA_TEMP_FILES 
        GROUP BY TABLESPACE_NAME) D, 
       SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
            FROM V$TEMP_SPACE_HEADER 
        GROUP BY TABLESPACE_NAME) F 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
ORDER BY 1;

查看Temp 表空间实际使用磁盘大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Select f.tablespace_name,
       d.file_name "Tempfile name",
       round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
       round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,
             2) "Free MB",
       round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",
       round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /
             round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
             2) as "Used_Rate(%)"
  from SYS.V_$TEMP_SPACE_HEADER f,
       DBA_TEMP_FILES           d,
       SYS.V_$TEMP_EXTENT_POOL  p
 where f.tablespace_name(+) = d.tablespace_name
   and f.file_id(+) = d.file_id
   and p.file_id(+) = d.file_id;

查看session使用回滚段

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  r.name 回滚段名,
        s.sid,
        s.serial#,
        s.username 用户名,
        t.status,
        t.cr_get,
        t.phy_io,
        t.used_ublk,
        t.noundo,
        substr(s.program, 1, 78) 操作程序
FROM   sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE  t.addr = s.taddr and t.xidusn = r.usn
ORDER  BY t.cr_get,t.phy_io;

查看SGA区剩余可用内存

1
2
3
4
5
6
select name,
      sgasize/1024/1024        "Allocated(M)",
      bytes/1024            "**空间(K)",
      round(bytes/sgasize*100, 2)   "**空间百分比(%)"
   from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
   where  f.name = 'free memory';

–监控表空间I/O比例
select df.tablespace_name name,df.file_name “file”,f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;

监控SGA命中率

1
2
3
4
5
6
7
select a.value + b.value "logical_reads",
       c.value "phys_reads",
       round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and
      b.statistic# = 39 and
      c.statistic# = 40 ;

监控 SGA 中字典缓冲区的命中率

1
2
3
4
5
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;

监控 SGA **享缓存区的命中率,应该小于1%

1
2
3
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

监控 SGA 中重做日志缓存区的命中率,应该小于1%

1
2
3
4
5
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

监控内存和硬盘的排序比率,最好使它小于 .10

1
2
3
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)') ;

监控字典缓冲区

1
2
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE ;

系统用户建在system表空间中的表”>非系统用户建在SYSTEM表空间中的表

1
2
3
4
SELECT owner,table_name
FROM DBA_TABLES
WHERE tablespace_name in('SYSTEM','USER_DATA') AND
      owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC');

性能最差的SQL

1
2
3
4
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
                FROM v$sqlarea
                ORDER BY disk_reads DESC)
WHERE ROWNUM<100;

读磁盘数超100次的sql

1
select * from sys.v_$sqlarea where disk_reads>100;

最频繁执行的sql

1
select * from sys.v_$sqlarea where executions>100;

查询使用CPU多的用户session

1
2
3
4
5
6
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
      c.sid=a.sid and
      a.paddr=b.addr
order by value desc;

当前每个会话使用的对象数

1
2
3
4
5
SELECT a.sid,s.terminal,s.program,count(a.sid)
FROM V$ACCESS a,V$SESSION s
WHERE a.owner <> 'SYS'AND s.sid = a.sid
GROUP BY a.sid,s.terminal,s.program
ORDER BY count(a.sid) ;

CentOS7 64位下MySQL5.7安装与配置(YUM)

安装环境:CentOS7 64位 MINI版,安装MySQL5.7

1、配置YUM源

在MySQL官网中下载YUM源rpm安装包:http://dev.mysql.com/downloads/repo/yum/
MySQL YUM源下载地址

# 下载mysql源安装包
shell> wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
# 安装mysql源
shell> yum localinstall mysql57-community-release-el7-8.noarch.rpm

检查mysql源是否安装成功

shell> yum repolist enabled | grep "mysql.*-community.*"

检查mysql源安装是否正确
看到上图所示表示安装成功。
可以修改vim /etc/yum.repos.d/mysql-community.repo源,改变默认安装的mysql版本。比如要安装5.6版本,将5.7源的enabled=1改成enabled=0。然后再将5.6源的enabled=0改成enabled=1即可。改完之后的效果如下所示:
这里写图片描述

2、安装MySQL

shell> yum install mysql-community-server

3、启动MySQL服务

shell> systemctl start mysqld

查看MySQL的启动状态

shell> systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since 五 2016-06-24 04:37:37 CST; 35min ago
 Main PID: 2888 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2888 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

624 04:37:36 localhost.localdomain systemd[1]: Starting MySQL Server...
624 04:37:37 localhost.localdomain systemd[1]: Started MySQL Server.

4、开机启动

shell> systemctl enable mysqld
shell> systemctl daemon-reload

5、修改root本地登录密码

mysql安装完成之后,在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码,然后登录mysql进行修改:

shell> grep 'temporary password' /var/log/mysqld.log

root默认密码

shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 

或者

mysql> set password for 'root'@'localhost'=password('MyNewPass4!'); 

注意:mysql5.7默认安装了密码安全检查插件(validate_password),默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号,并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误,如下图所示:
密码策略提示

通过msyql环境变量可以查看密码策略的相关信息:

mysql> show variables like '%password%';

mysql密码策略
validate_password_policy:密码策略,默认为MEDIUM策略
validate_password_dictionary_file:密码策略文件,策略为STRONG才需要
validate_password_length:密码最少长度
validate_password_mixed_case_count:大小写字符长度,至少1个
validate_password_number_count :数字至少1个
validate_password_special_char_count:特殊字符至少1个
上述参数是默认策略MEDIUM的密码检查规则。

共有以下几种密码策略:

策略 检查规则
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

MySQL官网密码策略详细说明:http://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html#sysvar_validate_password_policy

修改密码策略

在/etc/my.cnf文件添加validate_password_policy配置,指定密码策略

# 选择0(LOW),1(MEDIUM),2(STRONG)其中一种,选择2需要提供密码字典文件
validate_password_policy=0

如果不需要密码策略,添加my.cnf文件中添加如下配置禁用即可:

validate_password = off

重新启动mysql服务使配置生效:

systemctl restart mysqld

6、添加远程登录用户

默认只允许root帐户在本地登录,如果要在其它机器上连接mysql,必须修改root允许远程连接,或者添加一个允许远程连接的帐户,为了安全起见,我添加一个新的帐户:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'yangxin'@'%' IDENTIFIED BY 'Yangxin0917!' WITH GRANT OPTION;

7、配置默认编码为utf8

修改/etc/my.cnf配置文件,在[mysqld]下添加编码配置,如下所示:

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'

重新启动mysql服务,查看数据库默认编码如下所示:

mysql默认编码


默认配置文件路径:
配置文件:/etc/my.cnf
日志文件:/var/log//var/log/mysqld.log
服务启动脚本:/usr/lib/systemd/system/mysqld.service
socket文件:/var/run/mysqld/mysqld.pid

centos7.2 源码安装mariadb10.1

安装所需准备

安装mariadb 需要cmake 和依赖包readline-devel,zlib-devel,openssl-devel,libaio-devel readline-devel 还是先用yum工具安装一下吧

yum -y install readline-devel
yum -y install zlib-devel
yum -y install openssl-devel
yum -y install libaio-devel
yum -y install cmake

创建mysql用户和安装目录,数据存放目录

groupadd -r mysql#创建mysql用户组
useradd -g mysql -s /sbin/nologin mysql#创建mysql用户归属于mysql组
mkdir /usr/local/mysql#创建mariadb安装目录
mkdir -p /data/db #创建数据存储目录
chown -R mysql:mysql /data/db/ #给予权限

下载mariadb源码包

下载地址:https://mirrors.tuna.tsinghua.edu.cn/mariadb//mariadb-10.1.21/source/mariadb-10.1.21.tar.gz 其他版本
使用wget下载:

wget https://mirrors.tuna.tsinghua.edu.cn/mariadb//mariadb-10.1.21/source/mariadb-10.1.21.tar.gz 

或者使用桌面环境下载了用ssh上传到linux(我使用的腾讯云服务器,只有1mbps的小水管,下载非常慢,但是我发现使用ssh上传可以达到6mbps的网速,比直接在服务器上下载快多了)

开始安装

解压源码包

tar -xf mariadb-10.1.21/source/mariadb-10.1.21.tar.gz

进入源码包目录

cd mariadb-10.1.21

编译源码包

cmake cmake  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/db -DSYSCONFDIR=/etc -DWITHOUT_TOKUDB=1 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
  • -DCMAKE_INSTALL_PREFIX是指定安装的位置,这里是/usr/local/mysql
  • -DMYSQL_DATADIR是指定MySQL的数据目录,这里是/data/db
  • -DSYSCONFDIR是指定配置文件所在的目录,一般都是/etc,具体的配置文件是/etc/my.cnf,-DWITHOUT_TOKUDB=1这个参数一般都要设置上,表示不安装tokudb引擎,tokudb是MySQL中一款开源的存储引擎,可以管理大量数据并且有一些新的特性,这些是Innodb所不具备的,这里之所以不安装,是因为一般计算机默认是没有Percona Server的,并且加载tokudb还要依赖jemalloc内存优化,一般开发中也是不用tokudb的,所以暂时屏蔽掉,否则在系统中找不到依赖会出现:CMake Error at storage/tokudb/PerconaFT/cmake_modules/TokuSetupCompiler.cmake:179 (message)这样的错误
  • -DDEFAULT_CHARSET 字符集,这里是utf-8
  • -DDEFAULT_COLLATION排序规则,这里是utf8_general_ci
    如果编译遇到错误使用rm -f CMakeCache.txt删除编译缓存后尝试重新编译

安装

make && make install

完成安装

cd /usr/local/mysql/#进入mariadb安装目录
chown -R mysql:mysql . #给予mysql:mysql权限
scripts/mysql_install_db --datadir=/data/db --user=mysql #初始化系统表
cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld   # 添加至系统服务
chkconfig mysqld on    # 设置开机自启动
export PATH=/usr/local/mysql/bin:$PATH #导出环境变量

安装完成后在安装目录下的support-file目录下有my-small.cnf、my-medium.cnf、my-large.cnf以及my-huge.cnf,这是示例的配置文件,可以把他们替换到etc下的my.cnf再做适当修改

cp -f /usr/local/mysql/support-file/my-small.cnf /etc/my.cnf

再在配置文件中做如下修改

basedir=/usr/local/mysql #mariadb安装目录
datadir=/data/db#数据存放目录
pid-file=data/db/mariadb.pid
log-error=/log/mariadb.err #mariadb告警日志 启动失败可以查看该日志

可以启动mariadb服务了

service mysqld start#启动start 停止stop 重启restart

执行安全初始化脚本

mysql_secure_installation
/usr/local/mysql/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
#改变root用户的密码;
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
#移除匿名用户;
Remove anonymous users? [Y/n] Y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
#禁止root用户远程登录;
Disallow root login remotely? [Y/n] Y
 ... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
#不移除test数据库;
Remove test database and access to it? [Y/n] n
 ... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
#重载授权表生效;
Reload privilege tables now? [Y/n] Y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!

当前状态下我们不能远程登录到数据库,因为受到user表的限制,在非生产环境下我们可以把user表里面的host字段改为”%” 这样就可以在任意地址远程登录到数据库ps:注意这样非常不安全不能在生产环境下这样设置

mysql -uroot -p#登录mysql
Enter password:  #输入密码
MariaDB [(none)]> update mysql.user set host="%" where host="127.0.0.1";

关于处理单个功能的基本思路,拿查询举例

一、查询功能:

根据id(页面取值)–> 控制层(Controller) –> 取数据(Model)–> 显示(页面)

1、取值出错–> 提示XXX

2、传递控制层–> 网络传输(提示)

3、丢数据–> id出错(提示)

4、数据查询不到(捕获异常)–> 提示错误

5、数据不能解析(提示错误)

6、数据显示(数据能不能正常显示)—> 提示