Oracle9.2 Concepts Guide学习笔记(1221)dbms_metadata包

1220-1. Views with the Prefix DBA

Views with the prefix DBA show a global view of the entire database. Synonyms are

not created for these views, because DBA views should be queried only by

administrators.

以DBA开头的视图是实体数据库的一个全面的展示。不能为这些视图创建同义词，因为这些视图只有管理员有权访问。

1221-1. SYS owns the dynamic performance tables; their names all begin with V_$. Views

are created on these tables, and then public synonyms are created for the views. The

synonym names begin with V$. For example, the V$DATAFILE view contains

information about the database’s datafiles, and the V$FIXED_TABLE view contains

information about all of the dynamic performance tables and views in the database.

（SYS是动态性能表的所有者，这些表的名字都以V_$开头。可以在这些表上建立视图并为这些视图创建公共同义词。这些同义词都以V$开头。）

-2.Database Object Metadata

The DBMS_METADATA package provides interfaces for extracting complete

definitions of database objects. The definitions can be expressed either as XML or as

SQL DDL. Two styles of interface are provided:

_ A flexible, sophisticated interface for programmatic control

_ A simplified interface for ad hoc querying

这里提到关于DBMS_METADATA这样一个包，可以显示数据库对象的完整的定义语句。于是专门针对这个包查了下关于它的使用的一些资料，下面是EYGLE大师关于使用这个包的一个介绍：

自己也动手测试了一下

Set desc depth 1(设置desc的显示深度为1)

Desc dbms_metadata;

其中有下面一个函数：

FUNCTION GET_DDL RETURNS CLOB

参数名称                       类型                    输入/输出默认值？

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

 OBJECT_TYPE                    VARCHAR2                IN

 NAME                           VARCHAR2                IN

 SCHEMA                         VARCHAR2                IN     DEFAULT

 VERSION                        VARCHAR2                IN     DEFAULT

 MODEL                          VARCHAR2                IN     DEFAULT

 TRANSFORM                      VARCHAR2                IN     DEFAULT

了解了这个函数后下面就可以测试了

Set long 5000(注意这个参数得设置下如果不设置的话可能下面显示的DDL语句信息会不完全)

SQL> desc test;

 名称                                      是否为空? 类型

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

 AAA                                                VARCHAR2(50)

 BBB                                       NOT NULL NUMBER(5)

 

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TEST')

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

 

  CREATE TABLE "HANGAN"."TEST"

   (    "AAA" VARCHAR2(50),

        "BBB" NUMBER(5,0) NO

上面是没设置long长度，所以没有显示完全，以下是set long 5000的

SQL> select dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL;

 

DBMS_METADATA.GET_DDL('TABLE','TEST')

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

 

  CREATE TABLE "HANGAN"."TEST"

   (    "AAA" VARCHAR2(50),

        "BBB" NUMBER(5,0) NOT NULL ENABLE,

         CONSTRAINT "AAA" PRIMARY KEY ("BBB")

  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

 

DBMS_METADATA.GET_DDL('TABLE','TEST')

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

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

-3.Introduction to an Oracle Instance

 这里有个图没贴上

Every running Oracle database is associated with an Oracle instance. When a

database is started on a database server (regardless of the type of computer), Oracle

allocates a memory area called the System Global Area (SGA) and starts one or

more Oracle processes. This combination of the SGA and the Oracle processes is

called an Oracle instance.

（SGA和一组后台进程组成一个ORACLE INSTANCE）

After starting an instance, Oracle associates the instance with the specified database.

This is called mounting the database. The database is then ready to be opened,

which makes it accessible to authorized users.

(例程启动后，ORACLE会把这个例程和一个确定数据库结合。这就叫mount。然后数据库就准备打开，使通过验证的用户使用)

Multiple instances can run concurrently on the same computer, each accessing its

own physical database. In clustered and massively parallel systems (MPS), Real

Application Clusters enables multiple instances to mount a single database.

（多个例程能够在同一台机子上运行，没一个都访问它自己的物理数据库。在基群和MPS，RAC能够在一台单独的数据库上运行多个例程。）

Instance and Database Startup

The three steps to starting an Oracle database and making it available for

systemwide use are:

1. Start an instance.

2. Mount the database.

3. Open the database.

How an Instance Is Started

When Oracle starts an instance, it reads the initialization parameter file to

determine the values of initialization parameters. Then, it allocates an SGA, which

is a shared area of memory used for database information, and creates background

processes. At this point, no database is associated with these memory structures and

processes.

（数据库启动例程时，它首先读取初始化参数文件确定初始值。然后为数据库配置SGA共享内存区域，并且创建后台进程。在这个时间点，没有数据库与内存体系和进程相连接。）

（注：昨天博客老是打不开，只能今天发了）