Oracle12c操作命令

Oracle操作命令

1、創(chuàng)建用戶

需要先創(chuàng)建表空間
SQL> create tablespace work #創(chuàng)建work表空間
2 datafile '/orc/app/oracle/oradata/work01.dbf' #定義路徑
3 size 100M autoextend on; #大小為100M
SQL> create user c##dba #創(chuàng)建用戶為dba
2 identified by 123123 #密碼123123
3 default tablespace work #默認(rèn)數(shù)據(jù)庫(kù)work
4 temporary tablespace temp #
5 quota unlimited on work #不設(shè)置配額
6 password expire; #每次登陸后需要修改密碼

2、更改賬戶密碼

SQL> alter user c##dba identified by abc123;

3、刪除用戶

SQL> drop user c##dba cascade;

4、用戶登陸數(shù)據(jù)庫(kù)授權(quán)

grant connect,resource to c##dba;

5、撤銷用戶權(quán)限

SQL> revoke connect, resource from c##dba;

6、創(chuàng)建表

SQL> create table list
2 (
3 id number(4) constraint only_id primary key,
4 name varchar2(10),
5 score number(5,2),
6 born date,
7 address varchar2(50)
8 );

7、查看表結(jié)構(gòu)

SQL> desc list

8、事務(wù)

SQL> insert into list values (1,'zhangsan',88,to_date('2018-10-9','yyyy-mm-dd'),'beijing'); #插入zhangsan的數(shù)據(jù)
SQL> insert into list values (2,'lisi',90,to_date('2018-10-9','yyyy-mm-dd'),'shanghai'); #插入李四的數(shù)據(jù)
SQL> commit; #提交完成
SQL> insert into list values (3,'wangwu',100,to_date('2018-10-9','yyyy-mm-dd'),'tianjin'); #再次添加wangwu的數(shù)據(jù)
SQL> rollback; #回滾
SQL> select * from list; #會(huì)查看到wangwu數(shù)據(jù)并沒有被提交

9、關(guān)閉和開啟自動(dòng)提交

SQL> set autocommit on; #開啟自動(dòng)提交
SQL> insert into list values (3,'zhaoliu',55,null,null); #插入數(shù)據(jù)，會(huì)提示提交完成。
SQL> set autocommit off; #關(guān)閉自動(dòng)提交
SQL> insert into list values (4,'tianqi',66,null,null); #再次插入數(shù)據(jù)
SQL> rollback; #提示回退完成

10、創(chuàng)建索引

SQL> create index index_list on list(score); #創(chuàng)建score普通索引
SQL> create unique index id_unique on list(id); #創(chuàng)建唯一索引
SQL> create index reverse_source on list(score)reverse; #創(chuàng)建反向索引
SQL> create bitmap index add_index on list(address); #創(chuàng)建位圖索引
SQL> create index other_index on list(upper(id)); #創(chuàng)建其他索引(upper(id))
select index_name,index_type,table_name,tablespace_name from user_indexes; #查看索引
SQL> alter index other_index rebuild; #重建索引
SQL> alter index other_index rebuild tablespace work; #重建索引
SQL> alter index other_index coalesce; #合并索引碎片
SQL> drop index other_index; #刪除索引

11、創(chuàng)建和查看視圖

sqlplus / as sysdba #先進(jìn)最高管理員用戶
SQL> grant create any view to c##dba; #提權(quán)給c##dba用戶
然后再用c##dba用戶登錄
SQL> create view view_list as select from list where id=1; #創(chuàng)建視圖
SQL> select from view_list; #查看視圖

12、物化視圖

先通過sys最高管理員授權(quán)給我們創(chuàng)建的用戶c##dba;
SQL> grant create materialized view to c##dba; #賦權(quán)創(chuàng)建物化視圖
SQL> grant query rewrite to c##dba; #賦權(quán)查詢及重寫
SQL> grant create any table to c##dba; #賦權(quán)創(chuàng)建任何表
SQL> grant select any table to c##dba; #賦權(quán)查看任何表
切換回c##dba用戶
SQL> conn c##dba/123123;
先創(chuàng)建物化視圖日志
SQL> create materialized view log on list with rowid;
SQL> create materialized view mtrlview_list
2 build immediate
3 refresh fast
4 on commit
5 enable query rewrite
6 as
7 select * from list where id = '1'; #為list創(chuàng)建id為1的物化視圖；
刪除物化視圖
SQL>drop materialized view mtrlview_list