以管理员身份登录:
- 1.创建(新)用户:
create user username identified by password; username:新用户的用户名 password: 新用户的密码 也可以不创建新用户,而仍然用以前的用户,如:继续利用scott用户
- 2.创建表空间:
create tablespace tablespacename datafile 'd:\表空间文件名.dbf' size xxxm; tablespacename:表空间的名字 d:\表空间文件名.dbf':表空间的存储位置 xxx表空间的大小,m单位为兆(M) 修改表空间大小:alter database datafile 'd:\表空间文件名.dbf' resize 1024m; 设置自增长:ALTER DATABASE DATAFILE 'd:\表空间文件名.dbf' AUTOEXTEND ON NEXT 200M 删除表空间:DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
- 3.将空间分配给用户:
alter user username default tablespace tablespacename; 将名字为tablespacename的表空间分配给username
- 4.给用户授权:
GRANT CONNECT, RESOURCE, --DBA, --unlimited tablespace, CREATE SESSION, CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY VIEW , CREATE ANY INDEX, CREATE ANY PROCEDURE, CREATE ANY DIRECTORY, ALTER SESSION, ALTER ANY SEQUENCE, ALTER ANY TABLE, --ALTER ANY VIEW , --不能修改视图 ALTER ANY INDEX, ALTER ANY PROCEDURE, --ALTER ANY DIRECTORY, --不能修改目录 --DROP SESSION, --不能删除Session DROP ANY SEQUENCE, DROP ANY TABLE, DROP ANY VIEW , DROP ANY INDEX, DROP ANY PROCEDURE, DROP ANY DIRECTORY, SELECT ANY TABLE, SELECT ANY DICTIONARY, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, DEBUG ANY PROCEDURE, DEBUG CONNECT SESSION, exp_full_database, imp_full_database TO username;
- 5.使用新创建的用户登录-创建表
-- 创建表 create table USER_LOGIN ( ID VARCHAR2(100) not null, LOGIN_NAME VARCHAR2(100), LOGIN_PWD VARCHAR2(100), NICK_NAME VARCHAR2(100), USER_ID VARCHAR2(100), USER_NAME VARCHAR2(100), STATE VARCHAR2(100), LOCKED VARCHAR2(1), LAST_LOGIN_TIME DATE, CREATE_TIME DATE default sysdate ) tablespace SSM_TS pctfree 10 initrans 1 maxtrans 255 storage ( initial 16 minextents 1 maxextents unlimited ); -- 添加注释到表 comment on table USER_LOGIN is '用户登录表'; -- Add comments to the columns comment on column USER_LOGIN.ID is '主键'; comment on column USER_LOGIN.LOGIN_NAME is '登录名'; comment on column USER_LOGIN.LOGIN_PWD is '登录密码'; comment on column USER_LOGIN.NICK_NAME is '昵称'; comment on column USER_LOGIN.USER_ID is '用户ID'; comment on column USER_LOGIN.USER_NAME is '用户名'; comment on column USER_LOGIN.STATE is '状态'; comment on column USER_LOGIN.LOCKED is '是否锁定(0:锁定;1:未锁)'; comment on column USER_LOGIN.LAST_LOGIN_TIME is '上次登录时间'; comment on column USER_LOGIN.CREATE_TIME is '创建时间'; -- 给表添加主键 alter table USER_LOGIN add constraint PK_USER_LOGIN primary key (ID) using index tablespace SSM_TS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );
- 6.导入导出
导入:imp newusername/pwd@ORCL file=e:\data.dmp fromuser=oldusername touser=newusername full=y ignore=y 导出: exp username/pwd@ORCL file=e:\data.dmp或 exp username/pwd@127.0.0.1:1521/ORCL file=e:\data.dmp