自己整理的oracle中常見的命令

標籤：自己整理的oracle中常見的命令

自己整理的oracle中常見的命令：

oracle11g已經不支援isqlplus了。內建了oracle sql developer

1-----使用者多次登入錯誤，被鎖定：

       ALTER USER username ACCOUNT UNLOCK；

2-----修改使用者密碼：

       alter user system identifid by manager;

3-----建立/刪除使用者：

       create user lisi identified by lisi;

       drop user lisi cascade;

4.1-----授予與收回系統許可權：

       grant create session to lisi;才能登入連結到資料庫；

       grant  unlimited tablespace to lisi;才能建立資料表空間，但不能建表；

       grant create table to lisi;在上述兩個授權成功之後，授予建表許可權，就能建表；

       revoke create session to lisi;

       revoke create unlimited tablepspace to lisi;

       revoke create table to lisi;

###### grant create session to public;

###### select* from user_sys_privs;查詢目前使用者所擁有的系統許可權

4.2------授予與回收對象許可權：

       grant select  on test1 to lisi;授予查詢另一個使用者表的許可權，授予delete,update,insert許可權不再贅述；

       grant all on test1 to lisi;授予對錶test1的所用許可權；

       grant update(name) on test1 to lisi;對象許可權控制到列；，insert不再贅述；

###### 查詢，刪除不能控制到列；

5------許可權，角色，資料表空間查詢：

        select * from system_privilege_map order by name;查詢oracle中所有的系統許可權，一般是dba;208個

        select * from dba_roles;查詢oracle的所有角色：55個

        select distinct privilege from dba_tab_privs;查詢oracle所有對象許可權；17個

        select tablespace_name from dba_tablespaces; 查詢資料庫的資料表空間；

        select * from dba_role_privs where grantee=‘使用者名稱‘；查詢某個使用者具有的角色注意使用者名稱區分大小寫

        select * from dba_sys_privs where grantee=dba’；查詢某個角色所擁有的系統許可權；

        或者是select * from dba_sys_privs where grantee=‘dba’；查詢某個角色所擁有的系統許可權；

6------運行編輯,匯出sql指令檔：

       start e:\oracle\aa.sal;運行sql指令碼；

       edit  e:\oracle\aa.sql;編輯sql指令碼；

       spool e:\oracle\aa.sql;建立記錄檔案，開始記錄，然後輸入命令：

       spool off;記錄儲存；

7------使用profile系統管理使用者口令：

賬戶鎖定：

建立profile檔案：

create profile lock_account limit failed_login_attempts 3 password_lock_time 2;設定，三次登陸失敗後，限制登入2天；

修改profile：alter user lisi profile lock_account;

給賬戶解鎖：

 ALTER USER username ACCOUNT UNLOCK；

8------事物處理之建立savepoint和rollback;

        savepoint aa;建立儲存點；

        rollback to aa;復原到儲存點aa；

#######注意;復原的條件是，事物沒有提交（commit),如果事物提交了，復原時，提示savepoint不存在；

9------分頁

9.1-----根據rowid分頁：

select * from test1 where rowid in 

(select id from 

(select rownum rn,rid from 

(select rowid rid,cid from test1 order by cid desc)

where ronnum<1000)

where rn>9980)

order by cid desc;

9.2-----分析函數分頁：

select * from 

(select t.*,row_number() over (order by cid desc )rk from test)

where rk<1000 and rk>9980;

9.3-----使用rownum分頁：

select * from 

(select a.*,rownum rn from 

(select ename,job from emp) a where rownum<=10)

where rn>=5;

#######:我自己的方法：select * from (select rownum rn,ename,job from emp ) where rn>5 and rn<=10 ;

10-------資料庫的邏輯備份與恢複：

10.1----非互動式匯出：

1.匯出方案：

$exp scott/[email protected] owner=scott file=e:\oracle_workspace\scott.dmp;匯出自己的方案；

$exp system/[email protected] owner=(system,scott) file=e:\oracle_workspace\system.dmp;

註：如果要匯出其他方案，則需要dba的許可權或者是擁有exp_full_database的許可權，例如system就可以匯出任何方案；

2.匯出資料庫：

$exp userid=system/[email protected] full=y inctype=complete file=e:\oracle_workspace\oracle.dmp;

註：匯出資料庫是指利用export匯出所有資料庫中的對象和資料，要求該使用者具有dba的許可權或者是exp_full_database的許可權；

########注釋：互動式在這裡不做詳細介紹：

其實互動式匯出就是先輸入：$exp scott/[email protected]，然後根據提示選擇緩衝，匯出類型，檔案等一系列，，，，

3.匯出表：

$exp scott/tiger tables=emp,dept file=e:\oracle_workspace\scott_emp_dept.dmp grants=y 

說明:把scott使用者裡兩個表emp,dept匯出到檔案e:\oracle_workspace\scott_emp_dept.dmp

$exp scott/tiger tables=emp query=/"where job=/‘salesman/‘ and sal/<1600/" file=e:\oracle_workspace\scott_emp_salesman.dmp

說明:在exp裡面加上匯出emp的查詢條件job=‘salesman‘ and sal<1600

10.2----非互動式匯入：

1.匯入表：

$imp userid=scott/[email protected] tables=(emp) file=e:\oracle_workspace\emp.dmp;匯入自己的表；

$imp userid=system/[email protected] tables=(emp) file=e:\oracle_workspace\scott_emp.dmp;匯入表到其他使用者；

$imp userid=scott/[email protected] tables=(emp) file=e:\oracle_workspace\empstrcture.dmp rows=n;匯入表的結構；

$imp userid=scott/[email protected] tables=(emp) file=e:\oracle_workspace\empdata.dmp ignore=y;匯入資料；

2.匯入方案：

$imp userid=scott/tiger file=e;\oracle_workspace\scott.dmp;匯入自身的方案；

$imp userid=system/manager file=e:\oracle_workspace\system_scott.dmp;匯入其他的方案，一般要求具有dba的權

 

限

3匯入資料庫:

$imp userid=system/manager full=y file=e:\oracle_workspace\oracle.dmp;

########注釋：互動式在這裡不做詳細介紹：

11--------資料字典和動態效能檢視：

select table_name from user_tables;顯示目前使用者所擁有的所有表；

select table_name from all_tables;顯示目前使用者可以訪問的所有表；

select table_name from dba_tables; 顯示所有方案擁有的資料庫表，一般要求dba和有select any tables的系統許可權才會生效；

12--------建立，使用資料表空間：

create tablespace data01 datafile ‘e:\oracle_workspace\data01.dbf‘ size 20m unform 128k;

建立名稱為data01的資料表空間，並為資料表空間建立data01.dbf的資料檔案，區的大小為128K；

create table mydata(deptno number(4),dname varchar2(24)) tablespace data01;

使用資料表空間，在資料表空間建立表mydata;

alter tablespace data01 add datafile ‘e:\oracle_workspace\test.dbf‘ size 20m;

增加資料檔案；

alter database datafile ‘e:\oracle_workspace\test.dbf‘resize 20m;

增加2資料檔案的大小；

alter database datafile ‘e:\oracle_workspace\test.dbf‘ autoextend on next 10m maxsize 500m;

設定檔案的自動成長；

13------查看錯誤：

show error;當輸入語句或者命令提示錯誤時，使用此命令顯示詳細的錯誤資訊；

14-----oracle查看資料檔案, 控制檔案, 及記錄檔命令

一. 查看資料檔案

SQL> select name from v$datafile;

二. 查看控制項檔案

SQL> select name from v$controlfile;

三. 查看記錄檔

SQL> select member from v$logfile;

四查看所有的管理員：    

SQL>select * from v$pwfile_users;  

15------先查詢空閑空間

    select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space; 

16------關於許可權的傳遞問題（最好以自己資料庫的測試為準），下面是我在orcle11g下測試的結果

一.系統許可權

1）.舉例，sysdba授予scott使用者的create session with admin option ,然後scott授予wangwu許可權create session (沒有加with admin option )

測試結果如下，

1.當sysdba revoke create session from scott時，wangwu任然可以登入，但不能將許可權授予其他使用者（沒有加with admin option ）；

2）.舉例，sysdba授予scott使用者的create session with admin option ,然後scott授予wangwu許可權create session with admin option 

1.當sysdba revoke create session from scott時,wangwu還可以把此許可權授予其他使用者。

二.對象許可權

舉例，scott授予lisi許可權select on emp with grant option ,lisi授予xiaoming許可權select on emp with grant option

1.當scott收回許可權revoke select on emp from lisi,xiaoming的存取權限，和授予許可權全部被收回。

17-----查看進程資訊

1)、從v$process中查詢啟動的後台進程資訊

2)、查看啟動了幾個DBWR進程

select * from v$process where program like ‘%DBW%‘;

查看DBWR進程個數: show parameter db_wr，DBWR進程個數跟髒資料的產生有一定的影響。

修改DBWR進程個數: 

alter system set db_writer_processes=3 scope=memory; --會報"無法修改指定的初始化參數"錯誤

alter system set db_writer_processes=3 scope=spfile;--修改成功，下次啟動時才生效

3)、查看啟動了幾個ARC進程

select * from v$process where program like ‘%ARC%‘; 

4）查看是否有歸檔日誌：archive log list；

5）查看錶空間的具體資訊

select * from dba_data_files;

6）查看錶空間

select * from v$tablespace;

18-----11g中的自動記憶體管理

i1、自動記憶體管理，即AMM，Automatic Memory Management。只為Oracle的使用整體分配一個總的記憶體大小就可以了，不必像10g那樣具體分配SGA和PGA的大小。

i2、初始化參數statistics_level為typical或all，才可以啟動AMM。

show parameter statistics_level; --查看AMM

show parameter memory; --查看整個oracle佔用了多大記憶體

alter system set memory_max_target=900m;--設定記憶體最大可以達到多大

i3、新的初始化參數memory_target來定義了整個記憶體的大小，即SGA加上PGA的總的大小。

alter system set memory_target=500m; --memory_target設定的值要不大於memory_max_target

i4、新的初始化參數memory_max_target來定義了memory_target最大可以達到的值。

alter system set memory_max_target=900m scope=spfile; --下次啟動生效，scope=spfile不能省略否則報錯

i5、注意：如果使用AMM，則sga_target和pga_aggregate_target的值應該設定成0。

show parameter sga_target;

show parameter page_aggregate_target;

alter system set sga_target=0m;

alter system set pag_aggregate_target=0;

i6、11g中的後台進程MMAN，用於進行自動記憶體管理。

19 ------開啟顯示操作時間的開關，在底部顯示操作時間

set timing on/off;

eg、sql> insert into tb_stu values(‘0001‘, ‘zhangsan‘, 24); 

1 row inserted

executed in 0.015 seconds

20.設定唯讀事務。

SQL> set transaction read only;

本文出自 “7439523” 部落格，請務必保留此出處http://7449523.blog.51cto.com/7439523/1601796

自己整理的oracle中常見的命令