数据库导
出单表
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用户