每天一点点oracle
每天一点点oracle
sqlplus / as sysdba
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 23 19:52:26 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
显示当前用户名
SQL> show user
USER is "SYS"
oralce的一些工具
工具位置：
[oracle@oracle bin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/bin
[oracle@oracle ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
HISTTIMEFORMAT='[%F %T]'
ORACLE_BASE=/u01/app //oracle安装目录
ORACLE_SID=orcl //数据库实例名
ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0.3/db_1 //oracle家目录
NLS_LANG=AMERICAN_CHINA.ZHS16GBK
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG PATH
[oracle@oracle ~]$ orabase
/u01/app
Usage: oerr facility error
Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, "ora" is the facility and "7300"
is the error. So you should type "oerr ora 7300".
If you get LCD-111, type "oerr lcd 111", and so on.
[oracle@oracle bin]$ oerr ora 7300 查看一定的报错信息
sqlplus文件的位置
[oracle@oracle admin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/sqlplus/admin
#查看当前数据库名
SQL> select name from v$database;
NAME
------------------
ORCL
SQL> show parameter db;
#查看当前数据库实例名
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
orcl
SQL> show parameter instance;
#查询当前数据名
oracle体系结构
oracle错误日志的记录位置
[oracle@oracle trace]$ pwd
/u01/app/diag/rdbms/orcl/orcl/trace
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_cache_size big integer
0
SQL> show sga
Total System Global Area 776646656 bytes
Fixed Size 2232392 bytes
Variable Size 583012280 bytes
Database Buffers 188743680 bytes 数据缓冲区
Redo Buffers 2658304 bytes 重做日志缓冲区
SQL> select username from dba_users;
创建用户
SQL> create user nod identified by nod;
User created.
下班的时候提醒领导拿走螃蟹 在冰箱
显示哪些后台进程
SQL> select * from v$BGPROCESS;
SQL> select name from v$BGPROCESS;
PADDR PSERIAL# NAME
---------------- ---------- ----------
DESCRIPTION
--------------------------------------------------------------------------------
ERROR
----------
000000008DC86A58 1 PMON
process cleanup
LGWR 日志写入进程
redo log buffer àredo log file
database buffer cache àdata file
LGWR 1/3 或者多余1M 每隔3s 执行commit
DBWR
先写日志 后写数据
oracle总是先记录变化，再修改数据缓存
DBWR 脏缓冲区个数达到指定阈值 checkpoint
CKPT
在oracle当中 / 斜杠表示重复执行上一条语句
SQL> select current_scn from v$database;
SQL> /
CURRENT_SCN
-----------
3297183
SMON 系统监控进程 system monitor
PMON 进程监控进程
ARCn 归档进程
数据库四种形态
关闭 shutdown
非装载 nomount启动
装载 mount
打开 open
SQL> select status from v$instance;
STATUS
------------------------
OPEN
关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect scott/tiger
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.
SQL> startup nomount;
ORA-01031: insufficient privileges
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2232392 bytes
Variable Size 583012280 bytes
Database Buffers 188743680 bytes
Redo Buffers 2658304 bytes
SQL> select status from v$instance;
STATUS
------------------------
STARTED
SQL> alter database mount;
Database altered.
SQL> select status from v$instance;
STATUS
------------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------------------
OPEN
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 24 15:18:19 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
instance表示数据库实例处于关闭状态
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
这个账户被锁定了
处理方法 修改用户密码
SQL> conn / as sysdba
Connected.
SQL> alter user scott account unlock identified by oracle;
User altered.
SQL> conn scott/oracle
Connected.
查看当前用户下有几张表
SQL> select * from tab;
参数文件：
参数文件中的参数值： 非默认值的参数
默认值查看方法通过官当文档进行查
静态initsid.ora
动态spfilesid.ora
SQL> show parameter name
[oracle@oracle dbs]$ ls
hc_orcl.dat init.ora lkORCL orapworcl spfileorcl.ora
[oracle@oracle dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs
spfileorcl.ora
就是关于orcl这个实例的动态初始化参数文件
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------
spfile string /u01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora
通过动态初始化参数文件创建静态初始化参数文件
SQL> create pfile from spfile;
File created.
[oracle@oracle dbs]$ ls
hc_orcl.dat init.ora initorcl.ora lkORCL orapworcl spfileorcl.ora
SQL> select * from v$log;
进行修改
SQL> alter system switch logfile;
System altered.
#查看当前日志
SQL> select * from v$log;
current表示当前
sequence是一直在累加的
警告日志路径
[oracle@oracle trace]$ pwd
/u01/app/diag/rdbms/orcl/orcl/trace
[oracle@oracle trace]$ tail -200f alert_orcl.log
#查看有没有开归档
SQL> select log_mode from v$database;
LOG_MODE
------------------------
NOARCHIVELOG 没有开
归档可以做备份和恢复
如果数据库是归档模式，可以在数据库open状态下进行备份，热备份
[oracle@oracle trace]$ tail -200f alert_orcl.log
#创建表语句
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(*) from dba_objects;
COUNT(*)
----------
75584
SQL> select count(*) from t1;
COUNT(*)
----------
75584
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.3/db_1/dbs/spfileorcl.ora
查看监听状态
[oracle@oracle ~]$ lsnrctl stauts
SQL> show user
USER is "SCOTT"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
这段话表示 在scott下可以看到四张表
scott用户
解决ora-01031insufficient privileges错误
解决system用户不能登录的问题
alter user system account unlock identified by orcl;
grant sysdba to system;
#查询用户默认表空间
SQL> select username,default_tablespace from dba_users where username='SCOTT';
Oracle备份恢复
1 系统最新SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3428264
SQL> /
CURRENT_SCN
-----------
3428308
SQL> /
CURRENT_SCN
-----------
3428309
经过检查发现SCN号是一直在变化的
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
3419021
斜杠的作用就是类似分号
进行2个语句的分割
--根据结果集创建表 表结构+表数据
create table information_schema as select * from tab;
--创建表只包含表结构，不包含数据
create table information_schema_new as select * from tab where 1=2;
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select to_char(current_timestamp(5),'YYYY-MM-DD HH24:MI:SSxFF') as 当前时间 from dual;
--TRUNCATE 将表中数据一次性全部删除
truncate table information_schema;
TRUNCATE 和 DELETE 都能把表中的数据全部删除
他们的区别是：
1. TRUNCATE 是 DDL 命令，删除的数据不能恢复； DELETE 命令是 DML 命令，
的数据可以通过日志文件恢复。
2. 如果一个表中数据记录很多， TRUNCATE 相对 DELETE 速度快。
由于 TRUNCATE 命令比较危险，因此在实际开发中， TRUNCATE 命令慎用。
逻辑运算符三个 AND OR NOT
--查询中的算术运算
select ename,sal,(sal*12+2000) from emp where sal>2000;
-- 字符串的连接工作
select (ename ||' is a '|| job) as "job detail" from emp where sal>2000;
字符串连接
NUll操作
如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格,空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL 值，除非该列被定义为非空或者主键
select ename ,comm from emp where sal<2000 and comm is null;
select ename ,comm from emp where sal<2000 and comm is not null
Between…and操作
between操作指定的范围也包含边界
select ename,sal from emp where sal between 1000 and 2000;
select ename,sal from emp where sal>=1000 and sal<=2000;
使用desc
使用内连接处理问题
--请查询出工资大于 2000 元的，员工姓名，部门，工作，工资。
内连接方式1
select a.ename,a.job,a.sal,b.dname from emp a,dept b where sal>2000 and a.deptno=b.deptno
内连接方式2
select a.ename,a.job,a.sal,b.dname from emp a inner join dept b on a.deptno=b.deptno where a.sal>2000
子查询
子查询在 SELECT、 UPDATE、 DELETE 语句内部可以出现 SELECT 语句。内部的 SELECT 语
句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类
型有：
1. 单行子查询：不向外部返回结果，或者只返回一行结果。
2. 多行子查询：向外部返回零行、一行或者多行结果。
--请查询出每个部门下的员工姓名，工资
select ename,job,sal,deptno from emp where deptno in (select deptno from dept where dname='SALES')
select ename,job,sal,deptno from emp where deptno=(select deptno from dept where dname='SALES')
在Oracle当中 不区分列名的大小写
Any Any放在比较运算符后面 表示任意的意思
--查询出 Emp 表中比任意一个销售员("SALESMAN" )工资低的员工姓名、工作、工资
select ename,job,sal from emp where sal<any (select sal from emp where job='SALESMAN')
ALL 子查询
ANY 可以表示任意的，但本案例中要求比所有销售员工资都高，那么就要使用另外一个
关键字 ALL。 ALL 与关系操作符一起使用，表示与子查询中所有元素比较
--查询出比所有销售员的工资都高的员工姓名，工作，工资。
select ename,job,sal from emp where sal>all(select sal from emp where job='SALESMAN')
Oracle中的伪列
在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中
的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。rowid & rownum
rowid
表中的每一行在数据文件中都有一个物理地址， ROWID 伪列返回的就是该行的物理地
址。使用 ROWID 可以快速的定位表中的某一行。 ROWID 值可以唯一的标识表中的一行。由
于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。
rownum
在查询的结果集中， ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二
行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。
区别
ROWNUM 与 ROWID 不同， ROWID 是插入记录时生成， ROWNUM 是查询数据时生成。 |
select
rowid,ename from emp where sal>2000;
--查询出员工表中前 5 名员工的姓名，工作，工资。
select * from emp where rownum<=5;
Oracle函数
字符函数
数字函数
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "date" from dual
select to_char(sysdate,'YYYY-MM-DD') "date" from dual;
删除用户 如果用户下有对象必须要用cascade
SQL> drop user nod cascade;
User dropped
创建用户
SQL> create user nod identified by nod;
User created
SQL> grant connect to nod;
Grant succeeded
SQL> grant resource to nod;
Grant succeeded
SQL> grant create synonym to nod;
Grant succeeded
在plsql当中使用
SQL> conn sys/orcl@test as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as SYS
SQL> conn nod/nod@test as normal;
创建synonym
SQL> create synonym myEmp for scott.emp;
Synonym created
select * from myEmp
ORA-00942: 表或视图不存在
SQL> conn scott/orcl@test
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as scott
SQL> grant all on emp to nod;
Grant succeeded
SQL> conn nod/nod@test
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as nod
SQL> select * from myEmp;
序列 sequence
创建序列
CREATE SEQUENCE MYSEQ
MINVALUE 1
START WITH 1
NOMAXVALUE
INCREMENT BY 1
NOCYCLE
CACHE 30
使用序列
select MYSEQ.nextval from dual
表空间
在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可以提高系统的效率和工作性能。 Oracle 可以存放海量数据，所有数据都在数据文件中存储。而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时Oracle 是跨平台的数据库， Oracle 数据可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢？ Oracle 采用表空间来解决。
表空间只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：
