oracle中sql如何操作

這篇文章將為大家詳細講解有關oracle中sql如何操作，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

oracle用戶sys、system區別：

sys用戶是超級用戶，具有最高權限，具有sysdba角色，有create database的權限

system用戶是管理操作員，權限也很大，具有sysoper角色，沒有create database的權限

一般來說，對數據庫維護，使用system用戶登錄就可以

sqlplus：

Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]

<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

<logon> is: {<username>[/<password>][@<connect_identifier>] | / }

            [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]

oracle一個數據庫就是一個單實例（創建一個數據庫就是創建一個單實例，默認有那么幾個用戶）

連接命令

1、conn，用法：conn 用戶名/密碼@網絡服務名 [as sysdba/sysoper]

當用特權用戶身份連接時，必須帶上as sysdba或者as sysoper

使用空用戶登錄：

[oracle@oracle11g ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 15 22:31:50 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> show user;

USER is ""

SQL> 

使用system用戶登錄

[oracle@oracle11g ~]$ sqlplus system/redhat

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 15 19:56:40 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

SQL> show user;

USER is "SYSTEM"

SQL> 

進行切換用戶到scott

SQL> conn scott/redhat;

ERROR:

ORA-28000: the account is locked     表明用戶已經被鎖定了

使用系統用戶進行解鎖

SQL> conn system/redhat

Connected.

SQL> alter user scott account unlock;  該條命令進行解鎖

User altered.

然后重新使用Scott用戶登錄

SQL> conn scott/tiger;

ERROR:

ORA-28001: the password has expired   告知用戶scott密碼過期

Changing password for scott

New password: 

Retype new password: 

Password changed

Connected.

SQL> show user;

USER is "SCOTT"

文件操作命令：

1、運行sql腳本,start /root/a.sql

SQL>start /home/oracle/a.sql

2、spool將sqlplus屏幕上的輸入到一個文件中

SQL>spool /home/oracle/b.sql;

SQL>select * from emp;

SQL>spool off;  將select * from emp查詢出來的內容保存到某個文件中

&:可以替代變量，而該變量在執行時，需要用戶輸入

SQL>select * from emp where job='&job';這里就會輸入job的一個值來替代

SQL> select * from emp where job='&job';   

Enter value for job: MANAGER

old   1: select * from emp where job='&job'

new   1: select * from emp where job='MANAGER'

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7566 JONES      MANAGER         7839 02-APR-81       2975

        20

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850

        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450

顯示和設置環境變量：

SQL> show linesize;

linesize 80

SQL>set linesize 50;

創建用戶

在oracle中要創建一個新的用戶使用create user語句，一般是具有dba的權限才能使用

SQL> create user xiaoming identified by redhat;

User created.

SQL> show user;

USER is "SYSTEM"

給用戶修改密碼，需要dba權限或者alter user的系統權限

SQL> password xiaoming

Changing password for xiaoming

New password: 

Retype new password: 

Password changed

或者

sql>alter user 用戶名 identified by 新密碼；

SQL> alter user xiaoming identified by redhat;

User altered.

note:新創建的用戶并不能馬上能夠登錄，創建的新用戶沒有任何權限，所以登錄不了

SQL> conn xiaoming/huang;

ERROR:

ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon deniedWarning: You are no longer connected to ORACLE.

SQL> show user;

USER is ""

刪除用戶：

一般以dba的身份去刪除某個用戶，如果用其他的用戶去刪除某個用戶，需要具有drop user的權限

比如：drop user 用戶名 [cascade]

在刪除用戶時，注意，如果刪除的用戶已經創建了表，那么就需要在刪除的時候加上一個參數cascade

這個參數的意思就是刪除用戶以及用戶創建的表

用戶管理----》授權

創建的新用戶是沒有任何權限的，連登錄都不行，需要為其制定相應的權限，賦予權限用grant命令

回收權限的命令為revoke

oracle事先就有權限這個定義，有兩種

1、系統權限：用戶對數據庫訪問的相關權限（建庫建表建索引登錄數據庫等等）

create session（系統權限140個）

2、對象權限：用戶對其他用戶的數據對象訪問操作的權限

數據對象：每個用戶創建的表、視圖、觸發器等等（25個）

oracle角色：為了授予權限方便，事先定義了一些角色，賦予了一些權限

從系統權限中選出某些權限賦予給指定的角色

connect就是其中的一個角色（包含7個權限）

grant connect to xiaoming 

角色也分兩種：

1、自定義角色：自己定義角色

2、預定義角色：數據庫已經制定好的角色（connect就是預定義角色）

角色舉例：

connect

dba：授予dba角色就會有dba系統的所有權限

resource：可以讓某個用戶在表空間建表

例子：

SQL> grant connect to xiaoming ;  將connect角色授予給xiaoming，并具備一些權限

Grant succeeded.

再次使用用戶xiaoming登錄：

SQL> conn xiaoming/redhat;   這樣授權之后，用戶xiaoming就可以登錄了

Connected.

剛創建的用戶并沒有任何表，新創建的用戶能否創建表呢？

SQL> conn xiaoming/redhat;

Connected.

SQL> show user;

USER is "XIAOMING"

SQL> create table student(id number,name varchar2(20));

create table student(id number,name varchar2(20))

*

ERROR at line 1:

ORA-01031: insufficient privileges

由上述報錯發現，授予connect角色并不能創建表，那么在加上resource角色呢？

切換用戶，并授予角色給xiaoming

SQL> show user;

USER is "XIAOMING"

SQL> conn system/redhat;

Connected.

SQL> grant resource to xiaoming;

Grant succeeded.

再次創建表

SQL> show user;

USER is "XIAOMING"

SQL> conn system/redhat;

Connected.

SQL> grant resource to xiaoming;

Grant succeeded.

SQL> conn xiaoming/redhat

Connected.

SQL> create table student(id number,name varchar2(20));

Table created.

SQL> desc student;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER

 NAME                                               VARCHAR2(20)

可以看出表創建成功，這就是resource角色的作用

對象權限：用戶對其他用戶的數據對象訪問操作的權限

1、select

2、insert

3、update

4、delete

5、all

6、create index等等

數據對象：每個用戶創建的表、視圖、觸發器等等（25個）

xiaoming這個用戶能否查詢scott用戶下的某個表？

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-00942: table or view does not exist   可以清楚的看出不能訪問scott下的表emp

該如何操作呢？

由于表emp是scott用戶下面的，于是需要使用scott用戶向xiaoming用戶進行授權

SQL> conn scott/redhat

Connected.

SQL> grant select on emp to xiaoming;   使用scott用戶進行授權

Grant succeeded.

SQL> conn xiaoming/redhat   連接xiaoming這個用戶

Connected.

SQL> select * from emp;    查詢表emp，但是依然失敗，由于是scoot的表emp

select * from emp

              *

ERROR at line 1:

ORA-00942: table or view does not exist

所以需要下面這樣進行查詢

SQL> select * from scott.emp;  scott用戶下面的表

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20

收回權限revoke

scott收回xiaoming有查詢的權限

revoke select on emp from xiaoming；

revoke收回權限，必須是原始用戶對其賦予權限的那個用戶收回

SQL> show user;

USER is "XIAOMING"

SQL> conn scott/redhat;

Connected.

SQL> revoke select on emp from xiaoming;

Revoke succeeded.

對權限的維護：

1、如果是對象權限

希望xiaoming用戶可以去查詢scott的emp表，而且還希望xiaoming可以把這個權限能夠給另外的用戶？

利用scott用戶進行如下授權

SQL> show user;

USER is "SCOTT"

SQL> grant select on emp to xiaoming with grant option;

Grant succeeded.

然后新建一個用戶，授予connect角色權限

SQL> conn system/redhat

Connected.

SQL> create user xiaobai identified by redhat;

User created.

SQL> grant connect to xiaobai;

Grant succeeded.

然后使用xiaoming用戶給xiaobai用戶進行授權

SQL> conn xiaoming/redhat

Connected.

SQL> grant select on scott.emp to xiaobai;

Grant succeeded.

最后使用xiaobai用戶進行查詢emp表

SQL> conn xiaobai/redhat

Connected.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20

2、如果是系統權限

system用戶給xiaoming授權，并且使用戶能夠授權權限給其他用戶

grant connect to xiaoming with admin option;   with admin option這里不同而已

使用profile管理用戶口令

profile是口令限制，資源限制的命令集合，當監理數據庫時，oracle會自動建立

名稱為default的profile，當建立用戶沒有指定profile選項，那么oracle就會將default分配給用戶

1、賬戶鎖定

指定該賬戶登錄時最多可以輸入密碼的次數，也可以指定用戶鎖定的時間，一般用dba的身份去執行該命令

例如：指定tea這個用戶最多只能嘗試三次登錄，鎖定時間為兩天

創建profile文件（規則）

SQL>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;   -----》lock_account為profile名稱隨便起

SQL>alter user tea profile lock_account;  給用戶tea添加上profile文件約束

2、解決鎖定：

SQL> conn system/redhat;

Connected.

SQL> alter user scott account unlock;

User altered.

SQL> conn scott/redhat;

Connected

3、終止口令

為了讓用戶定期修改密碼可以使用終止口令的指令來完成，同樣這個命令也需要dba身份來操作

例子：給前面創建的用戶tea創建一個profile文件，要求該用戶每隔10天要修改自家的登錄密碼

寬限為2天        ----->寬限期限表示過了10之后，提醒你還有兩天時間進行修改，相當于一共12天

SQL>create profile myprofile limit password_life_time 10 password_grace_time 2;

SQL>alter user tea profile myprofile;

4、口令歷史

如果希望用戶在修改密碼時，不能使用以前使用過的密碼，口令歷史就是這樣將口令修改的信息存放在一個數據字典中

這樣當用戶修改密碼時，oracle就會對新舊密碼進行比較，當發現新舊密碼一樣時 ，就會提示用戶需要重新輸入密碼

例子：

1、建立profile文件

SQL>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;

---->password_reuse_time:指定口令可重用時間即10天后就可以重用（新舊密碼一樣，需要過10天才能使用）

2、分配給某個用戶---》tea

SQL>alter user tea profile password_history;

刪除profile

當不需要某個profile文件時，可以刪除該文件

SQL>drop profile password_history [cascade];

用此profile約束過的用戶都將失效

cascade：級聯關系

關于“oracle中sql如何操作”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。