Oracle DBMS_METADATA Ó÷¨Àý×Ó
rom£ºhttp://www.psoug.org/reference/dbms_metadata.html
General Information
Source
{ORACLE_HOME}/rdbms/admin/dbmsmeta.sql
First Available
9.0.1
¼¸¸ö³£Óùý³Ì»òº¯Êý£º
GET_DDL
Fetch DDL for objects
dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
Table
CREATE TABLE test
PCTFREE 0
TABLESPACE uwdata AS
SELECT table_name, tablespace_name
from user_tables;
SET LONG 10000
SELECT dbms_metadata.get_ddl('TABLE', 'TEST')
from dual;
View
CREATE OR REPLACE VIEW my_tables AS
select table_name, tablespace_name
from user_tables;
SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES')
from dual;
Function
CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS
BEGIN
RETURN user;
END whoami;
/
SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI')
from dual;
Tablespace
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
from dual;
GET_DEPENDENT_DDL
Fetch DDL for dependent objects (audits, object grants)
dbms_metadata.get_dependent_ddl(
object_type IN VARCHAR2,
base_object_name IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL',
object_count IN NUMBER DEFAULT 10000)
RETURN CLOB;
GRANT select ON servers TO hr;
GRANT select ON servers TO scott;
set long 100000
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','SERV
Ïà¹ØÎĵµ£º
Oracle ÄÚ´æ½á¹¹ÈçÏÂͼ£º
1£ºÊµÀý£¨Instance£©
ÔÚÒ»¸öÖУ¬Ã¿Ò»¸öÔËÐеÄOracleÊý¾Ý¿â¶¼ÓëÒ»¸öÊý¾Ý¿âʵÀýÏàÁªÏµ£¬ÊµÀýÊÇÎÒÃÇ
·ÃÎÊÊý¾Ý¿âµÄÊֶΡ£
ʵÀýÔÚ²Ù×÷ϵͳÖÐÓÃORACLE_SIDÀ´±êʶ£¬ÔÚOracleÖÐÓòÎÊýINSTANCE_NAMEÀ´±êʶ£¬
Ëü ......
OracleÊý¾Ý¿âµÄÊý¾Ý¿éDB_BLOCK_SIZE´óСȷ¶¨Êý¾Ý¿âµÄ×îСÊý¾Ý¿éµÄ´óС£¬ÔÚ´´½¨±í¿Õ¼äʱ¿ÉÒÔÈç¹û²»ÏëʹÓÃĬÈϵÄÊý¾Ý¿é´óС£¬¿ÉÒÔͨ¹ýÉèÖÃ×Ô¼ºµÄÊý¾Ý¿é´óС¡£
¾ßÌåʵÀýÈçÏ£º
create tablespace test_16k
bloc ......
ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ãë
¡¡¡¡sysdate+(5/24/60/60)
¡¡¡¡ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5·ÖÖÓ
¡¡¡¡sysdate+5/24/60
¡¡¡¡ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Сʱ
¡¡¡¡sysdate+5/24
¡¡¡¡ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ìì
¡¡¡¡sysdate+5
¡¡¡¡ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5ÔÂ
¡¡¡¡add_months(sysdate,-5)
¡¡¡¡ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Äê
¡¡¡¡add_months ......
Ò»¸öʵÀý¿ÉÒÔÓжà¸öºǫ́½ø³Ì,µ«ÊÇ£¬²¢²»ÊÇÿһ¸öºǫ́½ø³Ì¶¼»á³ö³ö£¬Í¨¹ýÊÓͼv$bgprocess¿ÉÒԲ鿴ºǫ́½ø³ÌÐÅÏ¢¡£
Ò»°ãÎÒÃÇÊÇͨ¹ýÒÔÏÂsql²é¿´ºǫ́±ØÐëµÄºǫ́½ø³Ì.
1.²é¿´ºǫ́½ø³Ì
select paddr,name,description
from v$bgprocess
order by paddr desc
£»
2.Õâ¸öÊÓͼÖÐpaddr<>'00'µÄÐж¼ÊÇϵͳÉÏÅäÖúÍÔËÐеĽø³ ......
1¡¢Êý¾Ý¿âµÄÂß¼½á¹¹ºÍÎïÀí½á¹¹£º
Oracle logically divides the database into a smaller units to manage ,store,and retreive
data efficently.
Tablespace\Blocks\Extents\segment\
ÓÐËÄÖÖsegment:
data segment¡¢Index segment¡¢Temporay segment¡¢Rollback Segment
temporary segment:
Ar ......