表结构获取方法（包括处理输出异常换行问题）

欢心之宇

已于 2024-02-07 15:47:48 修改

阅读量237

点赞数 5

文章标签： ffmpeg

于 2024-02-07 15:45:30 首次发布

本文链接：https://blog.csdn.net/weixin_60275015/article/details/136070369

版权

set pages 0;
set long 99999;
set feedback off;
set echo off;
set heading off;
set termout off;
set linesize 500;

spool get_table_strc.sql

select 'select dbms_metadata.get_ddl(''TABLE'',''' || table_name || ''',''' || owner || ''') from dual;'
from dba_tables where owner = '<owner_name>' order by table_name;

spool off

-- 解决“使用 dbms_metadata.get_ddl 查询表结构时经常出现异常的换行，导致语句无法直接执行”的方法：

select cast(dbms_metadata.get_ddl('TABLE','MYTABLE1','MYUSER') as varchar2(4000)) from dual;

示例：

SQL> set pages 0;
SQL> set long 99999;
SQL> set feedback off;
SQL> set echo off;
SQL> set heading off;
SQL> set termout off;
SQL> set linesize 500;
SQL> select dbms_metadata.get_ddl('TABLE','MYTABLE1','MYUSER') from dual;

CREATE TABLE "MYUSER"."MYTABLE1"
( "ID" NUMBER(*,0),
"ADDRS" VARCHAR2(20)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55
NOCOMPRESS LOGGING
TABLESPACE "USERS"

SQL> select cast(dbms_metadata.get_ddl('TABLE','MYTABLE1','MYUSER') as varchar2(4000)) from dual;

CREATE TABLE "MYUSER"."MYTABLE1"
( "ID" NUMBER(*,0),
"ADDRS" VARCHAR2(20)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"

SQL>

--关闭创建表的PCTFREE、NOCOMPRESS等属性
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);

示例：

CREATE TABLE "MYUSER"."MYTABLE1"
( "ID" NUMBER(*,0),
"ADDRS" VARCHAR2(20)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55
NOCOMPRESS LOGGING
TABLESPACE "USERS"

SQL> EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
SQL> select dbms_metadata.get_ddl('TABLE','MYTABLE1','MYUSER') from dual;

CREATE TABLE "MYUSER"."MYTABLE1"
( "ID" NUMBER(*,0),
"ADDRS" VARCHAR2(20)
)

SQL>