oracle安装教程-秋风落叶

名词介绍

DDL（Data Definition Languages）语句：数据定义语言。定义了不同的数据段、数据库、表、列等数据库对象。包括create,drop,alter等。

DML(Data Manipulation Language)语句：数据操纵语句，用于对于数据库记录进行增删改查，并检查数据完整性。包括insert,delete,update,select等。

DCL(Data Control Language)语句：数据控制语句。定义了数据库、表、字段、用户的访问权限和安全级别。包括grant,revoke等。

oracle安装

rpm官网安装文档

https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/installing-oracle-database-using-rpm-packages.html#GUID-5AF74AC1-510E-4EB0-9BCA-B096C42C6A76

oracle 19c rpm包安装

服务器环境安装准备

centos7版本需要的依赖包
yum -y install \
compat-libcap1 \
glibc-devel \
ksh \
glibc-devel \
libstdc++-devel \
libaio-devel \
bind-utils \
nfs-utils \
psmisc \
smartmontools \
sysstat \
xorg-x11-utils \
xorg-x11-xauth

备注：可能需要以下包，需要单独下载安装,此包在下面连接里面可以下载。
yum -y localinstall compat-libstdc++-33

下载安装预安装包(检查环境,创建用户)

下载地址，根据实际情况选择
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/oracle-database-preinstall-19c-1.0-3.el7.x86_64.rpm

#开始安装
[root@localhost ~]# rpm -ivh oracle-database-preinstall-19c-1.0-3.el7.x86_64.rpm 
警告：oracle-database-preinstall-19c-1.0-3.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ec551f03: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:oracle-database-preinstall-19c-1.################################# [100%]

下载安装数据库包

根据实际情况下载
https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

#开始安装
[root@localhost ~]# rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm 
警告：oracle-database-ee-19c-1.0-1.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ec551f03: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:oracle-database-ee-19c-1.0-1     ################################# [100%]
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-19c configure
[root@localhost ~]#

数据库操作

启动数据库

#先启动监听
[oracle@localhost ~]$lsnrctl status
[oracle@localhost ~]$lsnrctl start

#启动数据库
SQL>startup  是最全的，实例，数据库加载，数据库打开都完成
ORACLE instance started.
Total System Global Area 1031798368 bytes
Fixed Size		    8904288 bytes
Variable Size		  654311424 bytes
Database Buffers	  360710144 bytes
Redo Buffers		    7872512 bytes
Database mounted.
Database opened.

SQL>startup nomount 只是启动了实例而没有启动数据库
SQL>startup mount启动了实例，并加载了数据库，但是数据库没有打开

查看数据是否启动 
方式1：open状态 是说明开启
SQL> select instance_name,status from v$instance;   --正常启动结果
INSTANCE_NAME	 STATUS
---------------- ------------
orcl		 OPEN
如果没有启动会出现以下错误
SQL> select instance_name,status from v$instance;  --错误启动结果
select instance_name,status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


方式2：
SQL> show sga;  -正常启动结果

Total System Global Area  763359832 bytes
Fixed Size                  8901208 bytes
Variable Size             528482304 bytes
Database Buffers          222298112 bytes
Redo Buffers                3678208 bytes
    
SQL> show sga;   --错误启动结果
ORA-01034: ORACLE not available   --如果没有启动会出现执行会出现ORA-01034: ORACLE not available错误
Process ID: 0
Session ID: 0 Serial number: 0

关闭数据库

#Oracle执行某些清除工作后才关闭（终止会话、释放会话资源）,最安全的方式
SQL>  SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

#立刻关闭，正在访问数据库的会话会被突然终止()
#若数据库迟迟不能down下来，在os上观察IO状况，几乎没有io的时候，另开一窗口shutdown abort ，几乎不会出问题了
SQL>shutdown abort

#关闭监听
[oracle@localhost ~]$lsnrctl stop
[oracle@localhost ~]$lsnrctl status

开机自启

sqlplus操作

用户连接

--dba登录
[oracle@localhost ~]$sqlplus / as sysdba    这是典型的操作系统认证，不需要listener进程

--本地登录
[oracle@localhost ~]$sqlplus username/password    这种连接方式只能连接本机数据库，同样不需要listener进程,用户必须是可用状态

--网络连接登录 需要listener进程处于可用状态 数据库必需处于open状态 最普遍的通过网络连接
sqlplus 用户/用户密码@IP地址:1521/orcl

--切换用户
SQL>connect system/123456 as sysdba
SQL>connect username/password   --切换普工用户
SQL>connect / as sysdba  --切换到dba

--查看当前用户
SQL>show user;

乱码

1.查看当前数据库字符集
SQL> show parameter nls_la
NAME                                TYPE       VALUE
------------------------------------ -----------------------------------------
nls_language                        string      SIMPLIFIEDCHINESE
2.临时修改
[oracle@rac2 ~]$ export NLS_LANG=american_america.zhs16gbk
3.永久修改
如果要使之永久生效，可以添加这个变量到oracle 用户环境 .bash_profile中。

美观显示

设置列宽：使用COLUMN命令来设置每列的宽度。例如：COLUMN column_name new_width。

设置行高：使用SET LINESIZE命令来设置每行的高度。例如：SET LINESIZE 32767。

取消标题：使用SET HEADING OFF命令来取消标题的显示。

取消列名和行号的显示：使用SET PAGESIZE 0命令来取消列名和行号的显示。

设置显示格式：使用SET LINESPOOL ON命令来开启行缓冲，以便更快地滚动数据。同时，可以使用SET PAGESIZE n命令来设置每页显示的行数。
以下命令将设置每行高度为1000，取消标题和行号的显示，并开启行缓冲
SET LINESIZE 1000
SET HEADING OFF
SET PAGESIZE 0
SET LINESPOOL ON

sql developer

如果使用sql developer连接oracle，选择的连接名是服务名，不是SID。

表空间操作

--------------------查询--------------------
查某个用户属于哪个表空间
select username,default_tablespace from dba_users dba_users where username='用户名' 
查询系统默认表空间的位置
select * from dba_data_files;
select * from dba_temp_files; --查询默认临时表空间位置

--------------------创建--------------------
--创建临时表空间
create temporary tablespace wjlsbkj --  wjlsbkj临时表空间名字
tempfile '/opt/oracle/oradata/ORCLCDB/wjlsbkj.dbf'  --数据文件的路径和名称 可以先查询默认系统表空间的位置
size 10m --表空间的初始大小，单位为MB
autoextend on --自动增长
next 10m maxsize 20480m --每次增加1M，最大增加到2048M
extent management local; --本地管理

--创建表空间
create tablespace wjbkj --wjbkj表空间名字
datafile '/opt/oracle/oradata/ORCLCDB/wjbkj.dbf' --数据文件的路径和名称
size 10m --表空间的初始大小，单位为MB
autoextend on --自动增长
next 10m maxsize 20480m --每次增加1M，最大增加到2048M
extent management local; --本地管理



--------------------删除--------------------
DROP TABLESPACE wjlsbkj INCLUDING CONTENTS AND DATAFILES;  --wjlsbkj指定表空间名字即可
DROP TABLESPACE wjbkj INCLUDING CONTENTS AND DATAFILES; --wjbkj指定表空间名字即可

用户操作

--------------------查询--------------------

--查询某个用户是否存在,用户名字必须大写,且是单引号
select * from all_users where username = 'APPSTORE'
--查询数据库有多少用户
select count(*) from all_users;

--------------------修改--------------------
--修改用户密码
SQL>alter user 用户名 identified by 用户密码;
--解锁用户
alter user system account unlock;

--------------------删除--------------------
drop user wjdb cascade; --删除用户wjdb
说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。

--------------------创建用户--------------------
非容器创建用户及PDB下创建用户
如果密码有!  之类的特殊符号 ，需要加双引号

--只创建用户，如果不指定表空间，则使用默认表空间
create user wjdb identified by wangjian;

--创建用户 指定表空间  表空间名字必须大写 必须大写  必须大写

CREATE USER wjdb IDENTIFIED BY wangjian --创建wjdb用户，密码是wangjian
DEFAULT TABLESPACE "WJBKJ" --指定表空间位置
TEMPORARY TABLESPACE "WJLSBKJ"; --指定临时表空间

--------------------授权用户--------------------
--授权,根据实际情况，按需分配
grant CREATE SESSION to wjdb; --如果不授权，用户不能登录
grant CREATE TABLE to wjdb; --创建表的权限
grant unlimited tablespace to wjdb; --允许用户使用表空间，不执行不然无法插入表数据 
grant CREATE VIEW to wjdb; --授权创建视图的权限

grant dba to wjdb;  --直接授权DBA权限  生产环境慎用


--取消授权
REVOKE CREATE SESSION FROM wjdb; --取消授权session的权限，取消其他权限方式一样
REVOKE unlimited tablespace FROM wjdb --取消插入数据的权限  ORA-01950: 对表空间 'WJBKJ' 无权限


权限解释说明
CREATE SEQUENCE 权限：如果需要创建序列（sequence），则需要授予该权限。
CREATE PROCEDURE 和 CREATE FUNCTION 权限：如果需要创建存储过程（procedure）或函数（function），则需要授予该权限。
CREATE TRIGGER 权限：如果需要创建触发器（trigger），则需要授予该权限。
CREATE VIEW 权限：如果需要创建视图（view），则需要授予该权限。
ALTER SESSION 权限：该权限允许用户修改自己的会话设置，例如更改密码等。

表操作

--------------------查询表数据--------------------
--查询当前用户有多少表
此方式查询，可能会算到被删除的表，结果可能不是特别的准确
select count(*) from tab
此方式查询的结果效果是最好的
select * from tab

--------------------创建表--------------------
--学生表
drop table students;
create table students(
    student_id NUMBER PRIMARY KEY, -- 学生ID，主键
    student_name VARCHAR2(50), -- 学生姓名
    student_class VARCHAR2(50), -- 学生所在班级
    student_teacher VARCHAR2(50), -- 学生所属老师
    student_school VARCHAR2(50),  -- 学生所属学校
    student_district VARCHAR2(50), -- 学生所属市区
    student_province VARCHAR2(50), -- 学生所属省份
    student_country VARCHAR2(50), -- 学生所属国家
    student_enrollment_date DATE  DEFAULT SYSDATE -- 学生入学时间
);
--老师表
drop table teachers;
CREATE TABLE teachers (
  teacher_id NUMBER PRIMARY KEY, -- 老师ID，主键
  teacher_name VARCHAR2(50) NOT NULL, -- 老师姓名，不能为空
  teacher_age NUMBER(3), -- 老师年龄
  teacher_class VARCHAR2(50) -- 老师所教班级
);

--------------------插入数据--------------------
--学生数据
INSERT INTO students (student_id, student_name, student_class, student_teacher, student_school, student_district, student_province, student_country, student_enrollment_date)
VALUES (1, '张三', '一班', '李老师', '清华大学', '海淀区', '北京市', '中国', TO_DATE('2022-06-01', 'YYYY-MM-DD'));
INSERT INTO students (student_id, student_name, student_class, student_teacher, student_school, student_district, student_province, student_country, student_enrollment_date)
VALUES (2, '李四', '二班', '王老师', '北京大学', '朝阳区', '北京市', '中国', TO_DATE('2022-07-01', 'YYYY-MM-DD'))
--老师数据
INSERT INTO teachers (teacher_id, teacher_name, teacher_age, teacher_class)
VALUES (1, '张老师', 35, '一年级一班');
INSERT INTO teachers (teacher_id, teacher_name, teacher_age, teacher_class)
VALUES (2, '李老师', 40, '二年级二班');

--小技巧
--全字段插入,列名可以忽略不写
insert into 表名 values(值1,值2);
--指定字段插入
insert into 表名 (列名1,列名2) values(值1,值2);
小技巧：
如果字段是oracle关键字，需要加双引号，才能插入数据。

--------------------添加注释--------------------
--学生表注释
COMMENT ON TABLE students IS '学生表信息';  --为表添加注释
--表的字段添加注释
COMMENT ON COLUMN students.student_id IS '学生ID';
COMMENT ON COLUMN students.student_name IS '学生姓名';
COMMENT ON COLUMN students.student_class IS '学生所在班级';
COMMENT ON COLUMN students.student_teacher IS '学生所属老师';
COMMENT ON COLUMN students.student_school IS '学生所属学校';
COMMENT ON COLUMN students.student_district IS '学生所属市区';
COMMENT ON COLUMN students.student_province IS '学生所属省份';
COMMENT ON COLUMN students.student_country IS '学生所属国家';
COMMENT ON COLUMN students.student_enrollment_date IS '学生入学时间';

--老师表注释
COMMENT ON TABLE teachers IS '老师表';
COMMENT ON COLUMN teachers.teacher_id IS '老师ID';
COMMENT ON COLUMN teachers.teacher_name IS '老师姓名';
COMMENT ON COLUMN teachers.teacher_age IS '老师年龄';
COMMENT ON COLUMN teachers.teacher_class IS '老师所教班级';


-- 查看表的注释
SELECT table_name, comments
FROM user_tab_comments
WHERE table_name = 'STUDENTS';

--查看表的字段有没有注释
SELECT column_name, comments
FROM user_col_comments
WHERE table_name = 'STUDENTS' AND column_name = 'STUDENT_ID';


--------------------删除表--------------------

--表和数据都删除,并未真正的删除，只是放在数据库回收站了
drop table 表名;
--删除数据,保留表
delete 表名;
--从回收站还原已删除的表名
flashback table 表名 to before drop;

视图索引

在Oracle中，视图是一种虚拟的表，它是由查询语句生成的。视图本身不存储数据，而是从基表中获取数据。视图可以用于简化复杂查询，提高数据安全性和保密性，以及控制用户对数据的访问。视图可以像普通表一样使用，并且可以使用常规的SQL语句进行操作。

索引是一种数据库对象，它可以帮助快速访问数据库表中的数据。索引可以加快查询速度，但是如果不合理地创建和使用索引，反而会降低系统的性能。在Oracle中，可以为单个列或多个列创建索引。

视图和索引之间的主要区别在于它们的目的和实现方式。视图是从一个或多个表中导出的虚拟表，而索引则是用于加速对表中数据的访问。视图可以根据需要动态生成，而索引则需要手动创建和维护。

-----------------------------------视图
--创建一个wj_view的视图 ，班级是一班的 学生姓名
CREATE OR REPLACE VIEW wj_view AS SELECT STUDENT_CLASS FROM STUDENTS WHERE STUDENT_CLASS = '一班' ;
--查询视图的数据
select * from wj_view;

--删除视图
DROP VIEW wj_view;

----------------------------------索引
--创建一个wj_index的索引，以学生的老师为基础
CREATE INDEX wj_index ON STUDENTS (STUDENT_NAME);

select * from STUDENTS WHERE STUDENT_NAME = '张三'; --这样查询之后速度会变快些

--删除索引
DROP INDEX wj_index;

数据库导入导出

--------------------------相关参数解释------------------------
full=y 是导入文件中全部内容
ignore=y 如果没有的表，创建并倒入数据，如果已经有的表，忽略创建的，但不忽略导入 
fromuser=源库名 
touser=目标库名 
tablespaces=导入到指定表空间
-----------------------命令行方式exp和imp用户数据-----------------------
--exp导出用户数据
--普通用户登录导出wjdb用户的数据(包括索引，但是不包括视图)
[oracle@localhost ~]$ exp wjdb/wangjian@192.168.15.128:1521/ORCLCDB owner=wjdb file=/home/oracle/wjdbputong.dmp log=/home/oracle/wjdbputong.log
--导出全的数据
exp wjdb/wangjian@192.168.15.128:1521/ORCLCDB file=/home/oracle/wjdbfull.dmp log=/home/oracle/wjdbfull.log full=y

--imp导入用户数据
--普通用户登录导入wjdb用户的数据(包括索引，但是不包括视图)
[oracle@localhost ~]$ imp wjdb/wangjian@192.168.15.128:1521/ORCLCDB fromuser=wjdb touser=wjdb  ignore=y file=/home/oracle/wjdbputong.dmp log=/home/oracle/wjdbputong.log



-----------------------命令行方式exp和imp表数据-----------------------
--exp导出表数据
--普通用户指定表的导出  --多个表使用tabels=(students,teachers,其他表名); 
[oracle@localhost ~]$ exp wjdb/wangjian@192.168.15.128:1521/ORCLCDB file=/home/oracle/wjdbbiao.dmp log=/home/oracle/wjdbbiao.log tables=students

--导入表数据
--普通用户指定表的导入,本质就是插入数据，要确保用户有插入的权限，访问表空间没有就授权
[oracle@localhost ~]$ imp wjdb/wangjian@192.168.15.128:1521/ORCLCDB file=/home/oracle/wjdbbiao.dmp log=/home/oracle/wjdbbiao.log tables=students


----------------------------------数据泵工具dpexp和dpimport-----------------------
---------------参数介绍 
directory --导出路径，该名称指向字典DBA_DIRECTORIES中该名称对应的路径
dumpfile --导出文件的名称，后缀为.dmp
logfile --记录导出过程日志，后缀为.log
tables|schemas|tablespaces|full --分别表示导出表、模式（用户）、表空间、全库
content={all|data_only|metadata_only} --分别表示导出数据及定义、仅导出数据、仅导出定义
query=[tb_name:] query_condition --导出表时使用，指定where子句以进行筛选，不能与content连用

-------------普通用户权限登录，导出导入当前用户数据,这个方式的弊端是导入时用户必须存在，同时必须还要赋予权限，不要忘记
--创建导出目录
[oracle@localhost 1]$ mkdir -p /home/oracle/wjdbbak
SQL> CREATE DIRECTORY wjdbbak as '/home/oracle/wjdbbak';
--授权给用户 为防止在导出时遇到权限问题
SQL> GRANT READ,WRITE ON DIRECTORY wjdbbak TO wjdb; 
--使用数据泵导出
[oracle@localhost wjdbbak]$ expdp wjdb/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdb.dmp logfile=wjdb.log

--使用数据泵导入，如果是新建的用户，记得除了赋予创建用户时基础权限外，还要重新赋予导出目录的权限GRANT READ,WRITE ON DIRECTORY wjdbbak TO wjdb; 
[oracle@localhost wjdbbak]$ impdp wjdb/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdb.dmp logfile=wjdbdaoru.log

-------------dba用户权限登录，导出其它用户的数据，这个方式最好，如果用户不存在，会自动创建用户,如果需要表空间，则需要先创建表空间
--导出数据（按照用户）
--创建导出目录
[oracle@localhost 1]$ mkdir -p /home/oracle/wjdbbak
SQL> CREATE DIRECTORY wjdbbak as '/home/oracle/wjdbbak';
--授权给dba用户
SQL> GRANT READ,WRITE ON DIRECTORY wjdbbak TO sys; 
--使用dba用户登录，导出wjdb用户的数据
[oracle@localhost wjdbbak]$ expdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbdba.dmp logfile=wjdbdba.log schemas=wjdb
会提示让输入dba信息，输入账号和密码后 直接回车即可
Username: sys as sysdba
Password:

--使用dba用户登录，导入wjdb用户的数据
[oracle@localhost wjdbbak]$ impdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbdba.dmp logfile=wjdbdba.log schemas=wjdb transform=segment_attributes:n

同样会提示让输入dba信息，输入账号和密码后 直接回车即可
Username: sys as sysdba
Password:
ORA-31684: Object type USER:"APPSTORE" already exists 会有一个这个报错 是因为我在新库中提前把用户创建了
ORA-39112，是因为要导入的DMP文件包含了一些原来的数据，比如表空间等，而新库中没有，所有报错，要解决这个问题只需加个参数即可。transform=segment_attributes:n表示导入时忽略其他数据，例如表空间

--全量导出  整个库 适合整个库进行迁移
expdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbquanl.dmp logfile=wjdbquanl.log full=y
--全量导入 整个库 适合整个库进行迁移
impdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbquanl.dmp logfile=wjdbquanll.log full=y

--------相关操作---------
SELECT * FROM DBA_DIRECTORIES; --查看所有路径
DROP DIRECTORY WJDBBAK --删除刚创建的目录

知识点:
注意1：导入时也需要按照导出时，创建的逻辑目录一样，导入前也需要创建一遍。
注意2：impdp命令在导入数据时,如果用户不存在,则会自动创建该用户,因为expdp导出的dmp文件中包含了创建用户的脚本信息(包括密码,缺省表空间,临时表空间等)。
注意3：impdp自动创建用户有一个前提条件，就是需要首先创建用户的缺省表空间和临时表空间，如果缺省表空间或者临时表空间不存在，则自动创建用户会失败，导致导入数据的失败。


-----------------------第三方工具PL/SQL Developer-----------------------

格式化结果集

数值格式化

--将字符转为数字
select cast('236' as int) from dual;

文章版权归作者所有，未经允许请勿转载。

THE END

oracle
# oracle