oracle 基本操作

標籤：

資料庫和執行個體

=============================================
Jdbc：oracle：thin：@127.0.0.1:1521：orcl
監聽：
啟動監聽：lsnrctl start
停止監聽：lsnrctl stop orcl
查看監聽狀態：lsnrctl status
NET Start OracleServiceName
NET Stop OracleServiceName

查看資料庫執行個體
show parameter service_name;
select name from V$database;
select instance_name from V$instance;
prompt 注釋輸出

spool xx.log
--要截取的語句
spool end

查看錶的備忘
select comments from user_col_comments where table_name=‘Y_JGQ_YGXXB‘;

執行個體是資料庫一組後台進程/線程以及一個共用記憶體區
一般情況下是單一實例資料庫，一個資料庫上只有一個執行個體對其進行操作。
但是真正應用叢集（Real Application Clusters，RAC）
可以有多台執行個體同時裝載並開啟一個資料庫（位於一組共用物理磁碟上）。

表的管理
=============================================
資料類型
char（10） 定長，查詢快，浪費空間 最大2000字元
varchar2（20） 變長，查詢慢，節省空間的 最大4000
number
number（5,2）五位元，2位有效小數
number（5） 五位整數
date 時間類型(日-月-年)(01-5月-05)
timestamp 時間類型比date精確
blob 二進位類型 圖片聲音

建表
create table student（
xh number(4),xm varchar(2),sex char(2),bitthday date,sal number(7,2)
);

刪除表
drop table 表名;

修改表名字
rename 表名 to 新表名

添加一個欄位
alter table 表名 add(列名);

刪除一個欄位
alter table 表名 drop column 列名;

修改欄位長度
alter table 表名 modify(列名);

修改欄位類型或者名字(不能有資料)
alter table 表名 modify(列名);

添加資料
insert into 表名(列名...) values(列名...);
insert into 表名 values(列名...);

刪除資料，可以通過儲存點中的記錄檔找回
delete from 表名;（可以通過復原恢複，刪除之前設定復原點 save point 復原點；）

復原 rollback；

快速刪除資料，不寫日誌，不能找回；
truncate table 表名;

刪除表的結構和資料
drop table 表名;

查表：
查看錶的結構
desc 表名

查表
select * from 表名;
select 列名... from 表名;
select 列名 ‘要顯示的列名‘ from 表名;

取消重複行
select distinct 列名 from 表名;

單表查詢
select * from 表名 where (列名>500 or 列名=‘xxx‘) and 列名=‘xxx‘;
select * from 表名 order by 列名;(asc預設從低到高)
select * from 表名 order by 列名1 asc 列名2 desc;(從高到低)
select max(表名), min(表名) from 表名;
select 列名1,列名2 from 表名 where 列名1=(select max(列名1) from 表名);
select 列名1,列名2 from 表名 grop by 列名2;

多表聯集查詢(笛卡爾積)(查詢條件不能小於表的個數-1)
表關聯查詢

更新
update Y_JGQ_YGXXB set RLZYH=1001 where yhh=‘P03890‘
select Y_JGQ_YGXXB.*, Z_JSQ_JGB.FHMC , Y_JGQ_YGXXB.SSJG , Z_JSQ_JGB.XNJG from Y_JGQ_YGXXB ,Z_JSQ_JGB where Y_JGQ_YGXXB.SSJG = Z_JSQ_JGB.XNJG;
select mar1.列名1,mark2.列名2 from 表名1 mar1,表名2 mark2 where mar1.列名1=mark2.列名2;
子查詢=內嵌視圖
給子查詢表指定別名別加as

分頁 一共三種
1.rownum(像一個隱藏的欄位。記錄的是行數)
select * from (select a1.* rownum rn from (select * from mylist) a1 where rownum<=10) where rn>6;

用查詢結果建立表
create table 表名1() as select * from 表名2;
合并查詢 union(並集去重) union all(並集不去重) intersect(交集) minus(差集)
select * from 表名1 union select * from 表名2;

(匯入匯出表要到oracle下的bin下進行)
匯出表
exp userid= 使用者名稱/密碼@執行個體 tables=(表名) file=path\xx.dmp;

匯出表結構
exp userid= 使用者名稱/密碼@執行個體 tables=(表名) file=path\xx.dmp rows=n;

匯出資料庫(增量備份)
exp userid= system/密碼@執行個體 full=y inctype=complete file=path\xx.dmp;

匯入表
imp userid= 使用者名稱/密碼@執行個體 tables=(表名) file=path\xx.dmp;

複製一張表
create table 建立表 as select * from 原表;

匯出資料庫
複製一張表中的資料到另外一張表
insert into 目標表 from * 原表;

使用者
=============================================
使用者：類似於其他資料庫的資料庫，這裡一個項目對應一個使用者
使用者建立時，建立對應的方案 方案裡面有資料對象(表，觸發器，視圖...)
sys 系統管理員許可權(sysdba) 資料字典的基表和動態視圖在sys裡面
system 資料庫管理員許可權(dba) 次一級的內部資料 管理用資料

建立使用者並給許可權
create user 使用者名稱 identified by 密碼
grant connect to 使用者名稱
grant select on emp to 使用者名稱 with grant option(這個許可權可以繼續先下傳遞)

刪除使用者(刪除使用者名稱會刪除此使用者建的表，必須串聯刪除)
drop user 使用者名稱 cascade

切換使用者
conn 使用者名稱/密碼

中斷連線
disc 使用者名稱

修改密碼
passw 斷行符號
舊密碼
新密碼

截取介面內容到檔案中：
spool path；
sql語句；
spool off；

查看目前登陸角色
show user；

運行特定目錄下的指令碼
start path\xx.sql;

限制登入時間
create profile 限制名 limit failed_login_attempts 3 password_lock_time 2;
alter user 使用者名稱 profile 限制名;

解鎖
alter user 使用者名稱 account unlock;

定期修改密碼
create profile 限制名 limit password_life_time 10 password_grace_time 2;
drop profile password.history cascade

用os使用者登入，不用密碼
sqlplus / as sysdba

許可權和角色
=============================================
許可權分為：系統許可權 和 對象許可權(訪問其他對象的許可權)
角色分為：自訂角色 和 預定義角色
角色是許可權的一個集合

查看角色
select * from user_role_privs;

自己建的表的操作許可權賦給別的使用者
grant select on 表名 to 別的使用者名稱
select * from 別的使用者名稱.表名

收回許可權
revoke select on 表名 from 別的使用者名稱

dba 資料庫管理員
sysdba 系統管理員
sysoper 系統操作員
connect 登陸角色

Sysdba使用者: 可以改變字元集、建立刪除資料庫、登入之後使用者是SYS（shutdown、startup）
Sysoper:使用者不可改變字元集、不能創、刪資料庫、登陸之後使用者是PUBLIC （shutdown、startup）
DBA使用者：只有在啟動資料庫後才能執行各種管理工作。
Sysdba> Sysoper>普通的DBA

資料字典 靜態資料庫系統資訊(屬於sys 是只基表和視圖的集合)
user_xxx
all_xxx
dba_xxx
user_tables 目前使用者擁有的所有表
all_table 目前使用者可以訪問到的所有表
dba_tables 所有方案所擁有的資料庫表
dba_users 所有使用者
dba_sys_privs 使用者具有的系統許可權
dba_tab_privs 使用者具有的對象許可權
dba_col_privs 顯示使用者的列許可權
dba_role_privs 使用者具有的角色

查詢所有系統許可權(一共140種)
select * from system_privilege_map order by name;

查詢所有角色
select * from dba_roles;

查詢所有對象許可權(一共16種)
select distinct privilege from dba_tab_privs;

查詢資料庫所有資料表空間
select tablespace_name from dba_tablespaces;

查詢一個角色擁有的系統許可權
select * from dba_sys_privs where grantee=‘角色名稱‘;
select * from role_sys_privs where role=‘角色名稱‘;

查詢一個角色擁有的對象許可權
select * from dba_tab_privs where grantee=‘角色名稱‘;
select * from role_tab_privs where role=‘角色名稱‘;

使用者具有的角色
select * from dba_role_privs where grantee=‘使用者名稱‘;

顯示當前資料庫全稱
select * from global_name;

顯示目前使用者可以訪問所有資料字典視圖
select * from dict where comments like ‘%grant%‘;

動態效能檢視 記錄當前常式的活動資訊
v_$開頭 其同義字以v$開頭 v_$datafile

資料表空間
=============================================
資料表空間：管理表的邏輯檔案夾
資料表空間 -》段 -》區 -》塊
不同的資料表空間可以建立相同的表，但是需要建表的使用者不同，區別表示用使用者區別，而不是資料表空間，
資料表空間是表的邏輯結構。表命名約束跟使用者有關,跟資料表空間無關
(索引單放資料表空間，觸發器單放資料表空間，提高效率，減少io)

資料表空間的作用
1.提高磁碟使用率
2.減少io讀取同時利於備份和恢複

建立資料表空間(單檔案不能超過500m)
create tablespace 資料表空間名 datafile ‘path\xxx.dbf‘
size 500m uniform size 2m;

使用資料表空間
create table 表名() tablespace 資料表空間名;

使資料表空間離線
alter tablespace 資料表空間名 offline;

使資料表空間聯機
alter tablespace 資料表空間名 online;

唯讀資料表空間
alter tablespace 資料表空間名 read only;

讀寫資料表空間
alter tablespace 資料表空間名 read write;

顯示資料表空間的所有表
select * from all_tables where tablespace_name=‘資料表空間名‘

知道表，查屬於那個資料表空間
select tablespace_name，table_name from user_tables where table_name=‘表名‘;

刪除資料表空間
drop tablespace 資料表空間 including contents and datafiles；

擴充資料表空間
1.增加資料檔案
alter tablespace 資料表空間 add datafile ‘path\xx.dbf‘ size 20m;
2.增加檔案的大小
alter tablespace 資料表空間 ‘path\xx.dbf‘ rsize 20m;
3.設定檔案自動成長
alter tablespace 資料表空間 ‘path\xx.dbf‘
autoextend on next 10m maxsize 500m;

遷移資料表空間
1確定資料檔案所在資料表空間
select tablespace_name from dba_data_files where file_name=‘path\xx.dbf‘;

2.使資料表空間離線
alter tablespace 資料表空間 offline;

3.移動資料表空間到指定位置
host move 原路徑\xxx.dbf 新路徑\xxx.dbf;
4.執行alter tablespace命令
alter tablespace 資料表空間 rename datafile ‘原路徑\xxx.dbf’ to ‘新路徑\xxx.dbf‘;

5.使資料表空間聯機
alter tablespace 資料表空間 online;

顯示資料表空間資訊
select tablespace_name from dba_tablespaces;

顯示資料表空間所含資料檔案
select file_name,bytes from from dba_data_files where tablespace_name=‘資料表空間‘;
default tablespace 授權使用者訪問的資料表空間名 temporary tablespace tem account unlock;

事務
=============================================
事務提交(會刪除儲存點，釋放鎖)
commit;

設定儲存點
savepoint 儲存點名;

取消部分事務
rollback to 儲存點名;

取消全部事務
rollback;

設定唯讀事務
set transaction read only

sql函數
=============================================
lower(char) 將字串轉化為小寫格式

upper(char) 將字串轉化為大寫格式

length(char) 返回長度

substr(char m,n) 截取字串

replace（列名 ，‘a‘ ‘b‘）替換字串

language 語言

db_name 當前資料庫名稱

host ：資料庫所在主機名稱

session_user 當前操作的使用者

select sys_context(‘userenv’，‘db_那麼’) from dual;

資料完整性
=============================================
資料遵從邏輯和商業規則可以由約束，觸發器，應用程式(過程，函數)實現

1.約束
not null，unique(可以為null),primary key,foreign key,key,check,
sex char(2) default ‘man‘ check(sex(‘man‘,‘woman‘))
references(要關聯的表名(欄位));

增加約束
alter table 表名 modify 列名 not null;
alter table 表名 add constraint 約束名 unique(列名);
alter table 表名 add constraint 約束名 check(addr in(‘‘);

刪除約束
alter table 表名 drop constraint 約束名;（刪除主鍵要帶上cascade）

顯示使用者約束資訊
select constraint_name.constraint_type,status,validated
from user_constraints where table_name=‘表名‘;
select column_name,position from user_cons_columns
where constraint_name=‘約束名‘;

列級定義約束
表級定義約束 create table xxx() references

索引
=============================================
(建索引浪費空間和插入效率，提高查詢效率)

單列索引
create index 索引名 on 表名(列名)

複合索引
create index 索引名 on 表名(列名1,列名2);(sql語句掃描時從後往前,盡量把能排除多個的列放後面)

查詢索引
select index_name,index_type from user_indexes where table_name=‘表名‘;
unique上都有索引

預存程序
=============================================
塊-》包-》函數
塊-》包-》過程

create procedure 預存程序名 is
begin
--執行部分語句
end;
/
show error

調用預存程序
exec 過程名(參數..)
declear 定義
gigin 執行
exception 錯誤

create procedure 預存程序名 is
begin
--這是pl/sql編程中的包
dbms_output.put_line(‘hello‘);
end;
/

set serveroutput on開啟輸出選項
select 列名 into V_列名 from 表名 where 列名[email protected];

java調用預存程序
CallableStatement cs=connection.prepareCall("{call 預存程序(?,?)}");

函數
create funtion 函數名(name varchar) return number 名字 number（7,1）;
begin
--執行部分
end;
/
var abc number;
call 函數() into:abc;

包
create package 包名 is
函數();
過程();
end;
/

觸發器:隱含執行的預存程序。必須定義觸發的時間和操作。
create trigger
遊標 -ref cuisor
declare
定義一個遊標類型
type 遊標名 is ref cuisor;
定義一個遊標變數
遊標名1 遊標名;
v_列名 vaechar;
begin
open 遊標名1 for select 列名 from 表名 where 列名=&xx;
loop
fetch 遊標名1 into v_列名;
exit when 遊標名1 notfound;
dbms.output.putline(‘v_列名‘);
end loop
end;

建立視圖
create or replace view myview as select * from 表名;(with read only)
drop view myview;

其他零散
=============================================
oracle版本=i代表internet，g代表grid
針對電子商務推出的版本-9i, g--grid 針對網格推出的，有10g、11g,當前最新版11g
select查詢語句的執行步驟
1.編譯(parse)
2.執行(execute)
3.提取資料(fetch)
</其他零散>

oracle服務
=============================================
1.Oracle 11g七個Windows服務功能介紹
-------------------------------------------
①OracleServiceORCL：Oracle核心服務該服務，是資料庫啟動的基礎，只有該服務啟動，Oracle資料庫才能正常啟動。(預設開機啟動，必須啟動)

②OracleOraDB11g_home1TNSListener：Orace監聽服務，服務只有在資料庫需要遠端存取的時候才需要，如PL/SQL Developer。(預設開機啟動，非必須啟動)

③OracleJobSchedulerORCL：作業調度（定時器）服務，（預設為禁用，若需開啟，需改為手動，非必須啟動）

④OracleDBConsoleorcl：oracle資料庫控制台服務，訪問地址：http://localhost:1158/em。(預設開機啟動，非必須啟動)

⑤OracleVssWriterORCL：Oracle ORCL VSS Writer Service，Oracle卷映射拷貝寫入服務，VSS（Volume Shadow Copy Service）能夠讓儲存基礎裝置（比如磁碟，陣列等）建立高保真的時
間點映像，即映射拷貝（shadow copy）。它可以在多卷或者單個卷上建立映射拷貝，同時不會影響到系統的系統能。(非必須啟動)

⑥OracleMTSRecoveryService：服務端控制。該服務允許資料庫充當一個微軟事務伺服器MTS、COM/COM+對象和分布式環境下的事務的資源管理員。（非必須啟動）

⑦OracleOraDb11g_home1ClrAgent：Oracle資料庫.NET擴充服務的一部分。（預設手動，非必須啟動）

2.oracle啟動批處理
-------------------------------------------
@echo off

echo 確定要啟動Oracle 11g 服務嗎
pause
echo Starting OracleService, please wait ...

rem 1、Oracle核心服務該服務，是資料庫啟動的基礎，只有該服務啟動，Oracle資料庫才能正常啟動。(預設開機啟動，必須啟動)
net START OracleServiceORCL

rem 2、Orace監聽服務，服務只有在資料庫需要遠端存取的時候才需要，如PL/SQL Developer。(預設開機啟動，非必須啟動)
net START OracleOraDB11g_home1TNSListener

rem 3、作業調度（定時器）服務，（預設為禁用，若需開啟，需改為手動，非必須啟動）
rem net START OracleJobSchedulerORCL

rem 4、oracle資料庫控制台服務，訪問地址：http://localhost:1158/em。(預設開機啟動，非必須啟動)
rem net START OracleDBConsoleorcl

rem 5、卷映射拷貝寫入服務，VSS(Volume Shadow Copy Service)能夠讓儲存基礎裝置(比如磁碟，陣列等)建立高保真的時間點映像(非必須啟動)
rem net START OracleVssWriterORCL

rem 6、服務端控制。該服務允許資料庫充當一個微軟事務伺服器MTS、COM/COM+對象和分布式環境下的事務的資源管理員。（預設開機啟動，非必須啟動）
rem net START OracleMTSRecoveryService

rem 7、Oracle資料庫.NET擴充服務的一部分。（預設手動，非必須啟動）
rem net START OracleOraDb11g_home1ClrAgent

pause

3.oracle停止批處理
-------------------------------------------
@echo off

echo 確定要停止Oracle 11g 服務嗎
pause
echo Stopping OracleService, please wait ...

rem 1、Oracle核心服務該服務，是資料庫啟動的基礎，只有該服務啟動，Oracle資料庫才能正常啟動。(預設開機啟動，必須啟動)
net stop OracleServiceORCL

rem 2、Orace監聽服務，服務只有在資料庫需要遠端存取的時候才需要，如PL/SQL Developer。(預設開機啟動，非必須啟動)
net stop OracleOraDB11g_home1TNSListener

rem 3、作業調度（定時器）服務，（預設為禁用，若需開啟，需改為手動，非必須啟動）
net stop OracleJobSchedulerORCL

rem 4、oracle資料庫控制台服務，訪問地址：http://localhost:1158/em。(預設開機啟動，非必須啟動)
net stop OracleDBConsoleorcl

rem 5、卷映射拷貝寫入服務，VSS(Volume Shadow Copy Service)能夠讓儲存基礎裝置(比如磁碟，陣列等)建立高保真的時間點映像(非必須啟動)
net stop OracleVssWriterORCL

rem 6、服務端控制。該服務允許資料庫充當一個微軟事務伺服器MTS、COM/COM+對象和分布式環境下的事務的資源管理員。（預設開機啟動，非必須啟動）
net stop OracleMTSRecoveryService

rem 7、Oracle資料庫.NET擴充服務的一部分。（預設手動，非必須啟動）
net stop OracleOraDb11g_home1ClrAgent

pause

4.Oracle基本開發中需要啟動的服務
-------------------------------------------
對新手來說，要是只用Oracle內建的sql*plus的話，只要啟動OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的話，OracleOraDb11g_home1TNSListener服務也要開啟。
OracleDBConsoleorcl是進入基於web的EM必須開啟的，其餘服務很少用。

註：ORCL是資料庫執行個體名，預設的資料庫是ORCL，你可以建立其他的，即OracleService+資料庫名。

ORACLE遠端連線不上
==============================================================
D:\app\lb\product\11.2.0\dbhome_1\NETWORK\ADMIN
tnsnames.ora
ORCL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
(SERVER = DEDICATED)
)
)

 

開始- 運行 - 輸入“CMD” 確定
在彈出來的視窗中，輸入SQLPLUS / AS SYSDBA 斷行符號
然後，用命令修改：
alter user 使用者名稱 identified by "密碼";
斷行符號~ 注意冒號~

oracle 基本操作