Oracle

名词介绍

1. 数据库

   oracle数据的物理存储，包括数据文件、参数文件、日志等

2. 实例

   一个oracle实例有一系列的后台进程，一个数据库可以有n个实例。就好比说可以跑多个QQ一样

3. 表空间

   是数据文件（ora/dbf）的逻辑映射，也就是多个数据文件组成一个表空间，数据文件是看得见摸得着的文件，表空间是一个逻辑概念。

   --创建表空间
   create tablespace 表空间名称 
   datafile 'c:\app\gen\oradata\myora\xxx.dbf' 
   size 表空间初始大小100m
   Autoextend on; （自增长）
   --查看已经创建好的表空间(创建完用户后才能看到刚才创建好的表空间)
   select default_tablespace,temporary_tablespace,d.username from dba_users d
   

4. 用户

   用户使操作表的基本单位，相当于mysql中的数据库database。不同实例可以创建多个用户。

   --创建用户
   create user 用户名
   identified by 密码
   default tablespace 表空间名称
   temporary tablespace temp;
   

5. 权限

   有了用户，要想使用用户账号管理自己的表空间，还需要给它权限

   --给用户授予登录/创建表/管理员的权限
   grant connect/resource/dba to 用户名;
   --给用户授予其他用户的表权限
   grant select/delete/update/insert on 其他用户名.表名 to 用户名;
   --取消用户权限
   revoke 权限 from 用户名;
   

6. 数据文件

   数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或多个数据文件中。

windows安装

此处以oracle 11g为例

1. 取消安全更新
2. 创建和配置数据库
3. 服务器类
4. 单实例数据库安装
5. 高级安装
6. 简体中文、英语
7. 企业版
8. 安装目录
9. 一般用途/事务处理
10. 数据库名（一般根据实际用途取名，如ecard）
11. 内存（根据现场配，默认40%），字符集（默认gbk）
12. 管理选项（下一步）
13. 文件系统（根据现场指定数据库存放位置）
14. 不启动自动备份
15. 对所用账户使用相同的口令（大小写数字都要有，最少八位）
16. 保存响应文件（保存设置）
17. 安装ing
18. 确定 完成

登录

1. 在oracle服务端登录oracle的方法https://blog.csdn.net/heshushun/article/details/79773886

启动

1. 先登录服务器后，切换到oracle用户状态下： su - oracle

   2. 检查oracle监听器运行状态： lsnrctl status命令查看。
      1. 若没启动，执行启动监听，通过命令： lsnrctl start （lsnrctl stop 关闭监听）
      2. 如果启动报错，listener.ora文件打开，调整HOST IP
   3. 进入sqlplus，命令： sqlplus /nolog

2. 管理员 sysdba 权限登陆oracle： conn /as sysdba

   5. 启动服务（实例）： startup

   关闭

   关闭服务（实例）： shutdown immediate;

   重启

   SQL> shutdown immediate

   SQL> startup open

   检验服务是否开启

   看数据库当前状态

   SQL>select status from v$instance;

   如果status = open 就说明oracle服务正常。

3. 在oracle客户端登录oracle的方法

   1. 原始的登录方法

      sqlplus scott/123@//192.168.94.133/orcl     //192.168.94.133是服务端的IP地址
      

   2. 修改配置文件后的登录方法

      sqlplus scott/123@orcl_133
      

      可以理解为“orcl_133”是“//192.168.94.133/orcl”的别名，这个别名是在客户端软件(sqlplus)的安装目录下配置文件设置的。如E:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora

      这里以服务端的客户端软件为例，该配置文件为/opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora，默认文件内容为：

      # tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.
      
      LISTENER_ORCL =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      
      ORCL =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orcl)
          )
        )
      

可见在这里的客户端软件，ORCL是被指定为协议为TCP、IP地址为localhost，端口为1521的实例了，所以我们可以用sqlplus scott/123直接登录oracle。

客户端登录服务端

所以在只装oracle客户端的机器上，要使用sqlplus scott/123@orcl_133登录远程oracle的服务端，需要在该机器的tnsnames.ora文件增加：

ortcl_133 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.94.133)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

初始化配置

1. 对缺省密码的有效期进行修改
   1. 登录数据库
      1. sqlplus /nolog
      2. conn 账号/密码 as sysdba，以管理员权限登录数据库
   2. 输入alter profile default limit password_life_time unlimited;
2. 对空表不备份进行修改（调整为全量备份）
   1. 输入alter system set deferred_segment_creation=false;

备份与导入

备份

https://blog.csdn.net/xuheng8600/article/details/84324707

导入

创建表空间和用户，以IDC_U_STUWORK为例

1. oracle命令行登录

   sqlplus 用户名/密码

2. 创建表空间IDC_U_STUWORK，指定存储文件及路径/u01/app/oracle/oradata/orcl/IDC_U_STUWORK.dbf，表空间大小360M

   create tablespace IDC_U_STUWORK datafile '/u01/app/oracle/oradata/orcl/IDC_U_STUWORK.dbf' size 360M;

   扩展：

   1. 删除表空间：
      1. drop tablespace 表空间名
      2. 删除本地文件/u01/app/oracle/oradata/orcl/IDC_U_STUWORK.dbf即可

3. 创建用户并指定默认表空间

   create user IDC_U_STUWORK identified by Jiaminjm1 default tablespace IDC_U_STUWORK;

4. 赋予普通用户权限给用户

   grant connect,resource to IDC_U_STUWORK;

   DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
   RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
   CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
   对于普通用户：授予connect, resource权限。
   对于DBA管理用户：授予connect，resource, dba权限。

5. 切换用户

   sqlplus IDC_U_STUWORK/Jiaminjm1

导入dmp数据

5. 导入

   # 三种方式
   方式一：
   1.将oracle_6.0.4.dmp文件上传到/u01/app/oracle/admin/orcl/dpdump/中
   2.
   impdp IDC_U_STUWORK/Jiaminjm1@orcl file=oracle_6.0.4.dmp full=y ignore=y remap_schema=bladex_release:IDC_U_STUWORK transform=segment_attributes:n
   
   方式二：
   1.将oracle_6.0.4.dmp文件上传到/opt/dmp中
   2.修改文件属组： chown -R oracle:oinstall /opt/dmp
   3.创建文件夹dir：SQL> create directory dir as '/opt/dmp';
   4.授权用户访问授权：SQL> grant read,write on directory dir to IDC_U_STUWORK;
   5.
   impdp idc_u_stuwork/Jiaminjm1@orcl directory=dir dumpfile=oracle_6.0.4.dmp  remap_schema=bladex_release:IDC_U_STUWORK transform=segment_attributes:n
   

   问题：ORA-39088: file name cannot contain a path specification

   解决：方式一的错误，log文件名不能包含路径，直接修改成为文件名即可

   问题：ORA-39145: directory object parameter must be specified and non-null

   解决：方式一的错误，权限问题，给予用户最高权限grant dba to 用户名

   问题：导入时，出现大量ORA-39112: dependent object type comment skipped

   解决：方式一的错误，没有指定用户转移，语句中增加参数remap_schema=bladex_release:IDC_U_STUWORK transform=segment_attributes:n

   扩展：

   remap_schema=bladex_release:IDC_U_STUWORK 原来的schema:现在的schema
   transform=segment_attributes:n 去掉表空间和存储子句

表空间、用户、权限

https://blog.csdn.net/wang_chaodong/article/details/116243090

使用sys账号进行操作
# 查看哪些用户拥有dba权限
select * from dba_role_privs where granted_role='DBA';
# 去掉权限
revoke dba from 用户名;
# 修改密码
alter user 用户（sys） identified by 密码;
# 授权
grant connect, resource to 用户名;

创建用户视图

向第三方提供视图，需要建个新用户（用户名2），并且只授权这一个视图的查询权限

1. 创建用户

   create user 用户名2 identified by 密码 default tablespace tbs1 temporary tablespace tbs1_temp profile DEFAULT;
   

2. 授权（使用sys/system用户）

   grant connect,resource to 用户名2;
   --这两个是普通用户必须要的两个权限
   grant CREATE SYNONYM to 用户名2;
   --这个为了之后查询时方便，创建同义词权限
   grant select on 用户名1.视图名 to 用户名2;
   --授权一张表/视图的查询权限
   

3. 创建同义词（使用用户名2）

   create synonym 视图名 for 用户名1.视图名;
   

4. 查询即可

连接数查询

--查看当前连接数
select count(*) from v$session
--查看最大连接数
select value from v$parameter where name = 'processes' 

查看连接数是否占满，对比上面两个查出来的数即可

1. plsql，sqlplus连接oracle非常慢，偶尔能连接上

   原因是监听日志过大,4G封顶，一般windows装的oracle会出现此情况

   解决：\app\Administrator\diag\tnslsnr\LS–20171012URU\listener\trace\listener.log，找到该路径，备份到其他地方后删除掉（需要先关闭监听服务TNSListner）。

2. Oracle密码过期, 报：ORA-01017: 用户名/口令无效; 登录被拒绝…

   1. 通过sys进入管理员

   2. 查询密码有效期时间

      select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
      默认是180天，改成无限制
      alter profile default limit password_life_time unlimited;
      再查询一下，注意事务
      

   3. 修改新密码，不修改的话密码仍然是失效的

      alter user 数据库用户名 identified by 数据库新密码;
      

3. Oracle把一个表的数据复制到另一个表中

   1、新增一个表，通过另一个表的结构和数据：
     create table tab2 as select * from tab1;
   2、如果表存在：
     insert into tab2 select * from tab1;
   3、同一个表中，将A字段的值赋给B字段：
     update table_name set B = A;
   4、将一个表的字段数据插入到另一个表的字段数据中
     insert into tab2
         (t_code, t_name)
          select pk_code, pk_name from tab1;
     * 如果报提醒：ORA-00928: 缺失 select 关键字错误。
     原因是：这里tab1(Field1,Field2,....)不能加单引号
   5. 多个表的多个字段，插入同一个表的多个字段。
     insert into tab1
           (tab_id, tab2_name, tab_code)
            select a.pk_id, b.pk_name, b.pk_code
             from tab1 a, tab2 b
             where a.pk_id = '7777'
             and b.pk_code = '12';
   

4. 表空间占满，将102.57的超过90%的扩容

   1. 进入102.57中的xydb、orcl实例，sys用户

   2. 查看表空间占用率

      SELECT
      mAX(B.file_id) id,
      B.TABLESPACE_NAME 表空间,
      B.FILE_NAME 物理文件名,
      B.BYTES / 1024 / 1024 大小M,
      (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
      SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) 利用率
      FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
      WHERE A.FILE_ID = B.FILE_ID
      GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES
      ORDER BY B.TABLESPACE_NAME;
      

   3. 扩容

      1. 方法一（将原物理文件扩展）：

         --物理文件不能超过32G
         alter database datafile '表空间位置' resize 新的尺寸
         例如：
         alter database datafile '/ora_data/icsdb/system01.dbf' resize 500M;
         

      2. 方法二（增加物理文件个数）：

         alter tablespace 表空间名称 add datafile '新的数据文件地址' size 数据文件大小
         例如：
         alter  tablespace system add datafile '/ora_data/icsdb/system02.dbf' size 1G；
         

      3. 方法三（设置表空间自动扩展）：

         alter database datafile '数据文件位置' autoextend on next 自动扩展大小 maxsize 最大扩展大小
         例如：
         alter database datafile '/ora_data/icsdb/system02.dbf' autoextend on next 500m maxsize 10000m；
         

         扩展：

         创建表空间

         create tablespace 表空间名称 
         datafile 'c:\app\gen\oradata\myora\xxx.dbf' 
         size 表空间初始大小100m
         autoextend on next 1024m maxsize 20480m;