oracle ，删除用户、创建表空间、导入数据

本文详细介绍了如何在Oracle数据库环境中创建小文件表空间，并为用户分配了广泛的权限，包括对表、索引、视图等的创建、修改、删除等操作，以及连接权限和资源权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE SMALLFILE TABLESPACE "CNAGBASE" DATAFILE 'D:\app\dfhc\service\product\11.2.0\dbhome_1\oradata\CNAGBASE' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "CNAGBUSINESS" DATAFILE 'D:\app\dfhc\service\product\11.2.0\dbhome_1\oradata\CNAGBUSINESS' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "CNAGINDEX" DATAFILE 'D:\app\dfhc\service\product\11.2.0\dbhome_1\oradata\CNAGINDEX' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER CNAG PROFILE DEFAULT IDENTIFIED BY "123456" DEFAULT TABLESPACE CNAGBASE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT ALTER ANY INDEX TO CNAG;
GRANT ALTER ANY TABLE TO CNAG;
GRANT ALTER ANY TRIGGER TO CNAG;
GRANT ALTER ANY TYPE TO CNAG;
GRANT CREATE ANY INDEX TO CNAG;
GRANT CREATE ANY SYNONYM TO CNAG;
GRANT CREATE ANY TABLE TO CNAG;
GRANT CREATE ANY TRIGGER TO CNAG;
GRANT CREATE ANY TYPE TO CNAG;
GRANT CREATE ANY VIEW TO CNAG;
GRANT DELETE ANY TABLE TO CNAG;
GRANT DROP ANY INDEX TO CNAG;
GRANT DROP ANY SYNONYM TO CNAG;
GRANT DROP ANY TABLE TO CNAG;
GRANT DROP ANY TRIGGER TO CNAG;
GRANT DROP ANY TYPE TO CNAG;
GRANT DROP ANY VIEW TO CNAG;
GRANT SELECT ANY SEQUENCE TO CNAG;
GRANT SELECT ANY TABLE TO CNAG;
GRANT UPDATE ANY TABLE TO CNAG;
GRANT CONNECT TO CNAG;
GRANT RESOURCE TO CNAG;

导入方案：

impdp test/b32w22h32 directory=dump_dir dumpfile=cnag_db.dmp REMAP_SCHEMA=cnag:test

impdp cnag/123456 directory=dump_dir dumpfile=CNAG.DMP SCHEMAS = CNAG
drop user cnag cascade;//删除用户，cascade级联删除cnag的对象
grant read, write on directory dump_dir to cnag;//给cnag赋权限

REMAP_SCHEMA=cnag:test 表示：如果导出方案和导入方案不同时需要更改方案（用户）SCHEMAS = cnag