99数据库管理员账号 admin admin123

环境配置、查看

查看 Oracle 版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 5月 18 11:40:31 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn admin/admin123@192.168.0.99:1521/orcl
已连接。
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

大小写问题

  1. 用户名不区分大小写「大写或小写不影响连接使用」
  2. 11g 开始，密码默认区分大小写

数据库字符集

服务器 编码
99 ZHS16GBK
67 AL32UTF8
客户数据库 AL32UTF8
本机 AL32UTF8
138 AL32UTF8

  • 安装 Oracle 时字符集一定要使用 AL32UTF8「一个汉字占 3 个字符长度」，避免导入到 zhs16gbk「一个汉字占 2 个字符长度」 字符集数据库时长度不够。

  • zhs16gbk是中文字符集，也就是适合在中国用，只能存储中文和英文字符，如果你存储韩文则显示为乱码（没有编码），而al32utf8是utf8字符集，u是unicode的意思，适合中文、韩语、日语等等不同的语言使用。那么为什么我们要在中国使用zhs16gbk存储中文呢？这是因为utf8存储中文的效率不如zhs16gbk，比如一个字“懂”，zhs16gbk采用2个字符存储，而al32utf8采用3-4个字符存储，这样效率就有了高低之分。

  • GBK 编码下，一个中文占两个字节

    1
    2
    select instr('保定市南市区','市',1,1) from dual;的值就是5
    select instr('保定市南市区','市',1,2) from dual;的值就是9

  • 修改服务器端字符集（修改为zhs16gbk）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    $sqlplus /nolog
    $conn /as sysdba
    SQL>select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
    若此时数据库服务器已启动
    则先关闭数据库：  
    SQL>shutdown immediate;
    SQL>STARTUP MOUNT;
    SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
    SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
    SQL>ALTER DATABASE OPEN;
    SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
    数据库重启：
    SQL>shutdown immediate;
    SQL> startup

  • 客户端字符集应该与数据库服务器设置一致

    1
    2
    3
    4
    5
    6
    7
    8
    //查看数据库服务器字符集
    select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

    //设置客户端字符集
    //常用中文字符集
    set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
    //常用unicode字符集
    set NLS_LANG=american_america.AL32UTF8

时区处理

基础

  • DATE 和 TIMESTAMP 类型不支持时区转换

  • CURRENT_DATE、CURRENT_TIMESTAMP、LOCALTIMESTAMP 的结果都根据客户端时区做了转换

  • SYSDATE 和 SYSTIMESTAMP 返回的则是数据库所在操作系统所在时区的时间

操作时区

1
2
3
4
5
6
7
8
9
-- 查询系统时区和session时区
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

-- 设置session时区
ALTER SESSION SET TIME_ZONE='Asia/Shanghai';

-- 测试
SELECT sysdate from dual;
SELECT SYSTIMESTAMP from dual;

创建表空间、用户

Windows

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
35
1：首先运行SQLPLUS

C:\Documents and Settings\Administrator>sqlplus /nolog（运行cmd后可直接运行SQLPLUS）

SQL*Plus: Release 10.1.0.2.0 - Production on 星期一 9月 3 15:15:27 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

2：以 dba 的权限连接数据库

SQL> connect /as sysdba;

3：创建表空间

SQL> create tablespace cms datafile 'D:\app\Administrator\oradata\orcl\cms.dbf' size 256m uniform size 128k;


4：把表空间改为自动扩展存储空间大小

SQL> alter database datafile 'D:\app\Administrator\oradata\orcl\cms.dbf' autoextend on;


5：创建用户

SQL> create user cms identified by cms default tablespace cms;

注：用户名cms 密码cms 默认表空间cms;


6：为用户授权

SQL> grant connect ,resource,dba to cms;

把连接管理以dba的权限授权给cms;

Linux

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
//登陆linux服务器，切换到oracle用户下
su - oracle  

//不在cmd或者terminal当中暴露密码的登陆方式
sqlplus /nolog    

//以 sys 管理员登录
SQL>connect sys/sys123456@NMGDB2 as sysdba 

//以 dba 管理员登录（常用）
SQL>connect /as sysdba;

1. 创建表空间
（ASM模式）SQL>
create tablespace sgp datafile '+data' size 500m  AUTOEXTEND ON next 64m maxsize unlimited;

（非ASM模式）SQL>
create tablespace sgp datafile '/home/oracle/sgp.dbf' size 256m uniform size 128k;

2. 把表空间改为自动扩展存储空间大小（非ASM模式）
SQL> alter database datafile '/home/oracle/sgp.dbf' autoextend on;

3. 创建用户，并指定表空间
SQL> create user sgp identified by sgp default tablespace sgp;

4. 给用户授予权限
SQL> grant connect,resource, dba to sgp;

5. 撤销权限
SQL> revoke dba from sgp;  #把 sgp 用户撤销 dba（系统管理员）权限

数据库

查看当前连接的数据库IP地址

1
select utl_inaddr.get_host_address from dual;

查看当前数据库

1
select name from V$DATABASE;

查看数据库实例名称

1
select instance_name from v$instance;

获取当前时间

1
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

处理空表

alter table tablename allocate extent;

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> connect sgp/sgp
SQL> set echo off
SQL> set feedback off
SQL> set pagesize 0
SQL> set linesize 9000
SQL> set tab off
SQL> spool sgp_deal_tablenull_107.sql
SQL> select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
SQL> spool off

执行完以后，你会得到一个文件：sgp_deal_tablenull_107.sql，
把这个文件里面的非sql语句删掉，只保留alter table的那些sql，
然后执行一下这些sql

连接

连接数

select count(*) from v$session;

select count (*) from v$process;

并发连接数

select count(*) from v$session where status=’ACTIVE’;

查看不同用户的连接数

select username,count(username) from v$session where username is not null group by username;

数据库允许的最大连接数

select value from v$parameter where name = ‘processes’

修改最大连接数

alter system set processes = 300 scope = spfile;

重启

1
2
shutdown immediate;
startup;

表空间

查看已建好的表空间

1
select file_name,tablespace_name from dba_data_files;

表空间数据文件自动扩展

1
alter tablespace system autoextend on;

查看某个表空间下有哪些表

1
select * from all_tables where tablespace_name='sde';

删除表空间

1
drop tablespace tablespace_name including contents and datafiles;

查看表空间与数据文件的对应关系

1
select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;

查看所有的默认表空间

1
select tablespace_name from dba_tablespaces;

查看指定用户的默认表空间

1
select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME = 'SCM';

重命名表空间名称

1
alter tablespace oldname rename to newname;

修改表空间的读写状态

1
alter tablespace tablespace_name read {only|write};

设置表空间的可用状态

1
alter tablespace tablespace_name { online|offline[normal|temporary|immediate];

创建大文件表空间

1
CREATE BIGFILE TABLESPACE tablespacename DATAFILE filename SIZE size;

临时表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sqlplus /nolog

conn gms9xjz/gms9xjz

--查询临时表空间大小以及使用率：
select tablespace_name, bytes, user_bytes, user_bytes/bytes,file_name from dba_temp_files;

--查询临时文件是否在线：
select name,status from v$tempfile;

--删除临时文件：
alter database tempfile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP.DBF' drop;

--创建临时表空间物理文件
alter tablespace TEMP add tempfile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP.DBF' size 200m autoextend on;

windows 脚本 —— 重建临时表空间

1
2
3
4
5
6
7
8
9
@echo off
echo conn gms9xjz/gms9xjz > temp.sql
echo alter database tempfile 'D:\app\Administrator\oradata\orcl\TEMP.DBF' drop; >> temp.sql
echo alter tablespace TEMP add tempfile 'D:\app\Administrator\oradata\orcl\TEMP.DBF' size 200m autoextend on; >> temp.sql
echo EXIT >> temp.sql

sqlplus /nolog @temp.sql

if exist temp.sql del /q temp.sql

用户管理

创建用户

1
create user cms identified by cms default tablespace cms;

授权

  1. connect role(连接角色)

临时用户，特指不需要建表的用户，通常只赋予他们connect role.

connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。
拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)

  1. resource role(资源角色)

更可靠和正式的数据库用户可以授予resource role。resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)

  1. dba role(数据库管理员角色)

dba role拥有所有的系统权限。包括无限制的空间限额和给其他用户授予各种权限的能力

1
2
3
4
5
//为用户user1 授权 connect resource
grant connect, resource to user1;

//撤销对user1赋予的权限
revoke dba from user;

修改用户名

1
2
3
4
5
6
7
8
9
10
11
1. 查找 SGP 用户，找到用户标识 user_id「以 sysdba 登录，用户名区分大小写」
   conn /as sysdba;
   select username, user_id from dba_users where username='SGP';
2. 修改用户名为 scm 并提交「下行命令中 158 为上行命令结果中 user_id」
   UPDATE USER$ SET NAME='scm' WHERE user#=158;  
   COMMIT;
3. 强制刷新
   ALTER SYSTEM CHECKPOINT;
   ALTER SYSTEM FLUSH SHARED_POOL;
4. 修改用户密码
   ALTER USER SCM IDENTIFIED BY scm;

删除用户和表数据

1
drop user username cascade;

某个用户下所有表

1
SELECT * from all_all_tables WHERE owner='SCM';

某个用户下每张表的数据量

1
SELECT t.table_name,t.num_rows from user_tables t ORDER BY t.NUM_ROWS DESC;

查看当前用户的缺省表空间

1
select username,default_tablespace from user_users;

查看当前用户的表空间和有哪些表

1
select table_name,tablespace_name from user_tables;

账户锁定解锁

问题：ORA-28000: the account is locked

解决：ALTER USER username ACCOUNT UNLOCK;

解决PLSQL记录被另一个用户锁住的问题

1
2
3
4
5
6
7
8
9
10
1、查看数据库锁,诊断锁的来源及类型（忽略）：
select object_id,session_id,locked_mode from v$locked_object;
或者用以下命令： 
select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b where b.object_id=l.object_id 

2、找出数据库的serial#,以备杀死：
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time; 

3、杀死该session: 　　
alter system kill session 'sid,serial#'

修改用户密码「永不过期」

1
2
3
4
5
6
7
8
1. 查看默认有效期
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
2. 修改为 无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
3. 如果密码已经过期，则手动修改下
conn 用户名 回车提示输入新密码 
或者
alter user 用户名 identified by 密码

当前登录的用户名

1
select user from dual;

当前用户拥有的角色和权限

1
2
select * from user_role_privs;
select * from session_privs;

查看所有用户名

1
2
3
select username from dba_users;
select * from all_users;
select * from user_users;

查看角色(只能查看登陆用户拥有的角色)所包含的权限

1
select * from role_sys_privs;

导入/导出数据

导入/导出过程中可能会有错误或警告，如果导入或导出过程比较长，无法一直观测过程，则有必要将该过程输出信息输出至日志文件一份，以便后续查看

导出

y9t2uR 
1
2
3
4
//常用中文字符集
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
//常用unicode字符集
set NLS_LANG=american_america.AL32UTF8

导出用户所有数据

1
exp sgp/sgp@192.168.0.99:1521/orcl file=sgp_99_20170726.dmp owner=sgp log=explog.log

导出指定表数据(表结构和数据)

1
exp sgp/sgp@192.168.0.107:1521/orcl file=20160921sgp_table_sysmodule_sysdict.dmp tables=(sgp_sysmodule, sgp_sysdict)

导入

导入数据到指定用户

1
imp sgp/sgp@192.168.0.99:1521/orcl file=sgp20161025.dmp full=y log=implog.log

导入指定表数据(表结构和数据)

1
imp sgp/sgp@192.168.0.99:1521/orcl file=20160921sgp_table_sysmodule_sysdict.dmp tables=(sgp_sysmodule, sgp_sysdict)

导入数据（不附带创建表和表空间限制,前提条件是表名称一致）

1
imp scm/scm@192.168.0.99:1521/orcl file=sgp99dept.dmp tables=scm_dept ignore=y

索引

  1. create index 索引名 on 表名(列名);

    1
    2
    3
    4
    create index personindex on SGP_PERSON('personid');

    //唯一索引
    create unique index SGP_EVENT_EVENTCODE_uindex on SGP_EVENT (EVENTCODE);

  2. 创建组合索引

    1
    create index 组合索引名 on 表名(列名1,列名2);  //列名1 列名2 加引号

  3. 查看索引

    1. 查看某个表的索引

      1
      SELECT * from all_indexes WHERE table_name=upper('scm_event');

    2. 查看索引在哪个字段

      1
      select * from user_ind_columns where table_name=upper('scm_event');

    3. 查看某个用户下面所有的索引

      1
      SELECT * from all_indexes WHERE owner='SCM';

  4. 删除索引 drop index 索引名

    1
    drop index SGP_EVENT_EVENTCODE_uindex;

表

获取表的字段信息

摘要：表结构、表字段、表列信息

1
select  *  from user_tab_columns  where table_name = 'CMS_T_VIEW_RECORD'

查看表结构

1
describe 表名;

添加字段

alter table tablename add (column datatype [default value][null/not null],….);

修改字段

alter table tablename modify (column datatype [default value][null/not null],….);

1
2
3
4
5
6
7
8
9
10
11
1. 原字段改名
alter table SGP_PREGNANCY rename column  PLASTDATE to PLASTDATE1;

2. 新增字段（原字段名）
alter table SGP_PREGNANCY add PLASTDATE date;

3. 给新增字段赋值（从原字段值提取）
update SGP_PREGNANCY set PLASTDATE=to_date(PLASTDATE1, 'yyyy-mm-dd');

4. 删除改名后的原字段
alter table SGP_PREGNANCY drop column PLASTDATE1;

删除字段

alter table tablename drop column columnname;

更新表，根据另外一个表

1
2
UPDATE  T1 SET T1.C  =  (SELECT  B  FROM  T2  WHERE   T2.A = T1.A and rownum=1)
WHERE  EXISTS ( SELECT 1 FROM   T2  WHERE   T2.A = T1.A)

创建表，以另一个表的结构和数据

1
create table tabname as (select ......)

表存在，插入数据，以另一个表的数据

1
2
//表结构相同, A - B 代表不同数据库或用户 schema
insert into A.tab1 select * from B.tab2;

将一个表的字段数据插入到另一个表的字段数据中「字段顺序代表对应关系」

1
2
3
insert into A.tab1(col1,col2)  select field1,field2 from B.tab2;

//tab1 (col1,col2,....) 不能加单引号

多个表的多个字段，插入同一个表的多个字段

1
INSERT INTO tb1 (field1, field2, field3)  select t1.col1, t2.col2, t3.col3 from ...

注释

获取表注释

1
select 'comment on table '||t.table_name||' is '''||t.comments||''';' from user_tab_comments t;

获取列注释

1
select 'comment on column '||c.table_name||'.'||c.column_name||' is '''||c.comments||''';' from user_col_comments c;

删除表注释

1
2
1、select 'comment on table '||t.table_name||' is '''';' from user_tab_comments t;   
2、导出到cvs文件中，使用execl打开，（删除第一行）拷贝到plsql中执行即可删除

删除列注释

1
2
1、select 'comment on column '||c.table_name||'.'||c.column_name||' is '''';' from user_col_comments c;   
2、导出到cvs文件中，使用execl打开，（删除第一行）拷贝到plsql中执行即可删除

删除审计表

1
TRUNCATE TABLE SYS.AUD$;

利用存储过程删除审计表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace procedure dele_trunc as
begin
  execute immediate 'TRUNCATE TABLE SYS.AUD$';
end dele_trunc;

加入定时任务：
在SQL>
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
    'dele_trunc;',
    SYSDATE,'sysdate+3');  #每隔三天执行一次, 1/24/12表示每隔5分钟
    commit;
    end;
    /
提交后提示：
PL/SQL procedure successfully completed

序列

创建、修改、使用、删除

1
2
3
4
5
6
7
8
9
10
11
12
13
create sequence seq_dept 
       increment by 5 
       start with 1 
       maxvalue 100 
       minvalue 1 
       nocycle 
       nocache

select seq_dept.currval, seq_dept.nextval from dual

Alter sequence seq_dept maxvalue 200

DROP SEQUENCE seq_dept
1
2
3
4
5
6
7
8
9
10
11
//如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。  
create public database link dblink1  
　　connect to dbusername identified by dbpassword  
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';  
//删除dblink
DROP public  DATABASE LINK dblink1;
//查询现有DBLINK
select * from ALL_DB_LINKS;

//使用
select * from scm_user@dblink1;

视图

创建视图

create or replace view 视图名 as (select 字段 from 表名);
create or replace view 视图名 as (select 字段 from 用户.表名);
create or replace view 视图名 as (select 字段 from 表名@数据库链接名);

删除视图

drop view 视图名;

查询现有视图

select * from user_views;

安装问题

Oracle 执行先决条件检查失败

  1. 打开 cmd 后，键入 “net share c$=c:”，按下回车键，会出现 “c$ 已共享”，如下图所示：

图解如何解决Oracle执行先决条件检查失败

图解如何解决Oracle执行先决条件检查失败

  1. 在运行中（或键盘按 Win+R）输入 regedit 打开注册表，找到 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters 选中 Parameters，在右边名称中找到 AutoShareWks 项，在其右键菜单中 ，点击修改，将其 “数值数据 (V):” 由原来的 0 改为 1，然后点击确定，如下图所示：

图解如何解决Oracle执行先决条件检查失败

  1. 重新启动电脑后，通过 DOS 命令 net share 查看默认共享情况是否正常，在共享列表中会看到 ADMIN$、C$、IPC$ 等默认共享了
image-20210608170216247

安装后无法通过公网IP地址访问

连接本机Oracle报：ORA-12170: TNS: 连接超时

本机公网 ip 是防火墙映射地址，需要在防火墙中添加入站规则，将 1521 添加进去

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

  1. 查看并修改 listener.ora，正确格式如下

    F:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:F:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
        )
      )

    # wangqinpei 为计算机名
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = wangqinpei)(PORT = 1521))
        )
      )

    ADR_BASE_LISTENER = F:\app\Administrator

  2. 重启监听 OracleOraDb11g_home1TNSListener

  3. 重启 服务

  4. 等待一会儿，重新连接

连接linux数据库Oracle时报错ORA-12541: TNS: 无监听程序

1
2
3
4
5
6
7
8
9
10
1、连接oracle
su - oracle
sqlplus /nolog
conn /as sysdba
提示：Connected to an idle instance.
2、启动服务，startup
3、启动监听
切换到oracle /bin 目录，cd $ORACLE_HOME/bin
cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/
 lsnrctl start

本地oracle可以通过localhost连接,无法通过ip地址连接解决方法

1
2
3
4
1. 打开 Net Manager
2. 添加地址，输入 ip 端口 1521
3. 关闭，保存
4. 稍等 3 分钟，如果不行，可以重启监听服务

其它办法：ORA-12514：TNS：监听程序当前无法识别连接描述符中请求的服务_梁萌的博客-CSDN博客_ora12514

运维问题

修改端口号

参考《Oracle-修改端口》

解决 Oracle Temp01.dbf 不断变大的问题

1
2
3
4
5
6
7
8
9
10
11
12
13
sqlplus /nolog
conn /as sysdba;

1. 删除
alter database tempfile 'D:\app\Administrator\oradata\orcl\TEMP01.DBF' drop;

2. 新建
alter tablespace temp add tempfile 'D:\app\Administrator\oradata\orcl\TEMP01.DBF' size 2048m reuse autoextend on next 100m;

select d.file_name, d.file_id, d.tablespace_name, d.bytes from dba_temp_files d;

3. 关闭自动扩展
alter database tempfile 'D:\app\Administrator\oradata\orcl\TEMP01.DBF' autoextend off;

oracle 11g 默认情况下密码区分大小写

可通过 SEC_CASE_SENSITIVE_LOGON 参数设置 ，默认情况下该参数的值为 true

1
2
3
4
5
6
7
SQL> show parameter sec_case_sensitive_logon;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=false scope=both;
System altered.

11g oracle导出表时会默认不导出数据为空的表

问题：Oracle11g默认对空表不分配segment，故使用exp导出Oracle11g数据库时，空表不会导出
解决：设置deferred_segment_creation 参数为FALSE后，无论是空表还是非空表，都分配segment「针对以后新增表」

1
2
SQL>show parameter deferred_segment_creation; 
SQL>alter system set deferred_segment_creation=false;

oracle导入时提示IMP-00010：不是有效的导出文件，头部验证失败「导入高版本 dmp」

  1. 直接将dmp文件用notepad++打开修改版本号为被导入版本的版本号

第一行：TEXPORT:V11.02.00

  1. 文件过大，无法打开，可使用工具「AlxcTools.exe」修改

版本格式为 xx.xx.xx

查看 Oracle 版本: 11.02.00

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 6月 18 11:16:37 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn /as sysdba;
已连接。
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

ORA-00020:maximum number of processes (150) exceeded 错误解决方法

processes 进程，可以认为是数据库物理连接数

  1. 查看进程数「processes」，默认 150

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SQL> show parameter proce

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------------
    aq_tm_processes                      integer     1
    db_writer_processes                  integer     1
    job_queue_processes                  integer     10
    log_archive_max_processes            integer     2
    processes                            integer     150

  2. 修改 processes 「没有立即生效」

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SQL> alter system set processes=300 scope=spfile;

    系统已更改。

    SQL> show parameter proce

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    aq_tm_processes                      integer     1
    db_writer_processes                  integer     1
    job_queue_processes                  integer     10
    log_archive_max_processes            integer     2
    processes                            integer     150

  3. 关闭服务

    1
    2
    3
    4
    SQL> shutdown immediate
    数据库已经关闭。
    已经卸载数据库。
    ORACLE 例程已经关闭。

  4. 启动服务

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SQL> startup
    ORACLE 例程已经启动。

    Total System Global Area  530128356 bytes
    Fixed Size                   454116 bytes
    Variable Size             503316480 bytes
    Database Buffers           25165824 bytes
    Redo Buffers                1191936 bytes
    数据库装载完毕。
    数据库已经打开。

  5. 查看 processes 「已生效」

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SQL> show parameter proce

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    aq_tm_processes                      integer     1
    db_writer_processes                  integer     1
    job_queue_processes                  integer     10
    log_archive_max_processes            integer     2
    processes                            integer     300

本地计算机上的OracleOraDb11g_home1TNSListener服务启动后停止。某些服务在未由其他服务或程序使用时将自动停止。

产生原因

本机ip发生了变化(本机ip变化后与监听器配置中的ip不一致)

解决方法

打开Oracle的NetManager，保证本机ip与配置远程访问设置的ip一致即可启动监听(修改本机ip或修改远程访问ip)

Oracle 数据表转换为 Shapefile

https://www.cnblogs.com/hans_gis/p/3482986.html

ORA-12560: TNS: 协议适配器错误

参考 https://blog.csdn.net/Hello_World_QWP/article/details/78392855

方法一

系统有效：Ctrl + R 输入regedit 查找路径 “HKEY_LOCAL_MACHINE -> SOFTWARE -> Oracle -> KEY_OraDb11g_home1”
找到 ORACLE_SID 右键，修改为 orcl

XFumb5

方法二

终端有效：Ctrl + R 输入 cmd 进入到 DOS 界面，输入 

1
set oracle_sid=orcl

Oracle 数据库从 gbk 更换到 utf8 数据库兼容性问题解决

Oracal 数据库当前字符编码：

查询 sql：select userenv('language') from dual;

查询结果：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Oracle Database Migration Assistant for Unicode (DMU) 是一款独特的下一代迁移工具，提供将数据库从传统编码迁移到 Unicode 的端到端解决方案。通过在整个迁移流程中为 DBA 提供指导并实现许多迁移任务的自动化，DMU 直观的用户界面极大简化了迁移流程并降低了对字符集迁移专业知识的要求。它采用可扩展的就地迁移架构，与传统的导出和导入迁移方法相比，可显著减少数据转换所需的工作和停机时间。对于迁移后的数据库和已经使用 Unicode 字符集的现有数据库，DMU 还提供了一种验证模式，可识别未正确用 Unicode 编码的数据，从而对数据库应用中的 Unicode 实现的潜在问题进行健康检查。

从 2.1 版开始，DMU 与 Oracle GoldenGate 复制技术结合使用，支持近乎零停机时间的迁移模型。结合使用 DMU 与 GoldenGate 12.1.2.1.0 或更高版本，您可以设置这样一个迁移过程，利用 DMU 的数据准备和就地转换功能，利用 GoldenGate 复制迁移过程中生产系统上发生的增量数据更改，从而有效地消除停机时间窗口需求。

思路

  1. 全库扫描表字段，字段类型，字段值，字符编码
  2. 把对应表的字符编码从 gbk 转换为 utf8，如果之前字段的长度能够存储对应内容，就通过
  3. 如果之前字段的长度不能存储对应内容，比如存不下了，就会在 system.dum$exceptions 表里面添加对应表，行，列信息，这个字段就是需要扩展的字段