使用ChatGPT來學習oracle 特定主題

DL 我是一個資料庫初學者，請用容易理解的方式，告訴我五個優點，三個缺點，關於使用oracle partitioned table ChatGPT 當然！Oracle 分區表有許多優點和一些挑戰。我來分別談談它們。 優點： 性能提升： 分區可以使資料庫的查詢和操作更有效率。當你需要查詢或操作特定區域的資料時，分區可以幫助系統僅針對必要的部分進行處理，從而提升效能。 簡化管理： 分區表可以幫助你更輕鬆地管理大量資料，因為你可以針對不同的需求獨立地管理每個分區。這使得資料的刪除、移動、備份和恢復等操作更容易進行。 更好的維護： 透過分區，你可以更容易地執行維護任務，比如只針對某些分區進行索引重建或統計更新，而不需要對整個表進行操作，這可以節省時間和系統資源。 增強的故障恢復能力： 使用分區表可以使故障恢復更有效率。當發生故障時，你可以更快地恢復單個分區，而不需要恢復整個表，這可以減少系統停機時間。 提供更好的安全性： 你可以將不同的分區設置不同的安全性要求，以控制不同用戶對資料的訪問權限。這可以提高資料的安全性。 缺點： 複雜性增加： 管理分區表可能比單一表更加複雜，特別是在設計和維護方面。需要更多的計劃和注意事項，以確保分區的正確使用。 可能增加成本： 分區表可能需要更多的硬體資源和更高的設置成本，特別是當你需要額外的儲存空間來支持分區表時。 不當使用可能導致效能下降： 如果分區策略不當，可能會導致效能下降。例如，過多的分區可能會增加系統的管理負擔，而過少的分區則可能無法有效地提升效能。 希望這幫得上忙！如果有其他問題，歡迎隨時問我。 DL 請對你回答中的兩個項目，舉例說明，能夠列出指令更佳: 1. 更好的維護：刪除、移動。 2. 複雜性增加： 管理分區表可能比單一表更加複雜，特別是在設計和維護方面。需要更多的計劃和注意事項，以確保分區的正確使用。 ChatGPT 當然，讓我來進一步說明。 更好的維護： 刪除： 假設你有一個分區表，其中包含了過去五年的銷售資料，每個分區代表一年的資料。現在你需要刪除最舊的兩年資料以釋放空間。使用分區表，你可以輕鬆地執行這個任務，只需刪除對應的分區即可，而不需要影響其他年份的資料。 sql Copy code ALTER TABLE sales DROP PARTITION sales_2017; ALTER TABLE sales DROP PARTITION sales_2018; 移動： 假設你想要將分區表中的某些資料移動到不同的存儲位置，比如將 2019 年的銷售資料移動到更快的 SSD 硬碟上。你可以輕鬆地使用分區表來完成這個任務。 sql Copy code ALTER TABLE sales MOVE PARTITION sales_2019 TABLESPACE ssd_tablespace; 複雜性增加： 管理分區表需要更多的計劃和注意事項，這是因為你需要考慮分區策略、分區鍵、分區的大小等因素，以確保分區的正確使用。例如，如果你的分區策略過於細緻，可能會導致過多的分區，增加管理複雜性。相反，如果分區策略不夠細緻，可能無法充分發揮分區的效能優勢。 sql Copy code -- 定義分區表 CREATE TABLE sales ( sales_id NUMBER, sales_date DATE, amount NUMBER ) PARTITION BY RANGE (sales_date) ( PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')), PARTITION sales_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')), PARTITION sales_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')) ); 在上面的例子中，定義了一個按照銷售日期分區的分區表，分區鍵為銷售日期。管理這樣的分區表需要確保分區的定義與實際業務需求相符，並且需要不斷評估和調整分區策略以滿足系統的需求。 DL 謝謝，兩個問題: 1.移動例子中的指令，可以使用online的方式，讓移動的過程中不影響該partition的使用嗎? 2.使用partitioned table，在index的維護上，請列出五點特別需要注意的項目。 ChatGPT 當然，我來回答你的問題： 移動分區時使用Online方式： 在 Oracle 中，你可以使用 ALTER TABLE ... MOVE PARTITION 命令來移動分區，但默認情況下，這會導致該分區在移動期間不可用，可能會對系統造成影響。不過，你可以通過在命令中添加 UPDATE INDEXES 子句以使用 Online 方式，從而在移動過程中不影響該分區的使用。 sql Copy code ALTER TABLE sales MOVE PARTITION sales_2019 TABLESPACE ssd_tablespace UPDATE INDEXES ONLINE; 這樣，當移動分區時，相關的索引也會在線上被更新，從而使得該分區在移動過程中仍然可以被查詢和操作。 使用 Partitioned Table，在 Index 的維護上需要注意的項目： 在管理 Partitioned Table 的索引時，有幾個特別需要注意的事項： 分區索引的管理： 確保分區索引的適當管理，包括定期的統計信息更新、重建索引等。可以使用 ALTER INDEX ... REBUILD PARTITION 命令來對特定分區的索引進行重建。 索引分區與資料分區的一致性： 當修改了資料分區的結構或內容時，確保相應的索引分區也被更新以保持一致性。否則，可能會導致索引不再有效或不一致。 注意索引與分區策略的配合： 確保索引的建立與分區策略相符，以充分發揮索引的效能優勢。比如，使用本地索引（local index）來與分區表配合，以避免全局索引（global index）的性能問題。 考慮索引重建的影響： 當修改了分區表的結構或內容時，特別是當新增或移除分區時，需要考慮索引重建的影響，確保索引的維護不會對系統性能造成過大的影響。 定期監控與調整： 定期監控索引的使用情況和性能，根據實際情況進行調整索引的設計和維護策略，以確保系統的正常運作和性能優化。 這些都是在管理 Partitioned Table 索引時需要特別注意的事項，確保系統的穩定性和效能。 DL 請對注意索引與分區策略的配合，舉實例說明，能有指令的範例更佳。 ChatGPT 當然，讓我舉個例子來說明注意索引與分區策略配合的重要性。 假設你有一個分區表 sales，其中存儲了每天的銷售資料，分區方式是按照銷售日期進行分區。現在你想要為這個表建立索引以加快查詢速度，而你選擇了在銷售日期上建立索引。 sql Copy code CREATE INDEX sales_date_idx ON sales(sales_date); 這個索引的建立方式是全局索引（global index），它將所有分區的銷售資料都納入同一個索引結構中。 然而，如果你的分區表非常大，而且銷售資料是不斷增加的，使用全局索引可能會導致性能問題。因為當你查詢某個特定日期的銷售資料時，系統需要掃描整個索引結構，而不僅僅是單個分區，這會增加查詢的時間。 為了改善這個情況，你可以考慮使用本地索引（local index）。本地索引與分區表的分區策略配合得更好，每個分區都有自己的索引，只包含該分區的銷售資料。 sql Copy code CREATE INDEX sales_date_idx ON sales(sales_date) LOCAL; 這樣一來，在查詢某個特定日期範圍內的銷售資料時，系統只需要掃描該日期範圍所對應的分區，而不需要查詢整個索引結構，從而提高了查詢效率。 這個例子表明了使用本地索引與分區表的分區策略配合得更好，能夠更有效地提升查詢性能。因此，在設計索引時，特別是針對分區表，需要考慮索引與分區策略的配合，以達到最佳的效果。 DL 更進一步地問，既然分區表搭配全局索引會產生性能問題，那麼時候才需要為分區表建立全局索引呢? ChatGPT 建立全局索引通常在以下情況下是合適的： 跨分區的查詢需求： 如果你的查詢經常涉及到跨越多個分區的資料，而且這些查詢效能要求不高，那麼使用全局索引可能是合適的。全局索引可以提供對整個表的全局查詢支持。 特定的資料分佈： 如果你的資料在分區間的分佈比較均勻，而且沒有明顯的熱點（hotspot）分區，那麼使用全局索引可能會比較合適。這樣可以避免由於本地索引的分區不均勻而導致的性能問題。 不常查詢的分區： 如果某些分區的資料很少被查詢，而且這些分區的查詢性能要求不高，那麼使用全局索引可能是一個更簡單的選擇。這樣可以避免為這些不常查詢的分區建立本地索引所帶來的管理成本。 總的來說，全局索引在需要對整個分區表進行全局查詢或者資料分佈比較均勻的情況下是合適的。然而，在大多數情況下，特別是當分區表的資料量很大時，使用本地索引通常會更有效率，因為它們能夠更好地利用分區表的分區策略，提升查詢性能。 DL 我有另外一個資料庫B，在使用相同的作業系統，相同版本的Oracle資料庫軟體時，我希望將資料庫A中的分區表的較舊的分區，遷移到資料庫B，請問有哪些方式? 最簡單的方式為何? 能夠列出指令更佳。 ChatGPT 將分區表的較舊分區從一個資料庫遷移到另一個資料庫是一個常見的需求。以下是一些常見的方法： 使用資料庫鏡像或複製技術： 如果你使用的是 Oracle Enterprise Edition，你可以考慮使用 Oracle Data Guard 或 GoldenGate 等技術來實現資料庫的鏡像或複製。這樣可以將整個資料庫的資料與結構複製到另一個資料庫，包括分區表及其資料。這是最簡單的方式之一，因為它提供了一個完整的資料庫複製，而不需要單獨處理分區表。 使用資料庫連接和資料匯出/匯入： 可以使用 Oracle 的資料庫連接功能，將分區表中的資料匯出到文件中，然後在目標資料庫中使用資料匯入功能將資料加載進去。這種方法比較適合於小型資料量的分區表。 下面是使用 expdp 和 impdp 工具將分區表的資料匯出和匯入的示例指令： 在資料庫 A 上匯出分區表的資料： sql Copy code expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=sales_partition.dmp TABLES=sales:SALES_PARTITION CONTENT=DATA_ONLY QUERY="WHERE sales_date < TO_DATE('2023-01-01', 'YYYY-MM-DD')" 在資料庫 B 上匯入資料： sql Copy code impdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=sales_partition.dmp REMAP_SCHEMA=A:B TABLE_EXISTS_ACTION=APPEND 在上述指令中，sales 是分區表的名稱，sales_date 是分區鍵，TO_DATE('2023-01-01', 'YYYY-MM-DD') 是要匯出的分區的界限日期。 REMAP_SCHEMA 選項用於將資料匯入到資料庫 B 中的不同架構中（如果需要的話）。 TABLE_EXISTS_ACTION=APPEND 告訴匯入工具將資料附加到現有的表中。 這些方法中，鏡像或複製技術是最簡單的，因為它提供了完整的資料庫複製，而匯出/匯入方法則比較適合於特定的資料移動需求。

對物件的影響程度 Repair Chained Rows (蟾蜍) < Alter Table Move < Rebuild Table (蟾蜍)

 

對物件的影響程度

Rebuild Table (蟾蜍) > Alter Table Move (reorg) > Repair Chained Rows (蟾蜍)

1. Rebuild Table (蟾蜍) : Create Indexes, Compile Dependencies, Analyze Table

2. Alter Table Move (reorg): Rebuild Indexes, Analyze Table

3. Repair Chained Rows (蟾蜍): Analyze Table

SQL> create table tbl_1224 as select * from employees;

Table created.

SQL> desc tbl_1224;

 Name                                      Null?    Type

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

 EMPLOYEE_ID                                        NUMBER(6)

 FIRST_NAME                                         VARCHAR2(20)

 LAST_NAME                                 NOT NULL VARCHAR2(25)

 EMAIL                                     NOT NULL VARCHAR2(25)

 PHONE_NUMBER                                       VARCHAR2(20)

 HIRE_DATE                                 NOT NULL DATE

 JOB_ID                                    NOT NULL VARCHAR2(10)

 SALARY                                             NUMBER(8,2)

 COMMISSION_PCT                                     NUMBER(2,2)

 MANAGER_ID                                         NUMBER(6)

 DEPARTMENT_ID                                      NUMBER(4)

SQL> CREATE INDEX idx_tbl_1224_01 ON tbl_1224

(EMPLOYEE_ID);  2

Index created.

SQL> select EMAIL from tbl_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

SQL> CREATE OR REPLACE FORCE VIEW vw_1224

AS

    select EMAIL from tbl_1224;  2    3

View created.

SQL> select * from vw_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and object_type ='VIEW';   2    3

OWNER

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

OBJECT_NAME

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

OBJECT_TYPE         STATUS

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

HR1

VW_1224

VIEW                VALID

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';

  2    3

no rows selected

--Alter Table Move 不會讓 depenedncies 的狀態成為 INVALID

SQL> alter table tbl_1224 move;

Table altered.

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

no rows selected

SQL> select

  a.owner       OWNER,

  a.INDEX_NAME  NAME,

  a.TABLE_NAME  TABLE_NAME,

  a.STATUS      STATUS

from all_indexes a

where A.OWNER = 'HR1'

and A.STATUS <> 'VALID';  2    3    4    5    6    7    8

OWNER                          NAME

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

TABLE_NAME                     STATUS

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

HR1                            IDX_TBL_1224_01

TBL_1224                       UNUSABLE

SQL> select * from vw_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

SQL> ALTER INDEX IDX_TBL_1224_01 REBUILD;

Index altered.

-- TOAD Rebuild Table 會讓 depenedncies 的狀態成為 INVALID

SQL> SET LINESIZE 200

SQL> ALTER TABLE HR1.TBL_1224 RENAME TO TBL_1224_X;

Table altered.

SQL> CREATE TABLE HR1.TBL_1224

(

  EMPLOYEE_ID     NUMBER(6),

  FIRST_NAME      VARCHAR2(20 BYTE),

  LAST_NAME       VARCHAR2(25 BYTE)             NOT NULL,

  EMAIL           VARCHAR2(25 BYTE)             NOT NULL,

  PHONE_NUMBER    VARCHAR2(20 BYTE),

  HIRE_DATE       DATE                          NOT NULL,

  JOB_ID          VARCHAR2(10 BYTE)             NOT NULL,

  SALARY          NUMBER(8,2),

  COMMISSION_PCT  NUMBER(2,2),

  MANAGER_ID      NUMBER(6),

  DEPARTMENT_ID   NUMBER(4)

)  2    3    4    5    6    7    8    9   10   11   12   13   14

 15  ;

Table created.

SQL> INSERT /*+ APPEND */

  2  INTO HR1.TBL_1224 INS_TBL

 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,

  PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,

  3    4    5    COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

SELECT

  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,

  PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,

  COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID

FROM HR1.TBL_1224_X SEL_TBL;

  6    7    8    9   10  COMMIT;

214 rows created.

SQL>

Commit complete.

SQL>

SQL>

SQL>

SQL> DROP INDEX HR1.IDX_TBL_1224_01;

Index dropped.

SQL> CREATE INDEX HR1.IDX_TBL_1224_01 ON HR1.TBL_1224

(EMPLOYEE_ID)  2  ;

Index created.

SQL> select

  a.owner       OWNER,

  a.INDEX_NAME  NAME,

  a.TABLE_NAME  TABLE_NAME,

  a.STATUS      STATUS

from all_indexes a

where A.OWNER = 'HR1'

and A.STATUS <> 'VALID';  2    3    4    5    6    7    8

no rows selected

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

OWNER                          OBJECT_NAME                                                                                         OBJECT_TYPE          STATUS

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

HR1                            VW_1224                                                                                             VIEW                 INVALID

SQL> select * from vw_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

-- 使用到 VIEW VW_1224 之後，則DB會自動COMPILE該物件

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

no rows selected

-- 這是手動COMPILE該物件

SQL> ALTER VIEW "HR1"."VW_1224" COMPILE;

View altered.

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

no rows selected

SQL> select

  a.owner       OWNER,

  a.INDEX_NAME  NAME,

  a.TABLE_NAME  TABLE_NAME,

  a.STATUS      STATUS

from all_indexes a

where A.OWNER = 'HR1'

and A.STATUS <> 'VALID';  2    3    4    5    6    7    8

no rows selected

SQL>

Oracle database link and view status

 

結論: 

1. view 裡面有使用到 database link

2. create view 之後，drop database link，view testdb88_hr1_usertables 還是顯示為 VALID

3. 但是 select from testdb88_hr1_usertables 顯示錯誤 ORA-04063: view "HR1.TESTDB88_HR1_USERTABLES" has errors

4. 重新compile TESTDB88_HR1_USERTABLES COMPILE 時，報錯 Warning: compiled but with compilation errors

5. 這時 view status 顯示為 INVALID

CREATE DATABASE LINK HR1_TESTDB88

 CONNECT TO HR1

 IDENTIFIED BY "password"

 USING 'testdb88_DB11G';

CREATE OR REPLACE FORCE VIEW HR1.TESTDB88_HR1_USERTABLES

AS

    SELECT *

      FROM user_tables@HR1_TESTDB88;

  

  

select * from HR1.TESTDB88_HR1_USERTABLES   

  

select OWNER, object_name, OBJECT_TYPE , status

from dba_objects 

where  owner = 'HR1' and object_type ='VIEW'; 

DROP DATABASE LINK HR1_TESTDB88;

select OWNER, object_name, OBJECT_TYPE , status

from dba_objects 

where  owner = 'HR1' and object_type ='VIEW'; 

select * from HR1.TESTDB88_HR1_USERTABLES;

ORA-04063: view "HR1.TESTDB88_HR1_USERTABLES" has errors

SELECT * 

FROM user_tables@HR1_TESTDB88;

Error at line 2

ORA-02019: connection description for remote database not found

ALTER VIEW TESTDB88_HR1_USERTABLES COMPILE;

Warning: compiled but with compilation errors

select OWNER, object_name, OBJECT_TYPE , status

from dba_objects 

where  owner = 'HR1' and object_type ='VIEW'; 

OWNER       OBJECT_NAME                OBJECT_TYPE      STATUS        

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

HR1         TESTDB88_HR1_USERTABLES    VIEW             INVALID

                                                                                

1 row selected.

何時重建索引 When to Rebuild Indexes

 

http://www.dba-oracle.com/t_oracle_analyze_index.htm

Oracle ANALYZE INDEX

Oracle Database Tips by Donald Burleson

http://www.remote-dba.net/t_tuning_index_rebuilding.htm

Index Rebuilding Techniques

Oracle Tips by Burleson Consulting

何時重建索引

如果每次訪問獲取的塊過多，則我們可能要重建索引，因為過多的塊獲取表示碎片化的B樹結構。另一個重建條件是刪除的葉子節點佔索引節點的20％以上的情況。重建的另一個原因是當任何索引顯示的深度為4或更大時。

When to Rebuild Indexes

Rebuild an Oracle index with the following command:

ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;

We might want to rebuild an index if the block gets per access is excessive, since excessive block gets indicate a fragmented B-tree structure. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20 percent of the index nodes. Another reason to rebuild is when any index shows a depth of 4 or greater.

使用alter index rebuild進行索引重建是非常安全的命令。如果出現任何問題，Oracle會中止該操作並將現有索引保留在原處。

Index rebuilding with the alter index rebuild is a very safe command. If anything goes wrong, Oracle aborts the operation and leaves the existing index in place.

Oracle expdp impdp CONTENT=METADATA_ONLY testing

 

新增兩個USER，創建表，賦與權限。實驗看看哪個復原方式可以達到我要的效果。

CREATE USER HR3

  IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE8614C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

  DEFAULT TABLESPACE USERS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

-- 3 Roles for HR3 

GRANT CONNECT TO HR3;

GRANT RESOURCE TO HR3;

GRANT SELECT_CATALOG_ROLE TO HR3;

ALTER USER HR3 DEFAULT ROLE ALL;

-- 5 System Privileges for HR3 

GRANT ALTER SYSTEM TO HR3;

GRANT CREATE DATABASE LINK TO HR3;

GRANT CREATE MATERIALIZED VIEW TO HR3;

GRANT ON COMMIT REFRESH TO HR3;

GRANT UNLIMITED TABLESPACE TO HR3;

CREATE USER HR4

  IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE8614C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

  DEFAULT TABLESPACE TEST_TBS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

-- 3 Roles for HR4 

GRANT CONNECT TO HR4;

GRANT RESOURCE TO HR4;

GRANT SELECT_CATALOG_ROLE TO HR4;

ALTER USER HR4 DEFAULT ROLE ALL;

-- 5 System Privileges for HR4 

GRANT ALTER SYSTEM TO HR4;

GRANT CREATE DATABASE LINK TO HR4;

GRANT CREATE MATERIALIZED VIEW TO HR4;

GRANT ON COMMIT REFRESH TO HR4;

GRANT UNLIMITED TABLESPACE TO HR4;

SQL> create table hr3.EMPLOYEES_TEST as select * from HR1.EMPLOYEES_TEST;

Table created.

SQL> grant select on hr3.EMPLOYEES_TEST to hr4;

Grant succeeded.

[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr3_20201130.dmp

Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:42:39 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr3_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/hr3_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:42:55 2020 elapsed 0 00:00:10

[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr4_20201130.dmp

Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:44:47 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr4_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/hr4_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:45:02 2020 elapsed 0 00:00:07

[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3,HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr34_20201130.dmp

Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:45:17 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3,HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr34_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/hr34_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:45:30 2020 elapsed 0 00:00:07

[oracle@testdb89 dpdump]$ expdp include=user SCHEMAS=HR4 DIRECTORY=DATA_PUMP_DIR dumpfile=userhr4_20201130.dmp

Export: Release 11.2.0.4.0 - Production on Mon Nov 30 13:36:29 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** include=user SCHEMAS=HR4 DIRECTORY=DATA_PUMP_DIR dumpfile=userhr4_20201130.dmp

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/userhr4_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 13:36:41 2020 elapsed 0 00:00:04

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=userhr4_20201130.dmp

Import: Release 11.2.0.4.0 - Production on Mon Nov 30 13:37:24 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=userhr4_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 30 13:38:40 2020 elapsed 0 00:00:02

用expdp include=user 的export file 做impdp，只會create user。沒有相關system, object, role 等權限。

沒有權限，連登入資料庫都會失敗。

[oracle@testdb89 dpdump]$ sqlplus hr4/hr

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 30 13:41:44 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:

ORA-01045: user HR4 lacks CREATE SESSION privilege; logon denied

Enter user-name:

[oracle@testdb89 dpdump]$ sqlplus / as sysdba

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr4_20201130.dmp

缺 -- 1 Object Privilege for HR4 

GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;

[oracle@testdb89 dpdump]$ sqlplus / as sysdba

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Import: Release 11.2.0.4.0 - Production on Mon Nov 30 12:47:47 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"HR3" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "HR3"."EMPLOYEES_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Mon Nov 30 12:47:56 2020 elapsed 0 00:00:02

缺 -- 1 Object Privilege for HR4 

GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;

因為 ORA-39151: Table "HR3"."EMPLOYEES_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

[oracle@testdb89 dpdump]$ sqlplus / as sysdba

SQL> DROP USER HR3 CASCADE;

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Import: Release 11.2.0.4.0 - Production on Mon Nov 30 12:49:58 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 30 12:50:09 2020 elapsed 0 00:00:02

將HR3, HR4 都完整drop，才會恢復的完整。 HR4 有 GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;

SQL> set long 64000

SQL> select dbms_metadata.get_ddl('USER','HR3') from dual;

SQL> select dbms_metadata.get_ddl('USER','HR4') from dual;

DBMS_METADATA.GET_DDL('USER','HR4')

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

   CREATE USER "HR4" IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE86

14C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

      DEFAULT TABLESPACE "TEST_TBS"

      TEMPORARY TABLESPACE "TEMP"

如果是想在開發機，設定和正式機相同的密碼。

ALTER USER "HR4" IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE86

14C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

Oracle Row Chaining and Migration 圖示

 

The Secrets of Oracle Row Chaining and Migration

https://www.akadia.com/services/ora_chained_rows.html

Row Migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

Row Chaining

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

"alter table move tablespace" tips

http://www.dba-oracle.com/t_alter_table_move_index_constraint.htm

reorg

Identifying Oracle Tables with Migrated/Chained Rows http://www.dba-oracle.com/t_identify_chained_rows.htm

資料鏈結（Chained Row）有兩種型態

http://dbtim.blogspot.com/2016/05/1-3.html

可用TOAD Rebuild Table 或是 Repair Chained Rows (好用)來做 優化

analyze table TableName compute statistics;

Oracle 11g Default Schemas

以下內容節錄自 

http://www.oracle-wiki.net/premium:startdocsdefaultschemas

Oracle 11g Default Schemas

DEFAULT SCHEMA

USER NAME DEFAULT TABLESPACE TEMPORARY TABLESPACE LOCKED? DBA?

ANONYMOUS SYSAUX TEMP YES

APEX_030200 SYSAUX TEMP YES

APEX_PUBLIC_USER USERS TEMP YES

APPQOSSYS SYSAUX TEMP YES

BI USERS TEMP YES

CTXSYS SYSAUX TEMP YES

DBSNMP SYSTEM TEMP

DIP USERS TEMP YES

EXFSYS SYSAUX TEMP YES

FLOWS_FILES SYSAUX TEMP YES

HR USERS TEMP YES

IX USERS TEMP YES

MDDATA USERS TEMP YES

MDSYS SYSAUX TEMP YES

MGMT_VIEW SYSTEM TEMP

OE USERS TEMP YES

OLAPSYS SYSAUX TEMP YES

ORACLE_OCM USERS TEMP YES

ORDDATA SYSAUX TEMP YES

ORDPLUGINS SYSAUX TEMP YES

ORDSYS SYSAUX TEMP YES

OUTLN SYSTEM TEMP YES

OWBSYS SYSAUX TEMP YES

OWBSYS_AUDIT SYSAUX TEMP YES

PM USERS TEMP YES

SCOTT USERS TEMP YES

SH USERS TEMP YES

SI_INFORMTN_SCHEMA SYSAUX TEMP YES

SPATIAL_CSW_ADMIN_USR USERS TEMP YES

SPATIAL_WFS_ADMIN_USR USERS TEMP YES

SYS SYSTEM TEMP YES

SYSMAN SYSAUX TEMP

SYSTEM SYSTEM TEMP YES

WMSYS SYSAUX TEMP YES

XDB SYSAUX TEMP YES

XS$NULL USERS TEMP YES

SCHEMA OVERVIEW

ANONYMOUS

Purpose:Account that allows HTTP access to Oracle XML DB. It is used in place of the APEX_PUBLIC_USER account when the Embedded PL/SQL Gateway (EPG) is installed in the database. EPG is a Web server that can be used with Oracle Database. It provides the necessary infrastructure to create dynamic applications. See also XDB.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/catqm.sql

APEX_030200

Purpose:Part of the Oracle Application Express Suite - (Oracle APEX, previously named Oracle HTML DB) which is a freeware software development environment. It allows a fast development cycle to be achieved to create web based applications. The account owns the Application Express schema and metadata. See also APEX_PUBLIC_USER and FLOW_FILES.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/apex/apexins.sql

APEX_PUBLIC_USER

Purpose:Part of the Oracle Application Express Suite - (Oracle APEX, previously named Oracle HTML DB) which is a freeware software development environment. It allows a fast development cycle to be achieved to create web based applications. This minimally privileged account is used for Application Express configuration with Oracle HTTP Server and mod_plsql. See also APEX_030200 and FLOW_FILES.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/apex/apexins.sql

APPQOSSYS

Purpose:Used for storing/managing all data and metadata required by Oracle Quality of Service Management.

Safe To Remove:Yes

Recreation Script:$ORACLE_ADMIN/rdbms/admin/catqos.sql

BI

Purpose:The account that owns the Business Intelligence schema included in the Oracle Sample Schemas. See also HR, OE, SH, IX and PM.

Safe To Remove:Yes – run $ORACLE_HOME/demo/schema/drop_sch.sql

Recreation Script:$ORACLE_HOME/demo/schema/bus_intelligence/bi_main.sql

CTXSYS

Purpose:The account used to administer Oracle Text. Oracle Text enables the building of text query applications and document classification applications. It provides indexing, word and theme searching, and viewing capabilities for text.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ctx/admin/ctxsys.sql

DBSNMP

Purpose:The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database. Password is created at installation or database creation time.

Safe To Remove:Yes – run $ORACLE_HOME/rdbms/admin/catnsnmp.sql

Recreation Script:$ORACLE_HOME/rdbms/admin/catsnmp.sql

DIP

Purpose:The account used by the Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/catdip.sql

EXFSYS

Purpose:The account used internally to access the EXFSYS schema, which is associated with the Rules Manager and Expression Filter feature. This feature enables the building of complex PL/SQL rules and expressions. The EXFSYS schema contains the Rules Manager and Expression Filter DDL, DML, and associated metadata.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/exfsys.sql

FLOW_FILES

Purpose:Part of the Oracle Application Express Suite - (Oracle APEX, previously named Oracle HTML DB) which is a freeware software development environment. It allows a fast development cycle to be achieved to create web based applications. This account owns the Application Express uploaded files. See also APEX_030200 and APEX_PUBLIC_USER.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/apex/apexins.sql

HR

Purpose:The account that owns the Human Resources schema included in the Oracle Sample Schemas. See also BI, OE, SH, IX and PM.

Safe To Remove:Yes – run $ORACLE_HOME/demo/schema/drop_sch.sql

Recreation Script:$ORACLE_HOME/demo/schema/human_resources/hr_main.sql

IX

Purpose:The account that owns the Information Transport schema included in the Oracle Sample Schemas. See also BI, HR, OE, SH and PM.

Safe To Remove:Yes – run $ORACLE_HOME/demo/schema/drop_sch.sql

Recreation Script:$ORACLE_HOME/demo/schema/info_exchange/ix_main.sql

MDDATA

Purpose:The schema used by Oracle Spatial for storing Geocoder and router data. See also SPATIAL_CSW_ADMIN_USR , SPATIAL_WFS_ADMIN_USR and MDSYS.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/md/admin/catmd.sql

MDSYS

Purpose:The Oracle Spatial and Oracle Multimedia Locator administrator account. See also SPATIAL_CSW_ADMIN_USR , MDDATA and SPATIAL_WFS_ADMIN_USR.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

MGMT_VIEW

Purpose:An account used by Oracle Enterprise Manager Database Control. Password is randomly generated at installation or database creation time. Users do not need to know this password.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager

OE

Purpose:The account that owns the Order Entry schema included in the Oracle Sample Schemas. See also BI, HR, SH, IX and PM.

Safe To Remove:Yes – run $ORACLE_HOME/demo/schema/drop_sch.sql

Recreation Script:$ORACLE_HOME/ demo/schema/order_entry/oe_main.sql

OLAPSYS

Purpose:The account that owns the OLAP Catalog (CWMLite). This account has been deprecated, but is retained for backward compatibility.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/olap/admin/amdsys.sql

ORACLE_OCM

Purpose:This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/catocm.sql

ORDDATA

Purpose:This account contains the Oracle Multimedia DICOM data model.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ord/admin/ordisysc.sql

ORDPLUGINS

Purpose:The Oracle Multimedia user. Plug-ins supplied by Oracle and third-party, format plug-ins are installed in this schema. Oracle Multimedia enables Oracle Database to store, manage, and retrieve images, audio, video, DICOM format medical images and other objects, or other heterogeneous media data integrated with other enterprise information. See also ORDSYS and SI_INFORMTN_SCHEMA.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

ORDSYS

Purpose:The Oracle Multimedia administrator account. See also ORDPLUGINS and SI_INFORMTN_SCHEMA.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

OUTLN

Purpose:The account that supports plan stability. Plan stability prevents certain database environment changes from affecting the performance characteristics of applications by preserving execution plans in stored outlines. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

Safe To Remove:No

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq. Recover from backup or recreate the database.

OWBSYS

Purpose:The account for administrating the Oracle Warehouse Builder repository. Access this account during the installation process to define the base language of the repository and to define Warehouse Builder workspaces and users. A data warehouse is a relational or multidimensional database that is designed for query and analysis. See also OWBSYS_AUDIT.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql

OWBSYS_AUDIT

Purpose:This account is used by the Warehouse Builder Control Center Agent to access the heterogeneous execution audit tables in the OWBSYS schema.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql

PM

Purpose:The account that owns the Product Media schema included in the Oracle Sample Schemas. See also BI, HR, OE, SH and IX.

Safe To Remove:Yes – run $ORACLE_HOME/demo/schema/drop_sch.sql

Recreation Script:$ORACLE_HOME/demo/schema/product_media/pm_main.sql

SCOTT

Purpose:An account used by Oracle sample programs and examples.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/utlsampl.sql

SH

Purpose:The account that owns the Sales History schema included in the Oracle Sample Schemas and is only available for Enterprise Edition installations. See also BI, HR, OE, IX and PM.

Safe To Remove:Yes – run $ORACLE_HOME/demo/schema/drop_sch.sql

Recreation Script:$ORACLE_HOME/demo/schema/sales_history/sh_main.sql

SI_INFORMTN_SCHEMA

Purpose:The account that stores the information views for the SQL/MM Still Image Standard. See also ORDPLUGINS and ORDSYS.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

SPATIAL_CSW_ADMIN_USR

Purpose:The Catalog Services for the Web (CSW) account. It is used by the Oracle Spatial CSW cache manager to load all record type metadata, and record instances from the database into the main memory for the record types that are cached. See also SPATIAL_WFS_ADMIN_USR, MDDATA and MDSYS.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/md/admin/sdocswpv.sql

SPATIAL_WFS_ADMIN_USR

Purpose:The Web Feature Service (WFS) account. It is used by the Oracle Spatial WFS cache manager to load all feature type metadata, and feature instances from the database into main memory for the feature types that are cached. See also SPATIAL_CSW_ADMIN_USR , MDDATA and MDSYS.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/md/admin/sdowfspv.sql

SYS

Purpose:An account used to perform database administration tasks. Password is created at installation or database creation time.

Safe To Remove:No

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq. Recover from backup or recreate the database.

SYSMAN

Purpose:The account used to perform Oracle Enterprise Manager database administration tasks. The SYS and SYSTEM accounts can also perform these tasks. Password is created at installation or database creation time.

Safe To Remove:Yes

Recreation Script:Created as part of the dbconsole or Enterprise Manager build.

SYSTEM

Purpose:A default generic database administrator account for Oracle databases. For production systems, Oracle recommends creating individual database administrator accounts and not using the generic SYSTEM account for database administration operations. Password is created at installation or database creation time.

Safe To Remove:No

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq. Recover from backup or recreate the database.

WMSYS

Purpose:The account used to store the metadata information for Oracle Workspace Manager.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/owmctab.plb

XDB

Purpose:The account used for storing Oracle XML DB data and metadata. See also ANONYMOUS.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/catqm.sql

XS$NULL

Purpose:An internal account that represents the absence of a user in a session. Because XS$NULL is not a user, this account can only be accessed by the Oracle Database instance. XS$NULL has no privileges and no one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL.

Safe To Remove:No

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq. Recover from backup or recreate the database.

OTHER ADMINISTRATIVE SCHEMA

The schema listed below are not installed by default, but can be built using the creation script(s) cited and any necessary additional steps as prescribed by the appropriate Oracle manual.

LBACSYS

Purpose:The account used to administer Oracle Label Security (OLS). It is created only when the Label Security custom option is installed.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/rdbms/admin/catlbacs.sql

WK_TEST

Purpose:The instance administrator for the default instance, WK_INST. After unlocking this account and assigning this user a password, then the cached schema password must also be updated using the administration tool Edit Instance Page. Ultra Search provides uniform search-and-location capabilities over multiple repositories, such as Oracle databases, other ODBC compliant databases, IMAP mail servers, HTML documents managed by a Web server, files on disk, and more. See also WKSYS

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ultrasearch/admin/wk0csys.sql

WKSYS

Purpose:An Ultra Search database super-user. WKSYS can grant super-user privileges to other users, such as WK_TEST. All Oracle Ultra Search database objects are installed in the WKSYS schema. See also WK_TEST

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ultrasearch/admin/wk0csys.sql

WKPROXY

Purpose:An administrative account of Application Server Ultra Search.

Safe To Remove:Yes

Recreation Script:$ORACLE_HOME/ultrasearch/admin/wk0csys.sql