sql語句oracle

『壹』 oracle sql語句 分為哪些

Oracle SQL語句分類
SQL(Structured Query Language)即結構化查詢語句，應用程序與資料庫交互的介面
集數據操作、數據定義、數據控制等功能於一體
ANSI先後制定推出了SQL-89、SQL-92、SQL-99標准

oracle SQL 語句主要分為一下四類：

DML(Data Mannipulation Language)數據操縱語言：查詢、操縱數據表資料行
SELECT : 檢索資料庫表或視圖數據
INSERT : 將數據行新增至資料庫表或視圖中
UPDATE : 修改表或視圖中現有的數據行
DELETE : 刪除表或視圖中現有的數據行
注意：DML語句不會自動提交事務！

DDL(Data Definition Language)數據定義語言：建立、修改、刪除資料庫中數據表對象
CREATE TABLE : 創建表
ALTER TABLE : 修改表
DROP TABLE : 刪除表
注意：DLL語句會自動提交事務！所以：DML語句事務提交之前可以回滾，DDL語句不能回滾事務

DCL(Data Control Language)數據控制語言：用於執行許可權授予與收回操作
GRANT : 給用戶或角色授予許可權
REVOKE : 收回用戶或角色的所有許可權

TCL(Transactional Control Language)事物控制語言：維護數據的一致性
COMMIT :提交已經進行的資料庫改變
ROLLBACK : 回滾已經進行的數據改變
SAVEPOINT : 設置保存點，用於部分數據改變的取消

『貳』 資料庫手冊：Oracle維護常用SQL語句二

回滾段查看 select rownum sys dba_rollback_segs segment_name Name v$rollstat extentsExtents v$rollstat rssize Size_in_Bytes v$rollstat xacts XActs v$rollstat gets Gets v$rollstat waits Waits v$rollstat writes Writes sys dba_rollback_segs status status from v$rollstat sys dba_rollback_segs v$rollname where v$rollname name(+) = sys dba_rollback_segs segment_name andv$rollstat usn (+) = v$rollname usn order by rownum 耗資源的進程(top session) select s schemaname schema_name decode(sign( mand) to_char(mand) Action Code # || to_char(mand) ) action statussession_status s osuser os_user_name s sid p spid s serial# serial_num nvl(s username [Oracle process] ) user_name s terminal terminal s program program st value criteria_value from v$sesstat st v$session s v$process pwhere st sid = s sid and st statistic# = to_number( ) and ( ALL = ALL or s status = ALL ) and p addr = s paddr order by st value desc p spid asc s username asc s osuser asc 查看鎖(lock)情況 select /*+ RULE */ ls osuser os_user_name ls username user_name decode(ls type RW Row wait enqueue lock TM DML enqueue lock TX Transaction enqueue lock UL User supplied lock ) lock_type o object_name object decode(ls lmode null Row Share Row Exclusive Share Share Row Exclusive Exclusive null)lock_mode o owner ls sid ls serial# serial_num ls id ls id from sys dba_objects o ( select s osuser s username l type l lmode s sid s serial# l id l id from v$session s v$lock l where s sid = l sid ) ls where o object_id = ls id and o owner<> SYS order by o owner o object_name 查看等待(wait)情況 SELECT v$waitstat class v$unt count SUM(v$sysstat value) sum_valueFROM v$waitstat v$sysstat WHERE v$sysstat name IN ( db block gets consistent gets ) group by v$waitstat class v$unt 查看sga情況 SELECT NAME BYTES FROM SYS V_$SGASTAT ORDER BY NAME ASC 查看catched object SELECT owner name db_link namespace type sharable_mem loads executions locks pins kept FROM v$db_object_cache 查看V$SQLAREA SELECT SQL_TEXT SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS ROWS_PROCESSED FROM V$SQLAREA 查看object分類數量 select decode (o type# INDEX TABLE CLUSTER VIEW SYNONYM SEQUENCE OTHER ) object_type count(*) quantity fromsys obj$ o where o type# > group by decode (o type# INDEX TABLE CLUSTER VIEW SYNONYM SEQUENCE OTHER ) union select COLUMN count(*) from l$ union select DB LINK count(*) from 按用戶查看object種類 select u name schema sum(decode(o type# NULL)) indexes sum(decode(o type# NULL)) tables sum(decode(o type# NULL))clusters sum(decode(o type# NULL)) views sum(decode(o type# NULL)) synonyms sum(decode(o type# NULL)) sequences sum(decode(o type# NULL NULL NULL NULL NULL NULL ))others from sys obj$ o sys user$ u where o type# >= and u user# =o owner# and u name <> PUBLIC group by u name order bysys link$ union select CONSTRAINT count(*) from n$ 有關connection的相關信息 )查看有哪些用戶連接select s osuser os_user_name decode(sign( mand) to_char(mand) Action Code # || to_char(mand) ) action p program oracle_process status session_status s terminal terminal s program program s username user_name s fixed_table_sequence activity_meter query memory max_memory cpu_usage s sid s serial# serial_numfrom v$session s v$process p where s paddr=p addr and s type = USER order by s username s osuser )根據v sid查看對應連接的資源佔用等情況select n name v value n class n statistic#from v$statname n v$sesstat vwhere v sid = andv statistic# = n statistic#order by n class n statistic# )根據sid查看對應連接正在運行的sqlselect /*+ PUSH_SUBQ */mand_type sql_text sharable_mem persistent_mem runtime_mem sorts version_count loaded_versions open_versions users_opening executions users_executing loads first_load_time invalidations parse_calls disk_reads buffer_gets rows_processed sysdate start_time sysdate finish_time > || address sql_address N statusfrom v$sqlareawhere address = (select sql_address from v$session where sid = ) 查詢表空間使用情況select a tablespace_name 表空間名稱 round((nvl(b bytes_free )/a bytes_alloc)* ) 佔用率(%) round(a bytes_alloc/ / ) 容量(M) round(nvl(b bytes_free )/ / ) 空閑(M) round((a bytes_alloc nvl(b bytes_free ))/ / ) 使用(M) Largest 最大擴展段(M) to_char(sysdate yyyy mm dd hh :mi:ss ) 采樣時間 from (select f tablespace_name sum(f bytes) bytes_alloc sum(decode(f autoextensible YES f maxbytes NO f bytes)) maxbytesfrom dba_data_files fgroup by tablespace_name) a (select f tablespace_name sum(f bytes) bytes_freefrom dba_free_space fgroup by tablespace_name) b (select round(max(ff length)* / ) Largest ts name tablespace_namefrom sys fet$ ff sys file$ tf sys ts$ tswhere ts ts#=ff ts# and ff file#=tf relfile# and ts ts#=tf ts#group by ts name tf blocks) cwhere a tablespace_name = b tablespace_name and a tablespace_name = c tablespace_name 查詢表空間的碎片程度 select tablespace_name count(tablespace_name) from dba_free_space group by tablespace_namehaving count(tablespace_name)> ;alter tablespace name coalesce;alter table name deallocate unused;create or replace view ts_blocks_v asselect tablespace_name block_id bytes blocks free space segment_name from dba_free_spaceunion allselect tablespace_name block_id bytes blocks s lishixin/Article/program/Oracle/201311/18531

『叄』 Oracle sql語法

參考網上資料，整理Oracle sql語法：

DDL:

1、創建表

     create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

     根據已有的表創建新表：

 create table tab_new as select col1,col2… from tab_old

2、刪除表

     drop table tabname

3、重命名表

     說明：alter table 表名 rename to 新表名

        eg：alter table tablename rename to newtablename

4、增加欄位

     說明：alter table 表名 add (欄位名 欄位類型 默認值 是否為空);

        例：alter table tablename add (ID int);

       eg：alter table tablename add (ID varchar2(30) default '空' not null);

5、修改欄位

     說明：alter table 表名 modify (欄位名 欄位類型 默認值 是否為空);

        eg：alter table tablename modify (ID number(4));

6、重名欄位

     說明：alter table 表名 rename column 列名 to 新列名 （其中：column是關鍵字）

        eg：alter table tablename rename column ID to newID;

7、刪除欄位

     說明：alter table 表名 drop column 欄位名;

        eg：alter table tablename drop column ID;

8、添加主鍵

     alter table tabname add primary key(col)

9、刪除主鍵

     alter table tabname drop primary key(col)

10、創建索引

     create [unique] index idxname on tabname(col….)

11、刪除索引

     drop index idxname

     註：索引是不可更改的，想更改必須刪除重新建。

12、創建視圖

     create view viewname as select 語句

13、刪除視圖

     drop view viewname

14.    創建表空間

create tablespace schooltbs datafile 『D:\oracle\datasource\schooltbs.dbf』 size 1000M autoextend on;

15.    刪除表空間

drop tablespace schooltbs[including contents and datafiles];

註：查詢表空間和表空間存儲路徑

SELECT * FROM dba_data_files WHERE tablespace_name = 表空間名;

DML:

1、數據查詢

     select <列名> from <表名> [where <查詢條件表達試>] [order by <排序的列名>[asc或desc]]

2、插入數據

     insert into 表名 values(所有列的值);

     insert into test values(1,'zhangsan',20);

     insert into 表名(列) values(對應的值);

     insert into test(id,name) values(2,'lisi');

3、更新數據

     update 表 set 列=新的值 [where 條件] -->更新滿足條件的記錄

     update test set name='zhangsan2' where name='zhangsan'

     update 表 set 列=新的值 -->更新所有的數據

     update test set age =20;

4、刪除數據

delete from 表名 where 條件 -->刪除滿足條件的記錄

     delete from test where id = 1;

     delete from test -->刪除所有

     commit; -->提交數據

     rollback; -->回滾數據

     delete方式可以恢復刪除的數據，但是提交了，就沒辦法了 delete刪除的時候，會記錄日誌 -->刪除會很慢很慢

truncate table 表名

     刪除所有數據，不會影響表結構，不會記錄日誌，數據不能恢復 -->刪除很快

drop table 表名

     刪除所有數據，包括表結構一並刪除，不會記錄日誌，數據不能恢復-->刪除很快

5、數據復制

表數據復制

     insert into table1 (select * from table2);

復製表結構

     create table table1 select * from table2 where 1>1;

復製表結構和數據

     create table table1 select * from table2;

復制指定欄位

     create table table1 as select id, name from table2 where 1>1;