oracle 11g 导出表，表结构，导出入库

数据库导

出单表

exp uername/password@ip:port/ORCL file=d:\lw.dmp tables=(cims_information_change) ;

oracle导出的表结构SQL语句整理如下：

SELECT 

--t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",

     ROWNUM ,

       t1.Column_Name                                AS "字段名称",

       t1.DATA_TYPE             AS "数据类型",

             t1.DATA_LENGTH    AS "长度",

       t1.NullAble                                   AS "是否为空",

       t2.Comments                                   AS "字段说明",

       t1.Data_Default "默认值"

       --t4.created                                  AS "建表时间"

               --t3.comments                                 AS "表说明",

  FROM cols t1

  LEFT JOIN user_col_comments t2 

         ON t1.Table_name = t2.Table_name

        AND t1.Column_Name = t2.Column_Name

  LEFT JOIN user_tab_comments t3 

         ON t1.Table_name = t3.Table_name

  LEFT JOIN user_objects t4 

         ON t1.table_name = t4.OBJECT_NAME

  WHERE NOT EXISTS (SELECT t4.Object_Name

          FROM User_objects t4

         WHERE t4.Object_Type = 'TABLE'

           AND t4.Temporary = 'Y'

           AND t4.Object_Name = t1.Table_Name)

                     and t1.TABLE_NAME='你要查询的表名'

  ORDER BY t1.Table_Name, t1.Column_ID; 

drop user username cascade;

create temporary tablespace TEMP tempfile '/data/tablespace/TEMP.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

create tablespace username logging datafile '/data/tablespace/username.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

create user username identified by username default tablespace username temporary tablespace TEMP;

grant connect,resource,dba to username ;

imp username/password@IP:1521/orcl file=E:\dmp\example.dmp

crontab -u root /var/spool/cron/root

chown -R oracle:oinstall /data/oracle/***（表空间所在目录）　　#设置目录所有者为oinstall用户组的oracle用户