标签 导出 下的文章

exp导出报错EXP-00091: Exporting questionable statistics问题解决

使用如下命令执行导出操作:

exp user/pass@orcl file=/bak/20180803.dmp log=/bak/20180803.log owner=username buffer=111149600 grants=y 

输出如下信息,其中包含一些EXP-00091的错误提示:

EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                ARAP_BILLCONFER          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                   ARAP_BILLMAP     336693 rows exported

导出来的dump文件可用于导入。

看下官方对EXP-00091的介绍:

EXP-00091: Exporting questionable statistics. 
Cause: Export was able export statistics, but the statistics may not be usuable. The statistics are questionable because one or more of the following happened during export: a row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export, only certain partitions or subpartitions were exported, or a fatal error occurred while processing a table. 
Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, export complete tables. If desired, import parameters can be supplied so that only non-questionable statistics will be imported, and all questionable statistics will be recalculated.

导出过程中,由于客户端字符集或NCHARSET参数和服务器端不一致,导致一些统计信息不可用。解决方案是可以导出不存在问题的统计信息,或者改变客户端字符集或NCHARSET参数。

解决方法:
查询服务器端字符集:

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

设置客户端字符集:

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

并将这句写入到/home/oracle/.bash_profile文件的最末;

然后重新执行导出,问题解决;

或者在exp导出的时候增加参数(不推荐)

statistics=none   #统计信息字段statistics设置为none

不再报错,但这种方式相当于未导出那些因字符集不一致错误的统计信息,导入自然也不会有这部分统计信息,如果需要,那么只能再重新收集统计信息。

MySQL使用MySQLdump导出所有库

导出所有数据库
/usr/local/mysql/bin/mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > /tmp/all.sql
导入数据库
/usr/local/mysql/bin/mysqldu -u用户名 -p密码 < /tmp/all.sql

导出指定库,排除不导出的库

#!/bin/bash
USER="用户名"
PASSWORD="密码" 
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    fi
done

备份所有用户名密码

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

oracle使用spool导出select查询结果

Spool,是Oracle快速导出数据的工具,是sqlplus的指令,不是sql语法里的东西;
特殊场景,只有select表数据的权限,想要导出所有查询结果,使用sqlplus操作sql如下:

    set trimspool on;            #去除重定向(spool)输出每行的拖尾空格,缺省为off
    set linesize 500;            #输出一行字符个数,缺省为80
    set pagesize 30;             #输出每页行数,缺省为24,为了避免分页,可设定为0。
    set newpage 3;               #页和页之间隔着n个空行
    set heading off;             #输出域标题,缺省为on
    set term off;                #显示脚本中的命令的执行结果,缺省为on
    spool /tmp/e.sql;            #查询结果保存的文件          
    select * from brc.sale;      #查询
    spool off;                   #把文件发送到标准打印输出

其他参数说明:


---阅读剩余部分---

EXP-00056: ORACLE error 12154 encoun报错解决方法

在从库上使用exp导出数据时报错

[oracle@pldb02 oracle]$ exp 'powerdesk/xxxxxx"@powerdes' file=/oracle/powerdes-20170921.dmp log=/oracle/powerdes-20170921.log grants=y

Export: Release 11.2.0.4.0 - Production on Thu Sep 21 13:06:51 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


EXP-00056: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
EXP-00000: Export terminated unsuccessfully

使用tnsping检查:

---阅读剩余部分---

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

MySQL5.7.17使用select into outfile导出文件时报错如下:

mysql> SELECT CONT.BRAND_NAME, CONT.CONT_NO, CONT.STORE_NOS,
    -> SUM(CHARGE_DETAIL.RECEIVABLE_MONEY), SUM(CHARGE_DETAIL.RECEIVED_MONEY),
    -> (SUM(CHARGE_DETAIL.RECEIVABLE_MONEY) - SUM(CHARGE_DETAIL.RECEIVED_MONEY)) AS QIANFEI
    -> FROM BS_CONT_CHARGE_DETAIL CHARGE_DETAIL 
    -> INNER JOIN BS_CONT CONT ON CONT.ID = CHARGE_DETAIL.CONT_ID 
    -> AND CONT.MALL_ID = 6 AND CONT.IS_DEL = 0 AND CONT.`STATUS` = 1
    -> WHERE CHARGE_DETAIL.IS_DEL = 0 AND CHARGE_DETAIL.`STATUS` = 1
    -> AND CHARGE_DETAIL.RECEIVABLE_TIME <= '2017-06-30 00:00:00'
    -> GROUP BY CONT.CONT_NO, CONT.BRAND_NAME, CONT.STORE_NOS
    -> HAVING QIANFEI > 0
    -> ORDER BY CONT.BRAND_NAME, CONT.CONT_NO, CONT.STORE_NOS
    -> into outfile '/tmp/test.xls';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

在MySQL5.7.16和5.6以下版本中没这个问题;

This option sets the secure_file_priv system variable, which is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. For more information, see the description of secure_file_priv.
MySQL官方文档https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_secure-file-priv

secure_file_priv这个变量不支持动态修改,需要重启MySQL服务;

---阅读剩余部分---

MySQLdump 使用 --set-gtid-purged参数

1.导出时指定字符集,报错Character set 'utf-8' is not a compiled character set and is not specifie .
--default-character-set=utf-8

这个是因为字符集错了。是--default-character-set=utf8
2,导出时提示warning,A partial dump from a server that has GTIDs

[root@localhost data]# mysqldump -uroot --master-data=2 -p --single-transaction --databases test >3.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

关于GTID是5.6以后,加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。
官方给的:A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
所以可能是因为在一个数据库里面唯一,但是当导入其他的库就有可能重复。所有会有一个提醒。

可以通过添加--set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。
例如:导出langold_enroment库中FIN_INCOME表的数据和结构:

/usr/local/mysql/bin/mysqldump --user=root --host=localhost --default-character-set=utf8 --socket=/data/mysql/mysql.sock -p'xxxxxx' --extended-insert=false --set-gtid-purged=off langold_enroment FIN_INCOME > /tmp/FIN_INCOME.sql

MySQL导出查询结果到csv文件中

select 后面跟上 into outfile, fields terminated by, optionally enclosed by, line terminated by语句实现导出csv

语句的格式与作用
into outfile '导出的目录和文件名'
指定导出的目录和文件名

fields terminated by '字段间分隔符'
定义字段间的分隔符

optionally enclosed by '字段包围符'
定义包围字段的字符(数值型字段无效)

lines terminated by '行间分隔符'
定义每行的分隔符
如导出TB_USER表中ID,USERNAME,NICKNAME,MOBILE四个字段的内容到/tmp/zh.csv文件中,实现sql如下:

select ID,USERNAME,NICKNAME,MOBILE from TB_USER INTO OUTFILE '/tmp/zh.csv'  fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

每个字段以,分隔,字段内容是字符串的以”包围,每条记录使用rn换行。

MySQL 清空数据库中的所有表及导出/导入表、结构

清空所有表SQL
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='TABLE_NAME'" | mysql -f TABLE_NAME

例如:
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='langold_enrolment_db'" | mysql -f langold_enrolment_db

加-f参数是为了清理掉外键,要么会报错



---阅读剩余部分---

mysql将数据导出成excel

在mysql命令行下执行以下sql语句:
mysql>select * from user limit 10 into outfile '/tmp/test.xls'; 查询user表中前10条记录,导出到/tmp/test.xls文件中

linux系统尽量导出在/tmp目录下,不会出现读写权限问题

如果记录里面有中文,excel打开的时候会出现乱码,


---阅读剩余部分---

Oracle导出数据库 expdp命令参数详解

Oracle备份方式主要分为数据泵导出备份、热备份与冷备份三种,今天首先来实践一下数据泵备份与还原。数据泵导出/导入属于逻辑备份,热备份与冷备份都属于物理备份。oracle10g开始推出了数据泵(expdp/impdp),可以使用并行参数选项,因此,相对于传统的exp命令来说,执行效率更高。

---阅读剩余部分---

最新

分类

归档

评论

  • Liang: 贴下编译参数和步骤,...
  • shao3911: 您好,为什么我在编译...
  • aliang: 先看是yum安装还是...
  • aliang: 将原来的nginx安...
  • yen: 3、如果要回滚的话,...
  • yen: 刚好需要升级ngin...
  • 文雨: 一些新的method...
  • aliang: 默认不屏蔽估计开发团...
  • 山野愚人居: PHP既然允许直接使...
  • aliang: 最下面有github地址·

其它