【IT Notes】Oracle Database初學的基本概念

接續上一篇研究文章，後來登入時也遇到很多問題，後來只好一一破解。還是要先靠北一下Oracle，這款資料可能是開發的早，很多操作的概念跟後來的資料庫都比起來，它不是那麼方便和自動，很多時候噴的錯誤都讓我很莫名其妙，又得要花很多時間查詢，查詢後又多是英文，許多說明並不是很懂或明確，我得要一直試才會慢慢了解。

1.Instance的概念

Oracle的資料庫較常用「instance」這個名詞，這點在一開始會因為用過其他資料庫而導致概念混淆，一個instance中可以有好幾個service（Host_name），這些service就很像是MySQL裡面的database，愈講愈複雜，反正在用之前一定要確定instance和service都寫好在Oracle的連線檔案內（路徑：u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora），不然會連線失敗，每次要是想建立新的service，必須把連線資訊先寫進去。

LISTENER_ORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

\\這代表了系統用的instance，它也是安裝完畢後的第一個

PDBORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdborcl)

    )

  )

\\這代表了另外一個後來建的service，但它仍屬於orcl中的一個服務

所以分層的概念就像下面這樣：

SQL> select Name,CREATED from v$database;

NAME   CREATED

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

ORCL   24-MAR-21

SQL> select HOST_NAME,CON_ID from v$instance;

HOST_NAME  CON_ID

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

oracledb     0

SQL> select Name, CON_ID from v$tablespace;

NAME    CON_ID

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

SYSAUX 1

SYSTEM 1

UNDOTBS1 1

USERS 1

TEMP 1

SYSTEM 2

SYSAUX 2

TEMP 2

SYSTEM 3

SYSAUX 3

TEMP 3

USERS 3

EXAMPLE 3

\\ v$instance底下的就是v$tablespace，再底下則包著table

2.tablesace的形式

如前述，MySQL和PostgreSQL都有共通點，包著table的叫做database，這個觀念好理解。先建立db，use該DB，然後再建立table，最後在insert資料進去。例如：

>create database testdb;

>use testdb;

>create table testtb (id NUMBER, name VARCHAR2(20));

>insert into testtb values ('1', 'yichikuo');

可是在Oracle裡面的觀念，包著table的叫做tablespace，而且它能被select，但不能被use，例如下面遇到的噴錯。

SQL> select * from v$tablespace; 

       TS# NAME   INC BIG FLA ENC     CON_ID

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

1 SYSAUX   YES NO  YES    1

0 SYSTEM   YES NO  YES    1

2 UNDOTBS1            YES NO  YES    1

4 USERS   YES NO  YES    1

3 TEMP   NO  NO  YES    1

0 SYSTEM   YES NO  YES    2

1 SYSAUX   YES NO  YES    2

2 TEMP   NO  NO  YES    2

0 SYSTEM   YES NO  YES    3

1 SYSAUX   YES NO  YES    3

2 TEMP    NO  NO  YES    3

3 USERS    YES NO  YES    3

4 EXAMPLE                   YES NO  YES    3

13 rows selected.

SQL> use USERS;

SP2-0042: unknown command "use USERS" - rest of line ignored.

SQL> use tablespace USERS;

SP2-0734: unknown command beginning "use tables..." - rest of line ignored.

因此如果要叫出table，只能夠下這樣的指令：

SQL> select table_name from user_tables order by table_name fetch next 10 rows only;

TABLE_NAME

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

ACCESS$

ACLMV$

ACLMV$_REFLOG

ACLMVREFSTAT$

ACLMVSUBTBL$

ADMINAUTH$

ALERT_QT

ALL_UNIFIED_AUDIT_ACTIONS

APPLY$_BATCH_SQL_STATS

APPLY$_CDR_INFO

10 rows selected.

\\後面那一大串 fetch next 10 rows only，在MySQL裡面用limit 10就搞定了，我不知道Oracle為什麼喜歡跟人家不一樣。

SQL>create table testtb (id NUMBER, name VARCHAR2(20));

SQL>insert into testtb values ('1', 'yichikuo');

SQL>select * from testtb;

ID NAME

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

1 yichikuo

3.如何連線到我要的v$instance，並看叫出table？

Oracle的分層和權限非常嚴謹，一般user休想去訪問不同的instance，而且每個user賦予的角色也很重要，就算是同一個database底下，沒有足夠權限也很難操作。我用orcl這個instance)做例子。

SQL> create user twyichikuo identified by "auqJVrh8"; \\一定要記得設定密碼不能有@，這邊我因忘記卡了一段時間，@在這邊是要當作連線用途的

SQL> grant all privileges to twyichikuo;  \\ 一定要記得給予每個user權限，沒給這個user就沒辦法去任何地方

SQL> conn twyichikuo/auqJVrh8@orcl as sysdba \\我給自己最大權限，在orcl裡面我可以訪問或操作整個資料庫系統

Connected.

SQL> show user

USER is "SYS"

SQL> select name from v$database;

NAME

---------

ORCL

SQL> conn ot@pdborcl  \\我再連到另一個instance，測試看看去另一個的服務

Enter password:

Connected.

SQL> select Name from v$tablespace;

NAME

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

UNDOTBS1

SYSTEM

SYSAUX

TEMP

USERS

EXAMPLE

6 rows selected.

4.設定commandlet上的長寬限制

Oracle Database很機車地沒有在commandlet上提供很友善使用功能，除了前一篇安裝文章中提到的上下歷史鍵功能，預設的指令介面長寬都很小，叫出結果往往都會折行，整個版面根本不能看，所以要自己先將設定寫進配置檔案內。

$vim /u01/app/oracle/product/12.2.0/dbhome_1/sqlplus/admin/glogin.sql

在空白處加上下列指令並儲存

set wrap off      \\預設的欄位都是被包捲和折行，必須關掉才好方便檢視

set linesize 200    \\設定左右寬度是200字符

set pagesize 150   \\設定上下剛度是150字符

col NAME format a50  \\設定NAME欄位50寬度的字符

回到sqlplus，測試一下就會看到結果了

SQL> select name from v$parameter fetch next 10 rows only;

NAME

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

lock_name_space

processes

sessions

timed_statistics

timed_os_statistics

resource_limit

license_max_sessions

license_sessions_warning

cpu_count

instance_groups

10 rows selected.

雖然可以學到很多設定的方式，但是Oracle Database真的很多不能自動化的不便，光這些設定就可以搞個半個月、一個月了，很煩的東西。