oracle创建用户和表空间_v7hqs-优快云博客

本文链接：https://blog.youkuaiyun.com/djs00717/article/details/3612585

本文介绍如何在Oracle数据库中创建用户并分配权限，同时演示了创建表空间的过程。通过SQL命令，创建了一个名为V7HQS的用户，并授予其多种角色与系统权限。此外，还创建了一个名为TSDIC的表空间。

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

创建用户

create user V7HQS
identified by "password"
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant aq_administrator_role to V7HQS with admin option;
grant connect to V7HQS with admin option;
grant dba to V7HQS with admin option;
grant resource to V7HQS with admin option;
-- Grant/Revoke system privileges
grant create any table to V7HQS with admin option;
grant unlimited tablespace to V7HQS with admin option;

connect v7hqs/rezin@orcl
/

创建表空间

CREATE TABLESPACE TSDIC
DATAFILE 'e:/TSDIC.dbf' size 200M
EXTENT MANAGEMENT local;

create tablespace oaspaces01 datafile 'E:/oracle_tablespaces/oaspaces01.dbf' size 500M autoextend on next 5M maxsize 3000M;

CREATE TABLESPACE oaTables01
DATAFILE 'E:\app\administrator\oradata\ORCL\oaTables01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE oaIndex01
DATAFILE 'E:\app\administrator\oradata\ORCL\oaIndex01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

create user ECOLOGY
identified by "FwOA.2024!1231111"
default tablespace oaTables01
temporary tablespace TEMP
profile DEFAULT;

grant connect to ECOLOGY with admin option;
grant dba to ECOLOGY with admin option;
grant resource to ECOLOGY with admin option;
grant create any table to ECOLOGY with admin option;
grant unlimited tablespace to ECOLOGY with admin option;
-- Grant/Revoke system privileges

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

CREATE DIRECTORY dmpfiles AS 'd:\dmpfiles';
GRANT READ, WRITE ON DIRECTORY dmpfiles TO ECOLOGY;

impdp ECOLOGY/password@database DIRECTORY=dmpfiles DUMPFILE=file_name.dmp LOGFILE=import_log.log

expdp username/password directory=your_directory dumpfile=your_dump_file.dmp logfile=export.log NLS_LANG=SIMPLIFIED_CHINESE_CHINA.AL32UTF8

impdp ECOLOGY/FwOA.2024!123@orcl DIRECTORY=dmpfiles DUMPFILE=ECOLOGY_20241103_235520.DMP tables=('hrmdepartment') content=metadata_only logfile=metadata_hrmdepartment.log