Chinaunix

标题: oracle for the lazy people (1) [打印本页]

作者: sshd 时间: 2005-01-01 04:15
标题: oracle for the lazy people (1)
Have Oracle to write what you want.

Example 1)

If you want to create a table but do not know how, have oracle write the SQL for you.

-----
set long 4000
set pagesize 0
connect scott/tiger
select dbms_metadata.get_ddl('TABLE', 'EMP')
from dual;
-----

The SQL will return the SQL that creates the emp table in scott's schema.  Learn the SQL, modifiy and use it in your own work.

  CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9),
      "MGR" NUMBER(4,0),
      "HIREDATE" DATE,
      "SAL" NUMBER(7,2),
      "COMM" NUMBER(7,2),
      "DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATA"

Example 2)

Show us how scott  create the index on his emp table.
-----
set long 4000
set pagesize 0
select dbms_metadata.get_dependent_ddl('INDEX', 'EMP', 'SCOTT')
from dual;
-----

Example3)

Show us how tablespace data is created:

-----
set long 4000
set pagesize 0
select dbms_metadata.get_ddl('TABLESPACE', TABLESPACE_NAME)
from dba_tablespaces
where tablespace_name = 'DATA';
-----

  CREATE TABLESPACE "DATA" DATAFILE
  '/oracle/920/oradata/db920/data_1.dbf' SIZE 10485760 REUSE
  AUTOEXTEND ON NEXT 4096 MAXSIZE UNLIMITED
  LOGGING ONLINE PERMANENT BLOCKSIZE 4096
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL

Now you can start to learn what is means, modify it and use it in your own work.

作者: sshd 时间: 2005-01-01 04:21
标题: oracle for the lazy people (1)
Exampe 1) and 2)
connect scott/tiger
Example 3)
connect / as sysdba
if you remove the "where" clause you get the DDLs for all tablespaces in the system.

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)