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
相关文档:
今天开发过程中遇到了一个很麻烦的外连接的问题,到最后都没有找到很好的解决方法,最后只能用union all 实现了,虽然性能不比外连接,但至少拓展了外连接的局限性。 首先给出我测试用的三个表和数据(左右外连接道理是一样的,我只总结了左连接): KC21表: crea ......
Oracle 内存结构如下图:
1:实例(Instance)
在一个中,每一个运行的Oracle数据库都与一个数据库实例相联系,实例是我们
访问数据库的手段。
实例在操作系统中用ORACLE_SID来标识,在Oracle中用参数INSTANCE_NAME来标识,
它 ......
每隔几秒钟listener日志中就出现一次该错误的记录,即使是在没有人使用的情况下也是如此,不过并没有发现客户端机器不能正常连接数据库的情况。
Oracle关于TNS-12502错误的解释:
Error: ORA-12502 / TNS-12502
Text: TNS:listener received no CONNECT_DATA from client
Cause: No CONNECT_DATA was ......
先看下面的例子:oracle中两个系统表.emp,dept.
example:
1:not exists(not in)
not exists:
这条语句返回select * from scott.dept d where e.deptno=d.deptno and d.deptno=10条件满足的结果集.也就是说,
返回的结果集中不存在d.deptno=10结果集的记录,即emp表中没有dept表中d.deptno=10的记录.
SQL& ......
How to land your dream Oracle DBA job
By Don Burleson, Contributor
14 Jan 2010
If you look at the Help Wanted ads, some Oracle DBA jobs look very appealing, with salaries that rival those of engineers and other professionals. Some have starting salaries as high as $85,000, with senior Orac ......