【oracleocp知識點二】
1.數據庫操縱語言
DML語句實在下面的時候執行
向表添加新行
更新表現有的行
從表刪除現有行
一個事務處理是由一系列的DML語句邏輯組成
A.insert 每次插入一行數據 字符和日期的需要單引號引起來，日期的插入需要to_date()處理
SQL> insert into dept values(54,'',null); //插入空值
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
54
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
一次插入多條數據使用子查詢，即insert into es select * from emp;
DML事務結束需要commit/DDL/DCL/exit/conn
取消事務 rollback/quit
SQL> alter table dept modify(loc default 'TX');
Table altered.
SQL> insert into dept values (56,default,default);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept where deptno=56;
DEPTNO DNAME LOC
---------- -------------- -------------
56 TX
SQL> desc user_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
SQL> select column_name,data_default from user_tab_columns where table_name='DEPT';
COLUMN_NAME DATA_DEFAULT
-------------- --------------
DEPTNO
DNAME
LOC 'TX'
B.update 根據需要可以更新一條或者多條記錄
C.delete 不釋放表空間，truncate 刪除釋放空間
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
112 8
SQL> delete e3;
112 rows deleted.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
0 8
SQL> truncate table e3;
Table truncated.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
0 0
表經常delete的話會造成空間的浪費，就是說刪除後空間沒有釋放
解決方法：alter table e3 mvoe;或者導出表
SQL> c/emp/e3
1* select count(*) from e3
SQL> /
COUNT(*)
----------
196
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
196 5
SQL> delete e3 where rownum<180;
179 rows deleted.
SQL> select count(*) from e3;
COUNT(*)
----------
17
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
17 5
SQL> alter table e3 move;
Table altered.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
17 4
2.事務
DDL/DCL語句自動提交
事務結束與開始
save point 保存點 rollback to 保存點
3.數據類型
vrachar2 4000
char 2000
number(p,s)
interval 間隔
SQL> create table t1(id number,t1 date,t2 timestamp,t3 timestamp with time zone,
t4 timestamp with local time zone,t5 interval year(5) to month,
t6 interval day(6) to second );
Table created.
SQL> insert into t1 values(1,sysdate,sysdate,sysdate,sysdate,'1-10','3 2:10:10');
1 row created.
SQL> select * from t1;
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
1 19-JUL-14
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
19-JUL-14 09.53.11.000000 PM
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
19-JUL-14 09.53.11.000000 PM +08:00
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
19-JUL-14 09.53.11.000000 PM
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
+00001-10
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
+000003 02:10:10.000000
ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
還原scott資料用戶
SQL>startup ?/rdbms/admin/utlsampl
SQL>drop user sctt scade;
SQL>select username,sid,serial# from v$session where username='SCOTT';
SQL>alter system kill session '44,21';
4.DDL的管理及操作
數據庫對象：表 視圖 序列 索引 同義詞
命名規則：以字母開頭、1-30字符、A-Z/a-z/0-9/_/$、字符開頭
database link 128
庫名 8
實例名 12
使用內部保留字，小寫加上雙引號即可
create table "user"....
alter table t1 modify (sal default null);
5.約束
自動命名(sys)、手動命名
列級約束 非空只能在列級定義 逗號隔開
表級約束 空格隔開
非空約束 not null constraint
SQL> create table b(id number not null);
Table created.
SQL> insert into b values(null);
insert into b values(null) *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."B"."ID")
SQL> alter table b modify (id null);
Table altered.
SQL> alter table b modify (id constraint b_id_null not null);
Table altered.
唯一性約束，自動簡歷唯一索引
SQL> create table c(id number unique);
Table created.
SQL> insert into c values(1);
1 row created.
SQL> /
insert into c values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014385) violated
SQL> select constraint_name from user_constraints where table_name='C';
CONSTRAINT_NAME
------------------------------
SYS_C0014385
SQL>alter table c drop constraint sys_c0014385
Table altered.
SQL> alter table c add constraint a_id_u unique(id);
Table altered.
主鍵約束
一個表只能有一個主鍵
主鍵是唯一的並且非空
可以聯合主鍵，聯合主鍵要求每列都非空
主鍵唯一定位一行，所有主鍵也叫邏輯ROWID
主鍵不是必須的，可以沒有
主鍵是通過索引實現的
索引的名稱和主鍵的名稱相同
外鍵約束
表級列級都可以定義
表級定義關聯到子表中的列
執行刪除操作時會出現錯誤，特別注意
檢查約束 check constraint
check.....
違反約束 violating constraint
啟用/停用 enable disable
建立表使用子查詢
create table e as select * from emp; 新表不包含數據
create table e as select * from emp where 0=1;
alter table e read only; 使表只讀
alter table e read write;
10g沒有只讀這個說法，所以只能建成一個試圖
SQL> drop table c;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
C BIN$/o6ox8o+iMjgQ2YAqMDn3Q==$0 TABLE 2014-07-19:23:38:02
SQL> flashback table c to before drop;
Flashback complete.
SQL> alter table c add (name varchar2(20));
Table altered.
SQL> select * from c;
ID NAME
---------- --------------------
1
SQL> alter table c modify(name varchar2(28));
Table altered.
SQL> alter table c rename column name to dname;
Table altered.
SQL> alter table c drop column dname;
Table altered.
SQL> alter table c set unused column sex; //標記為不使用，在業務低峰期刪除
Table altered.
SQL> alter table c drop unused columns;
Table altered.
SQL> alter table c drop column dname;
Table altered.
6.視圖
本身就是一個查詢語句
限制數據訪問
復雜查詢簡單化
提供數據獨立性
沒有自己的數據，來源於查詢結果
簡單試圖 一個表 dml不限制
復雜試圖 一個或者多個表 dml限制
create [or replace] [force|noforce] view
.... as subquery
with check option ...
with read only ...
子查詢不能包含order by
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SQL> grant create view to scott; 授權創建試圖
SQL> create force view v1 as select * from v;
Warning: View created with compilation errors.
SQL> select object_name,status from user_objects where object_name='V1';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
V1
INVALID
SQL> select text from user_views where view_name='V1';
TEXT
--------------------------------------------------------------------------------
select * from v
創建基表，執行查詢
SQL> select object_name,status from user_objects where object_name='V1';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
V1
VALID
SQL> create or replace view empsal as select * from emp where sal>2000 with check option;
View created.
SQL> select sal from empsal;
SAL
----------
2975
2850
2450
3000
5000
3000
SQL> update empsal set sal=1799 where sal=2450;
update empsal set sal=1799 where sal=2450
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> update empsal set sal=7000 where sal=2450;
1 row updated.
drop view empsal;
視圖DML操作限制
碰到如下語法不能刪除試圖行數據
使用分組函數
使用group by語法
使用去除重復行語句
使用了rownum偽列
修改限制
使用分組函數
使用group by語法
使用去除重復行語句
使用了rownum偽列
使用了表達式
insert操作限制
使用分組函數
使用group by語法
使用去除重復行語句
使用了rownum偽列
使用了表達式
非空約束的列沒在select列表中引用
7.索引
自動建立
手動建立
creat unique|bitmap index ...on table...
多個列上建立索引需要注意順序
user_indexes
user_ind_columns
基於函數的建立索引
SQL> set autot trace exp;
SQL> select * from emp where ename='SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='SCOTT')
SQL> create index emp_ename_i on emp(ename);
Index created.
SQL> select * from emp where ename='SCOTT';
Execution Plan
----------------------------------------------------------
Plan hash value: 549418132
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I | 1 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
SQL> select * from emp where substr(ename,1)='KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("ENAME",1)='KING')
SQL> create index emp_e_i on emp(substr(ename,1));
Index created.
SQL> select * from emp where substr(ename,1)='KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 1426330053
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | EMP_E_I | 1 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SUBSTR("ENAME",1)='KING')
8.序列
自動產生唯一值
是一個共享對象
典型的用於創建主鍵值
可替代應用程序代碼
如果將序列值緩存在內存中可以提交訪問效率
create sequence ...
increment by ...
start with ...
...
SQL> create sequence s increment by 1 start with 50;
Sequence created.
SQL> insert into dept values(s.nextval,'D'||s.nextval,'LL'||s.nextval);
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
54
56 TX
50 D50 LL50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select s.currval from dual;
CURRVAL
----------
50
SQL> desc user_sequences
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SQL> select s.currval+increment_by from user_sequences where sequence_name='S';
S.CURRVAL+INCREMENT_BY
----------------------
51
SQL> alter sequence s cycle cache 10;
Sequence altered.
SQL> select s.currval+increment_by from user_sequences where sequence_name='S';
S.CURRVAL+INCREMENT_BY
----------------------
51
SQL> select last_number from user_sequences where sequence_name='S';
LAST_NUMBER
-----------
51
SQL> insert into dept values(s.nextval,'D'||s.nextval,'LL'||s.nextval);
1 row created.
SQL> select last_number from user_sequences where sequence_name='S';
LAST_NUMBER
-----------
61
SQL> select s.currval+increment_by from user_sequences where sequence_name='S';
S.CURRVAL+INCREMENT_BY
----------------------
52
SQL> alter sequence s maxvalue 55;
Sequence altered.
SQL> create table dept1 as select * from dept;
Table created.
SQL> insert into dept1 values(s.nextval,'D'||s.nextval,'LL'||s.nextval);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
54
56 TX
50 D50 LL50
51 D51 LL51
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
52 D52 LL52
53 D53 LL53
54 D54 LL54
DEPTNO DNAME LOC
---------- -------------- -------------
55 D55 LL55
1 D1 LL1
******序列需要調用後才會有當前值，否則是沒有的。********
序列中start with不能修改，其他都可以修改
序列不連續
發生回滾
系統崩潰
被其他對象調用過
12c實現自動增長
9.同義詞
對象的別名
共有的
私有的
create synonym ... for ...
SQL> grant create synonym to scott;
Grant succeeded.
SQL> l
1* grant create public synonym to scott
SQL> /
Grant succeeded.
SQL> create synonym es for empsal;
Synonym created.
SQL> desc es;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
給表起個別名
SQL> desc user_synonyms
Name Null? Type
----------------------------------------- -------- ----------------------------
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
公有的同義詞，在當前用戶下有兩個同名的同義詞，一個私有的，一個是共有的，訪問時先訪問的是私有的
SQL> desc all_synonyms
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SYNONYM_NAME VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
DB_LINK VARCHAR2(128)
1.控制用戶訪問
系統權限
對象權限
角色權限
數據庫安全性
系統安全
數據安全
create user identifued by password; 12c 公有賬號(PDB)、私有賬號(CDB)c##開頭的
grant privilege,... to suer
SQL> desc system_privilege_map;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
不同版本系統權限個數不一樣，
SQL> select name from system_privilege_map;
SQL> create user u1 identified by oracle;
User created.
SQL> conn u1/oracle
ERROR:
ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to u1;
Grant succeeded.
SQL> grant unlimited tablespace to u1;
Grant succeeded.
11g建立用戶之後有默認使用表空間的權限，而10g則沒有該權限，需要給用戶授予訪問表空間的權限
12c cdb庫必須是C##開頭的，
create user c##u1 identified by oracle;
show con_name;
插件數據庫啟動在mont或者open狀態
desc v$pdbs
select con_id,name from v$pdbs; 查看插件數據可數量
select * from user_sys_privs; 查看用戶具備系統的權限
回收權限指令
revoke create table from u1;
select * from dba_sys_privs where grantee='U1';
級聯授權問題
grant create table to scott with admin option; //sys
grant create table to v1;//scott
如果收回scott的create權限，此時v1的還是有create權限，但是此時的授予者變成了sys而不是scott
對象權限
SQL> grant select on scott.emp to u1;
Grant succeeded.
SQL> grant update(sal)on scott.emp to u1;
Grant succeeded.
SQL> grant select on scott.dept to u1 with grant option; 對象權限級聯
Grant succeeded.
SQL> select * from user_tab_privs;
GRANTEE OWNER
------------------------------ ------------------------------
TABLE_NAME GRANTOR
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
U1 SCOTT
DEPT SCOTT
SELECT YES NO
U1 SCOTT
EMP SCOTT
SELECT NO NO
GRANTEE OWNER
------------------------------ ------------------------------
TABLE_NAME GRANTOR
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SQL> update scott.emp set sal=sal+1;
14 rows updated.
SQL> update scott.emp set comm=1;
update scott.emp set comm=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
對象權限的回收，級聯的權限也會收回的。例如A對象的有某個權限，A對象有創建了B，當A的某個權限被收回時，B也相應該的權限也收回去了。
角色權限
create role manager;
create role c##manager; 12c
connect resource dba select_catlog_owner recovery_catalog_owner
SQL> desc dba_roles
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
AUTHENTICATION_TYPE VARCHAR2(11)
SQL> select role from dba_roles
2 ;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
LOGSTDBY_ADMINISTRATOR
DBFS_ROLE
AQ_ADMINISTRATOR_ROLE
ROLE
------------------------------
AQ_USER_ROLE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
ADM_PARALLEL_EXECUTE_TASK
GATHER_SYSTEM_STATISTICS
JAVA_DEPLOY
RECOVERY_CATALOG_OWNER
SCHEDULER_ADMIN
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
ROLE
------------------------------
GLOBAL_AQ_USER_ROLE
OEM_ADVISOR
OEM_MONITOR
WM_ADMIN_ROLE
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
EJBCLIENT
JMXSERVER
JAVA_ADMIN
ROLE
------------------------------
CTXAPP
XDBADMIN
XDB_SET_INVOKER
AUTHENTICATEDUSER
XDB_WEBSERVICES
XDB_WEBSERVICES_WITH_PUBLIC
XDB_WEBSERVICES_OVER_HTTP
OLAP_DBA
ORDADMIN
OLAP_XS_ADMIN
CWM_USER
ROLE
------------------------------
OLAP_USER
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
SPATIAL_CSW_ADMIN
CSW_USR_ROLE
APEX_ADMINISTRATOR_ROLE
OWB$CLIENT
OWB_DESIGNCENTER_VIEW
OWB_USER
MGMT_USER
SQL> select * from role_role_privs where role='CONNECT';
no rows selected
SQL> c/_role/_sys
1* select * from role_sys_privs where role='CONNECT'
SQL> /
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL> c/_sys/_tab
1* select * from role_tab_privs where role='CONNECT'
SQL> /
no rows selected
SQL> create role r1;
Role created.
SQL> create role r2 identified by oracle;
Role created.
SQL> grant create vicw to r1;
grant create vicw to r1
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> grant create view to r1;
Grant succeeded.
SQL> grant create synonym to r2;
Grant succeeded.
SQL> grant select on scott.emp to r1;
Grant succeeded.
SQL> grant select on scott.dept to r2;
Grant succeeded.
SQL> grant connect to r1,r2;
Grant succeeded.
SQL> grant r1,r2 to u1;
Grant succeeded.
SQL> conn u1/oracle
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
R1
CONNECT
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
U1 R1 NO YES NO
U1 R2 NO NO NO
SQL> set role r2 identified by oracle;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
R2
CONNECT
SQL> set role r1,r2 identified by oracle;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
R1
CONNECT
R2
簡化用戶管理，方便管理
drop role r2; 收回相應的權限
2.管理模式對象
alter table 語句增加修改刪除列
alter table XXX add(s type);
alter table XXX drop column s;
alter table XXX set unused (s); //打標簽
alter table XXX set unused column s; //打標簽
alter table XXX drop unused cilumns;
列位置修改
12c更改列的位置是很方便的
隱藏列：首先將需要增加的列增加好後，
alter table scott.tt modify(deptno invisible);
alter table scott.tt modify(deptno visible);
10g
rename ee4 to e41
create table e4 as select empno,ename,job,hirdate
修改基表，不推薦使用
alter table emp disable constraint pk_name;
alter table emp enable constraint pk_name;
alter table emp disable constraint pk_name cascade; 禁用級聯的，禁用後連個都會變為disable的，啟用的話，首先啟用主依賴，在啟用次依賴
alter table dept drop (deptno); 刪除出錯，由於存在級聯
alter table dept drop (deptno) cascade constraints; 這樣刪除就不會出錯
SQL> alter table dept drop (deptno);
alter table dept drop (deptno)
*
ERROR at line 1:
ORA-12992: cannot drop parent key column
SQL> alter table dept drop (deptno) cascade constraints;
Table altered.
11g、12c只讀
SQL> alter table emp read only;
Table altered.
SQL> update emp set sal=sal+1;
update emp set sal=sal+1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP"
SQL> alter table emp read write;
Table altered.
約束與索引的名字分開
SQL> create table a1(id number primary key using index(create unique index
2 a1_id_i on a1(id)),name varchar2(2));
Table created.
SQL> insert into a1 values(1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table a1 move;
Table altered.
SQL> select index_name,status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
A1_ID_I UNUSABLE
BIN$/o6ox8o9iMjgQ2YAqMDn3Q==$0 VALID
EMP_ENAME_I VALID
PK_EMP VALID
PK_DEPT VALID
SQL> insert into a1 values(2,'a');
insert into a1 values(2,'a')
*
ERROR at line 1:
ORA-01502: index 'SCOTT.A1_ID_I' or partition of such index is in unusable
state
SQL>alter index a1_id_i rebuild
Index altered.
flashback閃回 不適合於sys用戶的
drop table a purge;
臨時表
事務級別的 事務結束數據不在了
會話級別的 會話結束數據不再了
create global temporary table cart on commit delete rows;
外部表
文本文件存儲行
二進制文件存儲
不支持DML操作
datapump數據加載卸載
SQL> create directory ext as '/tmp';
Directory created.
SQL> grant read,write on directory ext to scott;
Grant succeeded.
建立腳本
create table fs(id number,name varchar2(10),loc varchar2(11))
organization external
(
type oracle_loader
default directory ext
access parameters(
records delimited by newline
fields terminated by ',' MISSING FIELD VALUES ARE NULL
(id,name,loc))
location('a.txt')
)
reject limit unlimited
/
SQL> select * from fs;
ID NAME LOC
---------- ---------- -----------
1 a f
2 f g
3 f
4 h
SQL> ho echo "6,u,i">>/tmp/a.txt
SQL> select * from fs;
ID NAME LOC
---------- ---------- -----------
1 a f
2 f g
3 f
4 h
6 u i
SQL> ho sed '2,4d' -i /tmp/a.txt
SQL> select * from fs;
ID NAME LOC
---------- ---------- -----------
1 a f
6 u i
**二進制文件平台之間遷移
***先導出文件，得到文本文件，然後在新的平台建立外部表執行導入操作。
數據字典
數據字典下面有基表，動態、靜態
由基表和可以訪問的視圖構成
user/all/dba
v$
user_objects 自已擁有的所有對象
all_objects 有權限訪問的所有對象
select * from dictionary
select * from dict
dba_ all_ user_ dba
all_ user_ 普通用戶
user_tables all_tables
user_tab_columns all_tab_columns
index
user_indexes user_ind_columns
all_indexs all_ind_columns
constraint
user_constraints user_cons_columns
all_ all_
view
user_views all_views
sequence
user_sequence all_sequences
synonym
user_synonyms all_synonyms
directory user_directories all_directories
注釋
comments on table |column is '......';
user_tab_comments
user_col_comments
SQL> comment on table dept is 'deptment table';
Comment created.
SQL> select * from user_tab_comments;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
SALGRADE TABLE
FS TABLE
FAS TABLE
TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
FA TABLE
EMP TABLE
DEPT TABLE
deptment table
TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
BONUS TABLE
SQL> comment on column dept.dname is 'aaaaaaa';
Comment created.
SQL> select * from user_col_comments where table_name='DEPT';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
COMMENTS
--------------------------------------------------------------------------------
DEPT DEPTNO
DEPT DNAME
aaaaaaa
DEPT LOC
SQL> comment on column dept.dname is '';
Comment created.
