Oracle DBA學習日記筆記

勿勿發表於2012-07-02
Oracle筆記

資料庫管理工具sql*Plus

啟動SQL*PLUS工具

 

Cmd   ->sqlplus /nolog

Conn system/manager

 

Desc 表名     檢視錶結構

Column 列名 for 9999  格式化列 

Column 列名 for a99  格式化

 

List檢視快取區的指令；

Del 清除快取區最後一行

Run 或者/檢視執行快取區指令。

Set   line   120 設定整個命令列寬度為120個字元

SET PAGESIZE  20 設定每頁顯示20行

Save d:\select _emp.sql  儲存指令碼檔案

@ d:\select _emp.sql  執行指令碼檔案

Edit  d:\select _emp.sql  編輯指令碼檔案

get  d:\select _emp.sql  檢視指令碼檔案

 

spool d：\d.sql

spool spool off  把命令輸出指定的檔案中

 

 

sql語言概述

Structured Query Language 

分類

1.       資料查詢

2.       資料操作 DML

3.       資料定義 DDL  

CREATE ALTER DROP  RENAME TRUNCATE 

4.       事務控制 COMMIT ROLLBACK

5.       資料控制 DCL  grant   revoke

 

條件表示式

CASE 表示式用於邏輯判斷

DECODE 函式跟CASE表示式具有相同的功能

SQL>SELECT ename，job，sal, DECODE(JOB,’SALESMAN’,1.20*sal,

                                     ‘MANAGER’,1.30*sal,

                                     ‘ANALYST’,1.40*sal,

sal

)

LAST_Salary

FROM emp

ORDER BY job;

 

 

SQL字元型單行函式

LOWER() 把字元變為小寫

Upper()大寫轉換

INITCAP  把首個字母大寫

CONCAT 連線字串

INSTR(‘MY NAME IAS NS’,’MY’) 搜尋字元的位置

LPAD() RPAD() 查詢結構後用字元補充

Substr(‘my nasdf is sdfsdf’,12) 擷取字串

Length（）字串的長度

Replace() 替換函式

TRIM()

 

Round() 四捨五入的規則

Mod() 求餘數

 

日期函式

ALTER SESSION SET NLS_date_lanauage=’amercan’;

Sysdate函式

MONTHS_BETWEEN()

ADD_MONTHS

NEXT_DAY(date,string)

LAST_DAY(date)

 

分組函式

AVG，SUM，MAX， MIN ，count ,distinct 

 

NVL(COL,0)

NVL2(EXPR1,EXPR2,EXP3)

 

 

 

第五章建立資料庫

記憶體：記憶體是否滿足Oracle例項SGA的要求

磁碟分配對資料檔案控制檔案和重做日誌檔案的大小評估

資料庫檔案分佈  對於存在競爭的資料檔案放在不同的磁碟上，以免I、O競爭，如重做日誌檔案和歸檔日誌檔案就不應該放在同一個磁碟對於控制檔案要進行多路複用Oracle要求將多個控制檔案放在不通的磁碟裝置上

建立資料庫 DBCA

Sqlplus /nolog

Conn  /as sysdba

startup

Create database 指令

在安裝資料庫軟體的時候建立

 

第六章管理和維護表

 

Varchar2(size) 儲存變長的字元資料，大小不固定的

Nvarchar2() 不同之處在於支援全球化資料型別支援定長和變長的字符集

 

Char(size) 定長字元型別

 

Rowid（）前6位 AAAQ +h表示資料物件號

接著3位 AAE表示相對檔案號

接著6位AAAAAO為塊號

最後3位為行號

SQL>select owner ,table_name,tablespace_name

From dba_tables

Where owner=’SCOTT’

 

建立臨時表

Create  global temporary table

Emp_temporary

On commit preserve rows

As

Select * from emp

Where job=’MANAGER’;

 

臨時表別的使用者是無法訪問的。

 

維護列

SQL>ALTER TABLE SCOTT.EMPLOYEES 

ADD (

DEGREE VARCHAR2(10)

);

修改列

SQL>ALTER TABLE   scott.employees

Modify(

Degree varchar2(10) not null

);

刪除列

SQL>ALTER TABLE scott.employees drop column degree;

 刪除表

 SQL> TRUNCATE TABLE TEST;

 

表被截斷。

SQL> drop table test

  2  ;

表已刪除。

 

分割槽表操作

對於一個很大的表而言，每次搜尋時對全表掃描很耗時間，Oracle允許對一個表進行分割槽。把達標分解為更容易管理的區分塊。按照不通的規則將表分佈在不同的磁碟上。

1．  範圍分割槽 range partitioning

SQL> conn system/manager

已連線。

SQL> create table Sales_range(

  2  Salesman_id number(5),

  3  Salesman_name varchar2(30),

  4  Sales_amount number(10),

  5  Sales_date date)

  6  partition by Range(Sales_date)(

  7  partition sales_jan2008 values less than(to_date('02/01/2008','MM/DD/YY')),

 

  8  partition sales_feb2008 values less than(to_date('03/01/2008','MM/DD/YY')),

 

  9  partition sales_mar2008 values less than(to_date('04/01/2008','MM/DD/YYYY')

),

 10  partition sales_apr2008 values less than(to_date('05/01/2008','MM/DD/YYYY')

))

 11  ;

 

表已建立。

按照Sales_date欄位分割槽。 Values less than指定什麼樣的資料放在什麼樣的分割槽中。

2.列表分割槽

顯示的把資料行對映到各個分割槽，這些分割槽的定義中指定了一個由分割槽鍵離散值的列表。

SQL> create table sales_list(

  2  Salesman_id number(5),

  3  Salesman_name varchar2(30),

  4  Sales_state varchar2(20),

  5  Sales_amount number(10),

  6  Sales_date date)

  7  partition by list(sales_state)(

  8  partition sales_west values('California','Hawaii'),

  9  partition sales_east values('New Yor','Virginia','Florida'),

 10  partition sales_centralt values('Texas','Tllinois'),

 11  partition sales_other values(default)

 12  )

 13  ;

 

表已建立。

該分割槽表有4個分割槽塊，鍵值sales_state用來把不通的資料行對映到相應的分割槽表中。

 

第七章Oracle 資料字典

靜態動態資料字典表

查詢資料庫的名字和建立時間

SQL> select name ,created from v$database;

 

NAME      CREATED

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

ORCL      20-6月 -12

查詢主機名和例項名

SQL> col host_name for a20;

SQL> run;

  1* select host_name,instance_name,version from v$instance

 

HOST_NAME            INSTANCE_NAME    VERSION

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

CSSTSTEP-ZHOU        orcl             11.1.0.6.0

檢視控制檔案的配置

SQL> run;

  1* select status,name from v$controlfile

 

STATUS     NAME

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

           D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL

           D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL

               D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL

查詢重做日誌配置

SQL> select group#,members,status,archived  from v$log;

 

    GROUP#    MEMBERS STATUS     ARC

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

         1          1 INACTIVE   NO

         2          1 CURRENT    NO

             3          1 INACTIVE   NO

查詢資料檔案

SQL> run;

  1* select file_id,file_name,tablespace_name from dba_data_files

 

   FILE_ID FILE_NAME                                          TABLESPACE

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

         4 D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF      USERS

         3 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF    UNDOTBS1

         2 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF     SYSAUX

         1 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF     SYSTEM

         5 D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF    EXAMPLE

查詢表空間資訊

SQL> select tablespace_name,block_size,contents from dba_tablespaces;

 

TABLESPACE BLOCK_SIZE CONTENTS

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

SYSTEM           8192 PERMANENT

SYSAUX           8192 PERMANENT

UNDOTBS1         8192 UNDO

TEMP             8192 TEMPORARY

USERS            8192 PERMANENT

EXAMPLE          8192 PERMANENT

 

已選擇6行。

 

第八章 Oracle檢視

SQL> conn /as sysdba;

已連線。

SQL> grant create view to scott;

SQL> run;

  1  create view accounting_view as

  2  select e.ename "employee_name",e.job "job",e.hiredate "hiredate",

  3  e.sal "salary" ,d.dname "dep_name"

  4  from dept d,emp e

  5  where e.deptno=d.deptno

  6  and

  7* d.deptno<20

 

檢視已建立。

查詢檢視資訊

SQL> select view_name from user_views;

 

VIEW_NAME

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

ACCOUNTING_VIEW

 

SQL> select text from user_views where view_name='ACCOUNTING_VIEW';

 

TEXT

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

 

select e.ename "employee_name",e.job "job",e.hiredate "hiredate",

e.sal "salary"

WITH READ ONLY 子句

 設定只讀檢視不能修改和增加檢視資料

WITH CHECK OPTION

插入資料的檢視約束

 

 

事務

事務是一個邏輯工作單元。ACID特性

COMMIT顯示事務控制

ROLLBACK實現事務控制

異常退出對事務的影響

AUTOCOMMIT實現自動提交

SQL> set autocommit on;

SQL> set autocommit off;

 

 

 

 

 

 

第十章資料查詢

SQL> run;

  1  select ename ||'的僱傭日期是：' || hiredate

  2* from emp

 

ENAME||'的僱傭日期是：'||HIREDATE

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

SMITH的僱傭日期是：17-12月-80

ALLEN的僱傭日期是：20-2月 -81

WARD的僱傭日期是：22-2月 -81

JONES的僱傭日期是：02-4月 -81

MARTIN的僱傭日期是：28-9月 -81

BLAKE的僱傭日期是：01-5月 -81

CLARK的僱傭日期是：09-6月 -81

SCOTT的僱傭日期是：19-4月 -87

KING的僱傭日期是：17-11月-81

TURNER的僱傭日期是：08-9月 -81

ADAMS的僱傭日期是：23-5月 -87

JAMES的僱傭日期是：03-12月-81

FORD的僱傭日期是：03-12月-81

MILLER的僱傭日期是：23-1月 -82

 

 

查詢加別名

SQL> run;

  1  select ename ||'的僱傭日期是：' || hiredate   "員工僱傭日期查詢"

  2* from emp

 

員工僱傭日期查詢

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

SMITH的僱傭日期是：17-12月-80

ALLEN的僱傭日期是：20-2月 -81

WARD的僱傭日期是：22-2月 -81

JONES的僱傭日期是：02-4月 -81

MARTIN的僱傭日期是：28-9月 -81

BLAKE的僱傭日期是：01-5月 -81

CLARK的僱傭日期是：09-6月 -81

SCOTT的僱傭日期是：19-4月 -87

KING的僱傭日期是：17-11月-81

TURNER的僱傭日期是：08-9月 -81

ADAMS的僱傭日期是：23-5月 -87

JAMES的僱傭日期是：03-12月-81

FORD的僱傭日期是：03-12月-81

MILLER的僱傭日期是：23-1月 -82

 

已選擇14行。

DISTINCT 該列的輸入是唯一的。

 

 

連線查詢 

 

乘機連線

 相等連線

 自連線

 不等連線

外連線

 

 

子查詢

Where 條件語句

HAVING子句單行子查詢

用來限制分組函式

SQL> run;

  1  select job,min(sal),avg(sal),max(sal)

  2  from emp

  3  group by job

  4* having avg(sal)>2000

 

JOB         MIN(SAL)   AVG(SAL)   MAX(SAL)

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

PRESIDENT       5000       5000       5000

MANAGER         2450 2758.33333       2975

ANALYST         3000       3000       3000

 

 

ALL子查詢

SQL> run;

  1  select ename,job,sal

  2  from emp

  3  where sal >=all(

  4  select avg(sal)

  5  from emp

  6  group by job

  7  )

  8*

 

ENAME      JOB              SAL

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

KING       PRESIDENT       5000

 

Any子查詢

SQL> select ename ,job,sal

  2  from emp

  3  where sal >any(

  4  select avg(sal)

  5  from emp

  6  group by job

  7  );

 

ENAME      JOB              SAL

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

KING       PRESIDENT       5000

FORD       ANALYST         3000

SCOTT      ANALYST         3000

JONES      MANAGER         2975

BLAKE      MANAGER         2850

CLARK      MANAGER         2450

ALLEN      SALESMAN        1600

TURNER     SALESMAN        1500

MILLER     CLERK           1300

WARD       SALESMAN        1250

MARTIN     SALESMAN        1250

ADAMS      CLERK           1100

 

已選擇12行。

 

 

第十一章索引

建立索引不用全表掃描，可以適當的減少磁碟I/O

對於具有隻讀特性或者較少的插入，更新或者刪除操作的大表通常可以提高查詢速度

可以對錶的一列或者多列建立索引

建立索引的數量沒有限制

索引需要磁碟儲存。由Oracel自動維護

索引對使用者透明

 

建立索引的語法：

 

CREATE [UNIQUE|BITMAP] INDEX [SCHEMA.] INDEX_NAME –UNIQUE 說明該索引是唯一的索引  BITMAP：建立點陣圖索引

ON [SCHEMA.] TABLE_NAME 

(COLUMN_NAME [DESC]ASC[,COLUMN_NAME[DESC]ASC])….)  --說明建立的索引為降序或者升序排序

[REVERSE] –REVERSE 說明建立反向鍵索引

[TABLESPACE TABLESPACE_NAME] 說明要建立的索引所儲存的表空間

[PCTFREE N]—索引塊中預先保留的空間比例

[INITRANS N]—每一個索引塊分配的事務數

[MAXTRANS N]---每一個索引塊分配的最多事務數

[INSTORAGE STATE]—說明索引中區段EXTENT如何分配

[LOGGING|NOLOGGING] –說明要記錄|不記錄索引相關的操作，並儲存聯機重做日誌中

[NOSORT]  不需要在建立索引時再按鍵值進行排序

 

建立索引 INDEX  exp：

SQL> create  index  emp_enam_idx

  2  on emp(ename)

  3  ;

 

索引已建立。

查詢索引

SQL> select index_name,index_type,tablespace_name from user_indexes

 

SQL> ;

 

INDEX_NAME           INDEX_TYPE TABLESPACE_NAME

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

PK_EMP               NORMAL     USERS

EMP_ENAM_IDX         NORMAL     USERS

PK_DEPT              NORMAL     USERS

 

使用者必須建立單獨的表空間來儲存索引

SQL> create tablespace index_tbs

  2  datafile 'd:/index_tbs'

  3  size 100m

  4  autoextend on ;

 

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

INDEX_TBS

 

已選擇7行。

 

建立符合索引（多列索引）

SQL> run;

  1  create index emp_ename_sal_idx

  2  on scott.emp(ename,sal)

  3  tablespace index_tbs

  4*

 

索引已建立。

 

SQL> select index_name ,table_name,tablespace_name from user_indexes

  2  where index_name like 'EM%'

  3  ;

 

INDEX_NAME           TABLE_NAME                     TABLESPACE_NAME

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

EMP_ENAME_SAL_IDX    EMP                            INDEX_TBS

查詢列上的索引資訊 user_ind_columns

SQL> run;

  1  select index_name,table_name,column_name

  2  from user_ind_columns

  3* where index_name like 'EMP%'

 

INDEX_NAME           TABLE_NAME COLUMN_NAME

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

EMP_ENAME_SAL_IDX    EMP        ENAME

EMP_ENAME_SAL_IDX    EMP        SAL

 

B樹索引

 

建立點陣圖索引

SQL> create bitmap index emp_job_bitmap_idx

  2  on emp(job);

SQL> select index_name,index_type,table_name,status from user_indexes;

 

INDEX_NAME           INDEX_TYPE                  TABLE_NAME STATUS

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

PK_EMP               NORMAL                      EMP        VALID

EMP_ENAM_IDX         NORMAL                      EMP        VALID

EMP_JOB_BITMAP_IDX   BITMAP                      EMP        VALID

PK_DEPT              NORMAL                      DEPT       VALID

反向鍵索引

SQL> create index emp_sal_reverse_index

  2  on emp(sal)

  3  reverse;

 

索引已建立。

SQL> select index_name,index_type,table_name from user_indexes;

 

INDEX_NAME           INDEX_TYPE                  TABLE_NAME

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

PK_EMP               NORMAL                      EMP

EMP_ENAM_IDX         NORMAL                      EMP

EMP_JOB_BITMAP_IDX   BITMAP                      EMP

EMP_SAL_REVERSE_INDE NORMAL/REV                  EMP

X

 

PK_DEPT              NORMAL                      DEPT

 

 

建立UPPER函式索引

SQL> create index dept_dname_idx

  2  on dept(upper(dname))

  3  ;

 

索引已建立。

 

 

對索引的監控

SQL> alter index EMP_JOB_BITMAP_IDX

  2  monitoring usage;  --啟動監控

 

SQL> select job from emp; --查詢使用到了索引

 

JOB

---------

ANALYST

ANALYST

CLERK

CLERK

CLERK

CLERK

 

SQL> alter index EMP_JOB_BITMAP_IDX  --關閉監控

  2  nomonitoring usage;

 

索引已更改。

 

 

SQL> select index_name ,table_name,monitoring,used from v$object_usage;

 

INDEX_NAME           TABLE_NAME MON USE

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

EMP_ENAM_IDX         EMP        YES NO

EMP_JOB_BITMAP_IDX   EMP         NO  YES  --說明索引有使用到

 

 

重建索引

SQL> alter index emp_enam_idx

  2  rebuild

  3  ;

 

索引已更改。

 

重建並遷移其表空間

SQL> alter index emp_enam_idx

  2  rebuild

  3  tablespace index_tbs1;

 

 

索引的維護

通過rebuild修改索引引數

SQL>alter index emp_job_bitmap_idx

Rebuild

Pctfree 30

Storage (next 100k) ;

 

 

 

 

 

手工增加磁碟空間

SQL>alter index emp_job_bitmap_idx

    Allocate extent;

 

合併索引碎片

SQL>alter index emp_job_bitmap_idx coalesce;

 

刪除索引

SQL> drop index emp_enam_idx;

 

索引已刪除。

 

相關文章