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

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

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   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

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

连接到: 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

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

连接到: 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-615548/