oracle中怎么查看某个表的所有者？

山野村夫

你搞错用法了，from user_objects 的目的是取list

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

TOADLover

原帖由 山野村夫 于 2009-8-11 16:53 发表
你搞错用法了，from user_objects 的目的是取list

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

--------------------
这样倒是可以的(不过显示的是所有'TABLE'):
--------------------
SQL> select dbms_metadata.get_ddl('TABLE', u.table_name) from user_tables u;

DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) 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 "USERS"



  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) 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 "USERS"



  CREATE TABLE "SCOTT"."BONUS"
   (    "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "SAL" NUMBER,
        "COMM" NUMBER
   ) 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 "USERS"



  CREATE TABLE "SCOTT"."SALGRADE"
   (    "GRADE" NUMBER,
        "LOSAL" NUMBER,
        "HISAL" NUMBER
   ) 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 "USERS"



  CREATE TABLE "SCOTT"."EMP3"
   (    "ENAME" VARCHAR2(10),
        "AGE" NUMBER(4,0),
        "SAL" NUMBER(7,2)
   ) PCTFREE 10 PCTUSED 0 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 "USERS"



SQL>

TOADLover

原帖由 山野村夫 于 2009-8-11 16:53 发表
你搞错用法了，from user_objects 的目的是取list

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

-----------------------
怎么把
SQL> set long 999999
SQL> set pagesize 50000
让SQL*PLUS记住这些设置，下次启动SQL*PLUS时自动应用？？？？？？？

TOADLover

------------------
你说的用from dual也是对的:
--------------------
SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) 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 "USERS"



SQL>

Minsic

当然要用dual的啊，前面的函数dbms_metadata.get_ddl是要对每一行做操作的啊，如果你确实要得到用户每张表的定义，那就像31楼写的那样。

更改sqlplus初始设置：$ORACLE_HOME/sqlplus/admin/glogin.sql这个文件