%toc

mysql 插入数据报错 Incorrect string value: '\xF0\x9F\x98\x84\xF0\x9F

找了点资料发现UTF-8编码有可能是两个、三个、四个字节。Emoji表情或者某些特殊字符是4个字节，而Mysql的utf8编码最多3个字节，所以数据插不进去。 解决方案

修改服务器mysql配置

1.在mysql的安装目录下找到my.ini,作如下修改：
[mysqld]
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4

重启服务
/etc/init.d/mysql restart
  
  修改表字符集
ALTER TABLE XXX CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

select 'DROP TABLESPACE '||tablespace_name||' INCLUDING CONTENTS AND DATAFILES;' from user_tablespaces;

CREATE TABLESPACE USER_DI_JPTXT DATAFILE '/oradata/files1/DI_JPTXT/USER_DI_JPTXT_01.DBF' SIZE 50M AUTOEXTEND ON NEXT 500M MAXSIZE unlimited LOGGING ONLINE PERMANENT BLOCKSIZE 32768 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

select username,default_tablespace from user_users;
alter user national default tablespace USER_NATIONAL profile default;

oracle sid 字符不要超过8个  服务名也尽量不要太长
sid 中不应该配置为带db_domain 的名字

修改默认启动模式为spfile：
create spfile from pfile;
shutdown immediate;
startup;

oracle db_files 的默认值是200

show parameter db_files;
alter system set db_files=1024 scope=spfile;
shutdown immediate;
startup;

原因：db_32k_cache_size 初始设置过小导致的。
show parameter db_32k_cache_size;
alter system set db_32k_cache_size=8M;
同理：
db_4k_cache_size, db_8k_cache_size, db_16k_cache_size 修改方法类似。

select  'CREATE TABLESPACE AU'||substr(table_name,2,28)||' DATAFILE ''/oradata/files1/DI_AUTXT/AU'||substr(table_name,2,28)||'_01.DBF'' SIZE 50M AUTOEXTEND ON NEXT 500M MAXSIZE unlimited LOGGING ONLINE PERMANENT BLOCKSIZE 32768 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;' from user_tables where table_name like 'T_%';

select  'alter table '||table_name||' move tablespace AU'||substr(table_name,2,28)||';' from user_tables where table_name like 'T_%';

sqlplus user/password @ server_sid
sql>@d:\test.sql

10、导出表DDL

select dbms_metadata.get_ddl('TABLE', u.table_name) from user_tables u;

--获得建TABLESPACE的语句
begin
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
  for v_ddl in (SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) as content FROM DBA_TABLESPACES TS)
  loop
     DBMS_OUTPUT.PUT_LINE(replace(v_ddl.content,'"',''));
  end loop;
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
end;
/
--别漏掉最后的这个斜杠/ 

create or replace directory dump as 'D:\databack\dump';
grant read, write on directory dump to spt;

#其他参数： remap_tablespace=A:B

#导出：
expdp national/national directory=DMP_DIR filesize=4G dumpfile=national_20131101_%U.dmp

#导入：
impdp di_docdb/di_docdb directory=DUMP_DIR content=metadata_only dumpfile=di_cntxt_alldb_20140623.dmp remap_schema=di_cntxt:di_docdb

SELECT tablespace_name 表空间,sum (blocks *8/ 1024) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;

create or replace view   dba_tablespace_free as
select a.tablespace_name, a.total_space_mb allocated_space_mb,round( b.free_space_mb,2 ) free_space_mb,(a.max_space- a.total_space_mb) free_allocate_mb ,round( a.max_space,2 ) max_space_mb,
round((a.total_space_mb -b.free_space_mb )/a.total_space_mb *100, 2) pct_usage,round( a.total_space_mb/a.max_space *100, 2) pct_allocated
from ( select tablespace_name ,sum( bytes)/ 1024/1024 total_space_Mb,decode( sum(maxbytes/1024 /1024), 0,
sum(bytes)/1024 /1024, sum(case when AUTOEXTENSIBLE='YES' then maxbytes
                             else bytes end)/1024 /1024) max_space
 from dba_data_files  group by tablespace_name )a ,(select tablespace_name , sum((bytes)/1024 /1024) free_space_Mb
 from dba_free_space group by tablespace_name ) b where a.tablespace_name=b.tablespace_name;

ALTER TABLESPACE NAT_DES_PARAGRAPHS ADD DATAFILE '/oradata/files1/DI_CNTXT/NAT_DES_PARAGRAPHS_03.dbf' SIZE 50 M AUTOEXTEND ON NEXT 500M MAXSIZE unlimited ;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;