oracle實驗記錄（移動outlines）

Moving Outline Tables 移動outline table
Oracle creates the USER_OUTLINES and USER_OUTLINE_HINTS views based on data in the OL$ and OL$HINTS tables, respectively. Oracle creates these tables, and

also the OL$NODES table, in the SYSTEM tablespace using a schema called OUTLN. If outlines use too much space in the SYSTEM tablespace, then you can move

them. To do this, create a separate tablespace and move the outline tables into it using the following process.

所查的表 (USER_OUTLINES(基於ol$,ol$hints建立)) 預設建立在SYSTEM TABLESPACE

SQL> select owner,object_type from dba_objects where object_name='USER_OUTLINES';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SYS                            VIEW~~~~~~~~~~~~~~~~~~~~~~~基於ol$建立
PUBLIC                         SYNONYM~~~~~~~~~~~~~``普通使用者訪問的是SYNONYM
關於靜態 view參考(oracle資料字典實驗記錄)

SQL> select text from dba_views where wner='SYS' and view_name='USER_OUTLINES';

TEXT
--------------------------------------------------------------------------------
select ol_name, category,
decode(bitand(flags, 1), 0, 'UNUSED', 1, 'USED'),

SQL> set autotrace traceonly exp
SQL> select * from user_outlines;

執行計劃
----------------------------------------------------------
Plan hash value: 3570962666

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

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

| 0 | SELECT STATEMENT | | 1 | 137 | 2 (0)| 00
:00:01 |

| 1 | NESTED LOOPS | | 1 | 137 | 2 (0)| 00
:00:01 |

| 2 | TABLE ACCESS FULL | OL$ | 1 | 120 | 2 (0)| 00~~~~~~~~~~~~~~~基於 ol$

SQL> select TABLESPACE_NAME,table_name from dba_tables where table_name='OL$';

TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYSTEM OL$~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~放在system tablespace

If outlines use too much space in the SYSTEM tablespace, then you can move them. To do this, create a separate tablespace and move the outline tables into it

using the following process.

如果覺得它佔得就MOVE 了
SQL> alter table ol$ move tablespace users;
alter table ol$ move tablespace users 不能用這種方法MOVE
*
第 1 行出現錯誤:
ORA-14451: 不受支援的臨時表功能

如下過程
The default system tablespace could become exhausted if the CREATE_STORED_OUTLINES parameter is on and if the running application has many literal SQL

statements. If this happens, then use the DBMS_OUTLN.DROP_UNUSED procedure to remove those literal SQL outlines.

Use the Oracle Export utility to export the OL$, OL$HINTS, and OL$NODES tables:

EXP OUTLN/outln_password
FILE = exp_file TABLES = 'OL$' 'OL$HINTS' 'OL$NODES'
Start SQL*Plus and connect to the database.

CONNECT OUTLN/outln_password;
Remove the previous OL$, OL$HINTS, and OL$NODES tables:

DROP TABLE OL$;
DROP TABLE OL$HINTS;
DROP TABLE OL$NODES;
Create a new tablespace for the tables:

CONNECT SYSTEM/system_password;
CREATE TABLESPACE outln_ts
DATAFILE 'tspace.dat' SIZE 2M
DEFAULT STORAGE (INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10)
ONLINE;
Enter the following statement to change the default tablespace:

ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;
To force the import into the OUTLN_TS tablespace, set quota for the SYSTEM tablespace to 0K for the OUTLN user. You will also need to revoke the UNLIMITED

TABLESPACE privilege and all roles, such as the RESOURCE role, that have unlimited tablespace privileges or quotas. Set a quota for the OUTLN tablespace.

Import the OL$, OL$HINTS, and OL$NODES tables:

IMP OUTLN/outln_password
FILE = exp_file TABLES = (OL$, OL$HINTS, OL$NODES)

SQL> select owner, object_id,object_type from dba_objects where object_name='OL$';

OWNER                           OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------------
PUBLIC                               5484 SYNONYM
SYSTEM                               5476 TABLE
OUTLN                                 452 TABLE

SQL> select owner, object_id,object_type from dba_objects where object_name='OL$HIN
TS';

OWNER                           OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------------
PUBLIC                               5485 SYNONYM
SYSTEM                               5477 TABLE
OUTLN                                 453 TABLE

SQL> select TABLESPACE_NAME,table_name from dba_tables where table_name='OL$NODES'
;

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
SYSTEM                         OL$NODES
                               OL$NODES

SQL> alter user outln identified by a123
2 ;

使用者已更改。
SQL> alter user outln account unlock;

使用者已更改。
SQL> conn system/a831115
已連線。
SQL> select count(*) from ol$;

COUNT(*)
----------
0

SQL> select count(*) from ol$hints;

COUNT(*)
----------
0

SQL> select count(*) from ol$nodes;

COUNT(*)
----------
0

SQL> conn outln/a123
已連線。
SQL> select count(*) from ol$;~~~~~~~~~~~~~~~~~~要匯出 outln schema的

COUNT(*)
----------
20

SQL> select count(*) from ol$hints;

COUNT(*)
----------
122

SQL> select count(*) from ol$nodes;

COUNT(*)
----------
34

C:\>exp outln/a123@xh file=d:\ol.dmp tables=ol$,ol$hints,ol$nodes;

Export: Release 10.2.0.1.0 - Production on 星期五 9月 25 11:47:32 2009

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將匯出指定的表通過常規路徑...
. . 正在匯出表 OL$匯出了 20 行
. . 正在匯出表 OL$HINTS匯出了 122 行
EXP-00011: OUTLN.OL$NODES; 不存在~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`不存在
匯出成功終止, 但出現警告。

SQL> conn outln/a123
已連線。
SQL> drop table ol$;

表已刪除。

SQL> drop table ol$hints;

表已刪除。

SQL> drop table ol$nodes;

表已刪除。

SQL> select * from user_outlines where name='PUBILC_MY_T3'(其它SESSION)
2 ;
select * from user_outlines where name='PUBILC_MY_T3'
*
第 1 行出現錯誤:
ORA-04063: view "SYS.USER_OUTLINES" 有錯誤

SQL> select * from dba_outlines where name='PUBILC_MY_T3'(其它SESSION)
2 ;
select * from dba_outlines where name='PUBILC_MY_T3'
*
第 1 行出現錯誤:
ORA-04063: view "SYS.DBA_OUTLINES" 有錯誤

SQL> conn / as sysdba
已連線。
SQL> create tablespace outline_ts datafile 'd:\xhdatafile\outline.dbf' size 20m;

表空間已建立。

SQL> conn outln/a123
已連線。
SQL> select * from session_roles;

ROLE
------------------------------
RESOURCE

SQL>

SQL> alter user outln default tablespace outline_ts;

使用者已更改。
注意這個步驟
ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;
To force the import into the OUTLN_TS tablespace, set quota for the SYSTEM tablespace to 0K for the OUTLN user. You will also need to revoke the UNLIMITED

TABLESPACE privilege and all roles, such as the RESOURCE role, that have unlimited tablespace privileges or quotas. Set a quota for the OUTLN tablespace.
SQL> conn / as sysdba
已連線。
SQL> alter user outln quota 0 on system;

使用者已更改。

SQL> alter user outln quota unlimited on outline_ts;

使用者已更改。
SQL> revoke unlimited tablespace from outln
2 ;

C:\>imp outln/a123@xh file=d:\ol.dmp tables=ol$,ol$hints,ol$nodes;

Import: Release 10.2.0.1.0 - Production on 星期五 9月 25 12:05:43 2009

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案
已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入
. 正在將 OUTLN 的物件匯入到 OUTLN
. 正在將 OUTLN 的物件匯入到 OUTLN
. . 正在匯入表 "OL$"匯入了 20 行

IMP-00017: 由於 ORACLE 錯誤 1536, 以下語句失敗:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~OL$HINTS好象必須在system tablespace
"CREATE TABLE "OL$HINTS" ("OL_NAME" VARCHAR2(30), "HINT#" NUMBER, "CATEGORY""
" VARCHAR2(30), "HINT_TYPE" NUMBER, "HINT_TEXT" VARCHAR2(512), "STAGE#" NUMB"
"ER, "NODE#" NUMBER, "TABLE_NAME" VARCHAR2(30), "TABLE_TIN" NUMBER, "TABLE_P"
"OS" NUMBER, "REF_ID" NUMBER, "USER_TABLE_NAME" VARCHAR2(64), "COST" FLOAT(1"
"26), "CARDINALITY" FLOAT(126), "BYTES" FLOAT(126), "HINT_TEXTOFF" NUMBER, ""
"HINT_TEXTLEN" NUMBER, "JOIN_PRED" VARCHAR2(2000), "SPARE1" NUMBER, "SPARE2""
" NUMBER, "HINT_STRING" CLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255"
" STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) T"
"ABLESPACE "SYSTEM" LOGGING NOCOMPRESS LOB ("HINT_STRING") STORE AS (TABLES"
"PACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGIN"
"G STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)"
")"
IMP-00003: 遇到 ORACLE 錯誤 1536
ORA-01536: 超出表空間 'SYSTEM' 的空間限額

SQL> select name from dba_outlines where name='PUBILC_MY_T3';

NAME
------------------------------
PUBILC_MY_T3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`可以使用了

SQL> conn outln/a123
已連線。
SQL> select tablespace_name from user_tables where table_name='OL$';

TABLESPACE_NAME
------------------------------
OUTLINE_TS
SQL> grant unlimited tablespace to outln;

授權成功。
C:\>imp outln/a123@xh file=d:\ol.dmp tables=ol$,ol$hints,ol$nodes;

. . 正在匯入表 "OL$HINTS"匯入了 122 行
IMP-00033: 警告: 在匯出檔案中未找到表 "OL$NODES;"
成功終止匯入, 但出現警告。
SQL> conn outln/a123
已連線。
SQL> select tablespace_name from user_tables where table_name='OL$HINTS';

TABLESPACE_NAME
------------------------------
SYSTEM

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-615548/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄 （移動outlines）

相關文章

oracle實驗記錄（移動outlines）