[20120816]快速修改表的schema.txt

最新推荐文章于 2023-11-14 20:43:55 发布
最新推荐文章于 2023-11-14 20:43:55 发布
阅读量101 收藏
点赞数
[20120816]快速修改表的schema.txt

    前几天在测试环境,开发人员把几个表建成system schema,需要修改成别的schema模式.当然方法很多,ctas,exp/imp等等.数据库建立在他自己的机器(数据库是11G的),我建议他可以直接修改sys.obj$对象.最后他发现修改后,查看dba_objects依旧是原来的schema.我自己也做了测试.

测试如下:

1.测试环境 
SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

grant dba to test1 identified by test1;
grant dba to test2 identified by test2;

connect test1/test1
create table t as select rownum id,'test' name from dual connect by level <=1000;
create unique index i_t_id on t(id);


2.查看建立的对象: 

SQL> column owner format a10
SQL> column object_name format a10
SQL> column segment_name format a10
SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID');
OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- ---------- ---------- -------------- -------------------
TEST1      I_T_ID         104533         104533 INDEX
TEST1      T              104532         104532 TABLE

SQL> SELECT owner, segment_name,segment_type, header_file, header_block FROM dba_segments WHERE owner LIKE 'TEST_' AND segment_name IN ('T', 'I_T_ID');
OWNER      SEGMENT_NA SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK
---------- ---------- ------------------ ----------- ------------
TEST1      I_T_ID     INDEX                        4        52474
TEST1      T          TABLE                        4        13922


3.修改底层sys.obj$ 

SQL> select obj#,dataobj#,owner#,name from obj$ where obj# in (104532,104533);

      OBJ#   DATAOBJ#     OWNER# NAME
---------- ---------- ---------- ------------------------------
    104532     104532        100 T
    104533     104533        100 I_T_ID

SQL> select user#,name from user$ where name like 'TEST_';
     USER# NAME
---------- ------------------------------
       100 TEST1
       101 TEST2


--再次强调,不要在生产系统做这样的操作!!!! 
update (select obj#,dataobj#,owner#,name from obj$ where obj# in (104532,104533)) set owner#=101;
commit;

4.查看视图dba_objects,dba_segments: 

SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID');
OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- ---------- ---------- -------------- -------------------
TEST1      I_T_ID         104533         104533 INDEX
TEST1      T              104532         104532 TABLE

SQL> SELECT owner, segment_name,segment_type, header_file, header_block FROM dba_segments WHERE owner LIKE 'TEST_' AND segment_name IN ('T', 'I_T_ID');
OWNER      SEGMENT_NA SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK
---------- ---------- ------------------ ----------- ------------
TEST2      I_T_ID     INDEX                        4        52474
TEST2      T          TABLE                        4        13922

--奇怪视图dba_objects显示的owner=test1,没有变,why?少修改了什么?


5.查看执行计划: 
SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID');

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bz93xvx2xynu5, child number 0
-------------------------------------
SELECT owner,object_name,object_id,data_object_id,object_type FROM
dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID')

Plan hash value: 4063009738

-------------------------------------------------------------------------------
| Id  | Operation                         | Name        | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |        |     8 (100)|
|   1 |  VIEW                             | DBA_OBJECTS |      2 |     8   (0)|
|   2 |   UNION-ALL                       |             |        |            |
|*  3 |    TABLE ACCESS BY INDEX ROWID    | SUM$        |      1 |     1   (0)|
|*  4 |     INDEX UNIQUE SCAN             | I_SUM$_1    |      1 |     0   (0)|
|*  5 |    FILTER                         |             |        |            |
|   6 |     NESTED LOOPS                  |             |      1 |     7   (0)|
|   7 |      NESTED LOOPS                 |             |      1 |     6   (0)|
|   8 |       TABLE ACCESS BY INDEX ROWID | USER$       |      1 |     2   (0)|
|*  9 |        INDEX RANGE SCAN           | I_USER1     |      1 |     1   (0)|
|  10 |       INLIST ITERATOR             |             |        |            |
|* 11 |        TABLE ACCESS BY INDEX ROWID| OBJ$        |      1 |     4   (0)|
|* 12 |         INDEX RANGE SCAN          | I_OBJ5      |      1 |     3   (0)|
|* 13 |      INDEX RANGE SCAN             | I_USER2     |      1 |     1   (0)|
|* 14 |     TABLE ACCESS BY INDEX ROWID   | IND$        |      1 |     2   (0)|
|* 15 |      INDEX UNIQUE SCAN            | I_IND1      |      1 |     1   (0)|
|  16 |     NESTED LOOPS                  |             |      1 |     2   (0)|
|* 17 |      INDEX FULL SCAN              | I_USER2     |      1 |     1   (0)|
|* 18 |      INDEX RANGE SCAN             | I_OBJ4      |      1 |     1   (0)|
|  19 |    NESTED LOOPS                   |             |      1 |     1   (0)|
|* 20 |     INDEX FULL SCAN               | I_LINK1     |      1 |     0   (0)|
|* 21 |     TABLE ACCESS CLUSTER          | USER$       |      1 |     1   (0)|
|* 22 |      INDEX UNIQUE SCAN            | I_USER#     |      1 |     0   (0)|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   4 - access("S"."OBJ#"=:B1)
   5 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1
              AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND
              "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
              (INTERNAL_FUNCTION("O"."TYPE#") AND
              ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
              "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR
               IS NOT NULL)))))
   9 - access("U"."NAME" LIKE 'TEST_')
       filter("U"."NAME" LIKE 'TEST_')
  11 - filter(BITAND("O"."FLAGS",128)=0)
  12 - access("O"."SPARE3"="U"."USER#" AND (("O"."NAME"='I_T_ID' OR
              "O"."NAME"='T')) AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL))
  13 - access("O"."OWNER#"="U"."USER#")
  14 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR
              "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
  15 - access("I"."OBJ#"=:B1)
  17 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('us
              erenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('u
              serenv','current_edition_id'))))
  18 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
              "O2"."OWNER#"="U2"."USER#")
  20 - filter(("L"."NAME"='I_T_ID' OR "L"."NAME"='T'))
  21 - filter("U"."NAME" LIKE 'TEST_')
  22 - access("L"."OWNER#"="U"."USER#")

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

78 rows selected.


检查发现如下:
  12 - access("O"."SPARE3"="U"."USER#" AND (("O"."NAME"='I_T_ID' OR
              "O"."NAME"='T')) AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL))

O.spare3=u.user# ?习惯连接条件应该是"O"."OWNER#"="U"."USER#",自己检查相关视图: 

CREATE OR REPLACE FORCE VIEW SYS."_CURRENT_EDITION_OBJ" (obj#,
                                                         dataobj#,
                                                         defining_owner#,
                                                         NAME,
                                                         namespace,
                                                         subname,
                                                         type#,
                                                         ctime,
                                                         mtime,
                                                         stime,
                                                         status,
                                                         remoteowner,
                                                         linkname,
                                                         flags,
                                                         oid$,
                                                         spare1,
                                                         spare2,
                                                         spare3,
                                                         spare4,
                                                         spare5,
                                                         spare6,
                                                         owner#,
                                                         defining_edition
                                                        )
AS
   SELECT o."OBJ#", o."DATAOBJ#", o."OWNER#", o."NAME", o."NAMESPACE", o."SUBNAME", o."TYPE#", o."CTIME", o."MTIME", o."STIME",
          o."STATUS", o."REMOTEOWNER", o."LINKNAME", o."FLAGS", o."OID$", o."SPARE1", o."SPARE2", o."SPARE3", o."SPARE4",
          o."SPARE5", o."SPARE6", o.spare3,
          CASE
             WHEN (o.type# NOT IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 22, 87) OR BITAND (u.spare1, 16) = 0)
                THEN NULL
             WHEN (u.type# = 2)
                THEN (SELECT eo.NAME
                        FROM obj$ eo
                       WHERE eo.obj# = u.spare2)
             ELSE 'ORA$BASE'
          END
     FROM obj$ o, user$ u
    WHERE o.owner# = u.user#
      AND (                                                                                         /* non-versionable object */
              (o.type# NOT IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 22, 87, 88) OR BITAND (u.spare1, 16) = 0
              )
           /* versionable object visible in current edition */
           OR (    o.type# IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 22, 87)
               AND (   (u.type# <> 2 AND SYS_CONTEXT ('userenv', 'current_edition_name') = 'ORA$BASE')
                    OR (u.type# = 2 AND u.spare2 = SYS_CONTEXT ('userenv', 'current_edition_id'))
                    OR EXISTS (
                          SELECT 1
                            FROM obj$ o2, user$ u2
                           WHERE o2.type# = 88
                             AND o2.dataobj# = o.obj#
                             AND o2.owner# = u2.user#
                             AND u2.type# = 2
                             AND u2.spare2 = SYS_CONTEXT ('userenv', 'current_edition_id'))
                   )
              )
          );

--注意 owner# 对应的是  o.spare3 小写,很明显是补进去的.
--与Edition-based Redefinition有关

6.增加修改obj#.spare3字段: 

SQL> update (select obj#,dataobj#,owner#,name,spare3 from obj$ where obj# in (104532,104533)) set spare3=101;
2 rows updated.
SQL> commit ;

SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE object_name IN ('T', 'I_T_ID');
OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- ---------- ---------- -------------- -------------------
TEST2      T              104532         104532 TABLE
TEST2      I_T_ID         104533         104533 INDEX


--OK现在一切正常!




来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-741154/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-741154/

cuixun7780
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
[20160910]快速修改表的schema.txt
09-10 115
[20160910]快速修改表的schema.txt --以前也做过例子： http://blog.itpub.net/267265/viewspace-741154/ http://blog.itpub.net/26...
ORACLE 更改表的schema
billyshao3222的博客
03-27 734
在项目开发中，有时候要更改表的schema，如 irp.user更改成irp2.user，以下为更改的方法： create table irp2.user as select * from irp.user 以上只是把表数据导过去，并且建立表结构，但是不会建立表的约束，如主外键、索引等，需要手工建表的约束条件。 建完约束条件后，就可以drop ta...
db2 删除schema中所有表_详解一款mysql在线DDL操作不锁表工具--pt-online-schema-change...
weixin_39585691的博客
11-28 252
概述作为DBA，我们也常常会碰到这样的需求：需要在不影响线上业务的情况下给表添加一个字段或索引。如果是一张只有几百或几千条记录的小表，这样的需求是非常容易解决的。但如果所管理的表数据量已经上亿、而且应用与数据库交互非常频繁，不允许停机窗口的出现，这样的需求又该如何满足？大多数的alter table操作都会涉及lock-->copy to new table-->rename--&gt...
在线大表的Schema修改
最新发布
你若盛开，清风自来
11-14 187
pt-online-schema-change会先根据源表结构复制出一张新表，在源表上创建trigger(触发器)，自动将新写入/编辑的数据写入到新表，并分批导入历史数据(–chunk-time批的执行时间，根据时间自动调整批的大小)，当拷贝完成后，通过原子的事务，删除老表，新表重命名为原表。默认值0.5，单位秒，pt-online-schema-change会统计每秒钟能拷贝的行数， 然后以这个行数*时间来定义本次拷贝的记录数，因为速度是动态变的，所以行数也是动态变的。
oracle修改表的schema,[20160910]快速修改表的schema.txt
weixin_40008870的博客
04-15 159
[20160910]快速修改表的schema.txt--以前也做过例子：http://blog.itpub.net/267265/viewspace-741154/http://blog.itpub.net/267265/viewspace-744787/--第1种就是修改数据字典的情况，但是这种存在一定的风险，我当时的测试版本11.2.0.1还有修改obj$的字段spare3.--第2种就是利用...
Toad快速入门.txt
09-08
- 方便的数据表创建、修改及删除操作。 - 可视化界面方便进行数据表结构的设计与维护。 - **性能调优**： - 提供性能监控工具，帮助管理员监控数据库运行状态。 - 分析数据库性能瓶颈，提供调优建议。 - **安全...
oracle sqlplus 常用命令大全.txt
05-30
在自动提交模式下，每条 DML 语句都会被立即提交，这有助于快速开发，但在生产环境中可能会导致数据不一致的风险。 #### 5. 分页与数组大小设置 - **`set pause on`**：打开分页显示功能，默认为关闭状态（OFF）。...
eclipse开发环境配置说明.txt
01-13
- **调整鼠标悬停时间**：为了更快速地查看工具提示，可以通过修改注册表来调整鼠标指针悬停时间。 - **路径**：打开注册表编辑器（regedit），找到`HKEY_CURRENT_USER\ControlPanel\Mouse\MouseHoverTime`键，将...
Mondrian schema编辑器插件1.0.2版本.zip
04-15
例如，用户可以在这里找到如何安装和配置插件、如何创建和修改Mondrian schema的步骤，以及插件提供的各种工具和功能的详细说明。 在使用Mondrian Schema编辑器插件时，用户需要了解Mondrian的基本概念，比如维度...
闪回技术、备份恢复与优化.txt
01-21
- 首先，确保表所在的模式支持行移动：`ALTER TABLE schema.table_name ENABLE ROW MOVEMENT;` - 使用`FLASHBACK TABLE schema.table_name TO SCN scn_value;`或`FLASHBACK TABLE schema.table_name TO TIMESTAMP ...
数据库的 Schema 变更实现
KaiwuDB 数据库
06-13 747
元数据变更是数据库管理中不可避免的工作项，减少元数据变更次数可降低数据库维护和管理成本，减轻对业务的影响。在数据库设计和开发阶段，精细设计元数据结构可有效避免设计不合理或不充分的情况；为降低维护难度和变更次数，元数据结构设计应当秉承简单、实用、符合业务需求的原则，避免过度设计；在设计元数据结构时，合并使用约束，包括主键、外键、唯一性约束、非空约束等可以保证数据的完整性和一致性。
查看执行计划的方法
lyp13696402570的博客
12-19 2238
目录 查看执行计划的方法 1、Explain Plan For SQL 1.1 查看plan_table 1.2 解析SQL执行计划 1.3 查看执行计划 2、SQLPLUS AUTOTRACE 2.1 查看plan_table 2.2 查看SQL执行计划 3、SQL TRACE 3.1 执行SQL TRACE 3.2 查看trc文件中的执行计划 4、V$SQL和V$SQL...
db2 改变表的schema_DB2中设置当前会话的Schema
weixin_39807541的博客
12-19 783
在有些情况下，当前登录用户所要访问的Schema可能并不是默认分配给他的Schema，这时查询表前就需要一个前缀来约束所要访问的Schema。如：SELECT * FROM wanted_schema_name.table_name如果每次都这么做，那就相当麻烦，所以必须通过某些手段来修改当前的Schema/Sqlid。在修改前，先学习下如何通过命令行方式查看当前所使用的Schema/Sqlids...
ORACLE中修改表的Schema的总结
weixin_34319374的博客
07-11 852
前阵子遇到一个案例，需要将数据库中的几个表从USER A 移动到USER B下面，在ORACLE中，这个叫做更改表的所有者或者修改表的Schema。其实遇到这种案例，有好几种解决方法。下面我们通过实验来测试、验证一下。首先准备简单测试数据,如下所示:     SQL&gt; CREATE TABLE TEST.KKK( ID   INT   ,    NAME VARCHAR2(12) ,  C...
sql 修改表结构 schema
weixin_30512043的博客
07-23 647
-- ALTER SCHEMA 新架构 TRANSFER 旧架构.对象名称 例如：ALTER SCHEMA FLT TRANSFER dbo.TIME_TABLE_TRIP_OVERRIDE //修改表TIME_TABLE_TRIP_OVERRIDEG 的Schema由dbo变为FLT； 转载于:https://www.cnblogs.com/Martincheng/p/3656708....
更改当前schema
08-24 606
在session中，更改当前schema而不重新登陆。可适用于使用system用户管理其他用户下的数据，而不必在每个表前都加上schema限定词。[@more@]在SQL语句中指定对象时，如果不限定对象所在schema，则会使用...
MySQL8.0-INFORMATION_SCHEMA增强
Python研究所
11-25 217
此文转载自：https://blog.csdn.net/n88Lpo/article/details/100907209 导读 作者：Gopal Shankar 翻译：徐晨亮 原文地址： https://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/ C...
- collector.performance_schema.eventswaits - collector.performance_schema.file_events - collector.performance_schema.indexiowaits - collector.performance_schema.tableiowaits - collector.performance_schema.tablelocks这几参数都需要等号吗
06-07
- performance_schema.eventswaits - performance_schema.file_events - performance_schema.indexiowaits - performance_schema.tableiowaits - performance_schema.tablelocks ``` 在这个配置中，您可以看到...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值