oracle基本操作-16.htm

微软数据库开发梦工场多媒体教学-oracle篇.rar,是多媒体教学的

    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;select * from [e-mail];</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">返回结果为beijing.test.com.cn就对了。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">用system身份登录beijing数据库：</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;create public database link 
          shenzhen.test.com.cn using 'shenzhen';</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">测试数据库全局名称和公共的数据库链接</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;select * from [e-mail];</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">返回结果为shenzhen.test.com.cn就对了。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">3、建立管理数据库复制的用户repadmin，并赋权。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">①、用system身份登录shenzhen数据库</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;create user repadmin identified 
          by repadmin default tablespace users temporary tablespace temp;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;execute dbms_defer_sys.register_propagator('repadmin');</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;grant execute any procedure 
          to repadmin;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;grant comment any table to 
          repadmin;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;grant lock any table to repadmin;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD 
  class=a14><font color="#FFFFFF">②、同样用system身份登录beijing数据库，运行以上的命令，管理数据库复制的用户repadmin，并赋权。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">说明：repadmin用户名和密码可以根据用户的需求自由命名。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">4、在数据库复制的用户repadmin下创建私有的数据库链接。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">①、用repadmin身份登录shenzhen数据库</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;create database link beijing.test.com.cn 
          connect to repadmin identified by repadmin;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">测试这个私有的数据库链接：</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;select * from [e-mail];</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">返回结果为beijing.test.com.cn就对了。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">②、用repadmin身份登录beijing数据库</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;create database link shenzhen.test.com.cn 
          connect to repadmin identified by repadmin;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">测试这个私有的数据库链接</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;select * from [e-mail];</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">返回结果为shenzhen.test.com.cn就对了。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">5、创建或选择实现数据库复制的用户和对象，给用户赋权，数据库对象必须有主关键字。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">假设我们用ORACLE里举例用的scott用户，dept表。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">①、用internal身份登录shenzhen数据库，创建scott用户并赋权</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;create user scott identified 
          by tiger default tablespace users temporary tablespace temp;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;grant connect, resource to 
          scott;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;grant execute on sys.dbms_defer 
          to scott;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">②、用scott身份登录shenzhen数据库，创建表dept</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;create table dept</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">(deptno number(2) primary key,</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">dname varchar2(14),</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">loc varchar2(13) );</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">③、如果数据库对象没有主关键字，可以运行以下SQL命令添加：</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;alter table dept add (constraint 
          dept_deptno_pk primary key (deptno));</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD 
  class=a14><font color="#FFFFFF">④、在shenzhen数据库scott用户下创建主关键字的序列号，范围避免和beijing的冲突。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt; create sequence dept_no increment 
          by 1 start with 1 maxvalue 44 cycle nocache;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">(说明：maxvalue 44可以根据应用程序及表结构主关键字定义的位数需要而定)</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">⑤、在shenzhen数据库scott用户下插入初始化数据</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;insert into dept values (dept_no.nextval,'accounting','new 
          york');</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;insert into dept values (dept_no.nextval,'research','dallas');</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt;commit;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">⑥、在beijing数据库那边同样运行以上①，②，③</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD 
  class=a14><font color="#FFFFFF">⑦、在beijing数据库scott用户下创建主关键字的序列号，范围避免和shenzhen的冲突。</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>
    <TBODY>
      <TR> 
        <TD class=a14><font color="#FFFFFF">SQL&gt; create sequence dept_no increment 
          by 1 start with 45 maxvalue 99 cycle nocache;</font></TD>
      </TR>
    </TBODY>
  </TABLE>
  <TABLE width=620 align=center>