每天一點點oracle
sqlplus / as sysdba
[[email protected] ~]$ 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的一些工具
工具位置：
[[email protected] bin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/bin
[[email protected] ~]$ 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
[[email protected] ~]$ 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.
[[email protected] bin]$ oerr ora 7300 檢視一定的報錯資訊
sqlplus檔案的位置
[[email protected] 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錯誤日志的記錄位置
[[email protected] trace]$ pwd
/u01/app/diag/rdbms/orcl/orcl/trace
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_cache_size big integer
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
[[email protected] ~]$ 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
[[email protected] dbs]$ ls
hc_orcl.dat init.ora lkORCL orapworcl spfileorcl.ora
[[email protected] 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.
[[email protected] 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是一直在累加的
警告日志路徑
[[email protected] trace]$ pwd
/u01/app/diag/rdbms/orcl/orcl/trace
[[email protected] trace]$ tail -200f alert_orcl.log
#檢視有沒有開歸檔
SQL> select log_mode from v$database;
LOG_MODE
------------------------
NOARCHIVELOG 沒有開
歸檔可以做備份和恢複
如果資料庫是歸檔模式，可以在資料庫open狀态下進行備份，熱備份
[[email protected] 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
檢視監聽狀态
[[email protected] ~]$ 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 是查詢資料時生成。 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/[email protected] as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as SYS
SQL> conn nod/[email protected] as normal;
建立synonym
SQL> create synonym myEmp for scott.emp;
Synonym created
select * from myEmp
ORA-00942: 表或視圖不存在
SQL> conn scott/[email protected]
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/[email protected]
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 采用表空間來解決。
表空間隻是一個邏輯概念，若幹作業系統檔案（檔案可以不是很大）可以組成一個表空間。表空間統一管理空間中的資料檔案，一個資料檔案隻能屬于一個表空間。一個資料庫空間由若幹個表空間組成。如圖所示：
轉載于:https://www.cnblogs.com/nodchen/p/9744706.html