说明

  • 关系数据库管理系统，以分布式数据库为核心，结构为Client/Server或B/S体系
  • 由至少一个表空间和数据库模式对象组成
    • 模式是对象的集合，而模式对象是直接引用数据库数据的逻辑结构
      • 模式对象包括：表、视图、序列、存储过程、同义词、索引、簇、数据库链等
    • 逻辑结构由逻辑存储结构和逻辑数据结构组成，而模式对象（逻辑数据结构）和关系形成了数据库的关系设计
      • 逻辑存储结构包括表空间、段和范围，用于描述怎样使用数据库的物理空间
    • 段（Segment），是表空间中一个指定类型的逻辑存储结构，它由一个或多个范围组成，段将占用并增长存储空间
    • 数据库的物理存储结构由一些多种物理文件组成，主要有数据文件、控制文件、重做日志文件、归档日志文件、参数文件、口令文件、警告文件等
      • 控制文件，存储实例、数据文件及日志文件等信息的二进制文件
      • 数据文件，存储数据，以 .dbf做后缀。一个表空间对多个数据文件，一个数据文件只对一个表空间

  • Oracle安装会自动生成 sys用户和 system用户

    • sys用户是超级用户，具有最高权限，具有 sysdba角色，具有 create database的权限
      • 用sys登录sqlplus，用户名输入 sys as sysdba，否则报错
    • system用户是管理操作员，具有 sysoper角色，没有 create database的权限
    • sys和 system最大的区别是在于有没有 create database的权限

      Oracle结构

      数据库

  • 存储数据的多个物理文件的集合，如控制文件、数据文件、参数文件、日志文件、临时文件等

    • 是静态的、永久的
  • 数据库名是对数据库的标识
    • 启动数据库/全局数据库，是数据库系统的入口，对内置一些高级权限的用户如SYS、SYSTEM。
    • 用这些高级权限账号登录就可以在数据库实例中创建表空间、用户、表等

  • select name from v$database; 查询当前数据库名

    数据库实例

  • 官方描述：实例是访问Oracle数据库所需的一部分计算机内存和辅助处理后台进程，是由进程和这些进程所使用的内存（SGA）所构成的一个集合

  • 是用来访问和操作数据库的一块进程，只存在于内存中
    • 是用户访问数据的中间层，是使用数据库的手段，为用户访问数据库提供了必要的内存空间和工作进程
    • 是动态地，临时的
  • 访问Oracle都是访问一个实例，这个实例如果关联了数据库文件，就是可以访问的，如果没有，会得到实例不可使用的错误
  • 实例名指的是用于响应某个数据库操作的数据库管理系统的名称，也叫SID
    • select instance_name from v$instance;
    • show patameter instance_name;
  • 数据库实例名（instance_name）用于外部连接。操作系统要取得与数据库的联系，必须使用数据库实例名
  • 一个数据库可以有多个实例，在作数据库服务集群的时候可以用到

  • Oracle系统的进程分为三种类型：

    • 用户进程
      • 用户在客户端运行一个应用程序或Oracle工具时，系统将为该用户创建一个用户进程
      • 用户进程负责与Oracle服务器建立连接和会话并向服务器发出数据处理请求，得到结果后再输出给用户
      • Oracle运行为一个用户同时创建多个会话
    • 服务进程
      • Oracle为用户进程创建一个服务进程或分配一个空闲的服务进程
      • 服务进程负责在用户进程和Oracle实例之间调度请求和响应
    • 后台进程
      • 是操作数据库的基础，不管有没有用户连接数据库，这些进程都会被启动
      • DBWR（数据库写入程序）、LGWR（日志写入程序）、CKPT（检查点）、SMON（系统监控）、PMON（进程监控）、ARCH（归档）、RECO（恢复）、LCKn（封锁）

        表空间

  • Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表

    • 有了数据库，就可以创建表空间
    • 创建表空间
      • create tablespace 表空间名 datafile 表空间数据文件路径 size 表空间初始大小 autoextend on;
    • 给已有的表空间增加表空间文件（一个表空间文件不够的情况）
      • alter tablespace 表空间名 add datafile 表空间数据文件路径 size 表空间初始大小 autoextend on；
    • 查看已经创建好的表空间
      • select default_tablespace,temporary_tablespace,d.username from dba_users d;
    • select name from v$datafile; 查看表空间文件的位置

      用户

  • Oracle数据库建好后，要想在数据库里建表，必须先为数据库建立用户，并为用户指定表空间

    • create user 用户名 identified by 密码 default tablespace 表空间（默认USERS） temporary tablespace 临时表空间（默认temp）;
  • 有了用户，要想使用用户账户管理自己的表空间，还得给用户分配权限

  • select * from dba_users; 查看数据库用户

    表

  • 有了数据库、表空间、和用户，就可以用自定义的用户在自己的表空间创建表了

    数据库服务名

  • 为了方便并行环境的设置，引进了Service_name参数，该参数对应一个数据库，而不是一个实例

  • 一个数据库可以对应多个Service_name，以便实现更灵活的配置，该参数与SID没有直接关系
  • 如果数据库有域名，则数据库服务名就是全局数据库名，否则，数据库服务名与数据库名相同
  • select value from v$parameter where name = ‘service_name’;
  • show parameter service_name;

  • select instance_name from v$instance;

    sql*plus常用命令

    修改密码，将密码有效期由默认的180天改为无限制

  • 进入 sqlplus 模式，sqlplus / as sysdba;

  • SELECT * FROM dba_profiles WHERE profile=’DEFAULT’ AND resource_name=’PASSWORD_LIFE_TIME’;
  • ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
  • alter user userName identified by oldPwd;

  • 如果账户锁定：

    • alter user db_user account unlock;

      查看数据库基本信息

  • 查找用户

    • select * from dba_users;
  • 查找工作空间的路径
    • select * from dba_data_files;
  • 删除用户
    • drop user userName cascade;

  • 删除表空间

    • drop tablespace tablespaceName including contents and datafiles cascade constraint;

      建库思想

  • 创建一个主键时，会自动创建一个与之对应的唯一索引

    • 如果没有特殊指定，那么这个索引的表空间和表格的空间是一样的，但不建议挡在一起
    • 不建议把索引和数据存在同一个表空间中
  • 创建表时，不能再统一个字段上建立两个索引
    • 唯一索引，该字段没有重复值，但允许为空（该字段可以有多个null值）
    • 一张表只允许有一个主键，但可以创建多个 unique index

  • Package 为了满足程序模块化的需要，引入了包的构造，通过使用包就可以分类管理过程和函数等

    • 在一个大型项目中，可能有很多模块，而每个模块又有自己的过程、函数等
      • 而这些过程、函数默认都是放在一起的，造成查询和维护问题，甚至发生误删
    • 包是一种数据库对象，相当于一个容器
      • 将逻辑上相关的过程、函数、变量、常量和游标组合成一个更大的单位
      • 用户可以从其他 PL/SQL 块中对其进行引用

        脚本文件

        spoll数据导出

  • spool缓冲池技术可以实现Oracle数据导出到文本文件

  • spool是SQLPLUS的命令，不是SQL语法里的东西

  • 对于spool数据的sql，最好要自己定义格式，以方便程序直接导入

    set设置

  • set colsep ‘’; //域输出分隔符

  • set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
  • set feedback off; //回显本次sql命令处理的记录条数，缺省为on
  • set heading off; //输出域标题，缺省为on
  • set pagesize 0; //输出每页行数，缺省为24，为了避免分页，可设定为0
  • set termout off; //显示脚本中的命令的执行结果，缺省为on
  • set trimout on; //去除标准输出每行的拖尾空格，缺省为off
  • set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off

    实例

    ```sql CREATE TABLESPACE NMAN2020_TBLS_DATA_STATIC LOGGING DATAFILE ‘DISK_LABEL1:\oradb\data\static1\NMAN2020_TBLS_DATA_STATIC01.dbf’ SIZE 128M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 4095M, ‘DISK_LABEL1:\oradb\data\static1\NMAN2020_TBLS_DATA_STATIC02.dbf’ SIZE 128M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 4095M EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE NMAN2020_TBLS_IDX_STATIC LOGGING DATAFILE ‘DISK_LABEL1:\oradb\idx\static1\NMAN2020_TBLS_IDX_STATIC.dbf’ SIZE 128M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 4095M EXTENT MANAGEMENT LOCAL;

CREATE user NMAN2020 identified by FLD_PASSWORD;

ALTER USER NMAN2020 DEFAULT TABLESPACE NMAN2020_TBLS_DATA_STATIC TEMPORARY TABLESPACE TEMP; exit;

CREATE TABLE XXX( primary key(XXX_ID) using index tablespace NMAN2020_TBLS_IDX_STATIC ) tablespace NMAN2020_TBLS_DATA_STATIC;

prompt xxxx; 相当于 echo

alter table XXX add constraint PRIMARY00001 primary key (XXX_ID) using index tablespace NMAN2020_TBLS_IDX_STATIC; create unique index XXXID on XXX (XXXID) tablespace NMAN2020_TBLS_IDX_STATIC;

ALTER TABLE a ADD CONSTRAINT name FOREIGN KEY (a.xx) REFERENCES b(b.xx) ```