|
登录本地系统用户
su - oracle #切换到oracle账号
sqlplus / as sysdba 登陆sys帐户
SQL> select * from V_$PWFILE_USERS; #查看哪些用户拥有SYSDBA、SYSOPER权限:
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SQL> show user; #查看当前登录用户
USER is "SYS"
SQL> select username,default_tablespace from user_users; #查看当前用户的默认表空间
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZG_MOBILE T_MOBILE
SQL> select * from user_role_privs; #查看当前用户的角色
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
ZG_MOBILE CONNECT NO YES NO
ZG_MOBILE RESOURCE NO YES NO
SQL> select * from user_sys_privs; #查看当前用户的系统权限
USERNAME PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ZG_MOBILE UNLIMITED TABLESPACE NO
SQL> select * from user_tab_privs; #查看当前用户的表权限
GRANTEE OWNER TABLE_NAME GRANTOR
SQL>select * from user_tables; #查看用户下所有的表
SQL> select TABLE_NAME from user_tables;
TABLE_NAME
------------------------------
MB_APP_LOG
MB_BSS_APP_ROLE
MB_BSS_APP_USER
MB_BSS_APP_USER_ROLE
MB_BSS_ELECTRONIC_FENCE
MB_BSS_SMS_INFO
MB_BSS_USER_TERMINAL_LOCATION
MB_SYS_APP_LOG
MB_USER_RESOURCE_MAP
MB_USER_WARING
MB_VERIYFCODE_SMSSEND
查看当前的所有数据库: select * from v$database;
SQL> select name from v$database;
NAME
---------
MOBILE
查看权限
select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限
SQL> select username,default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
CAMPUS T_MOBILE
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
CAMPUS CONNECT NO YES NO
CAMPUS DBA NO YES NO
CAMPUS RESOURCE NO YES NO
SQL> select * from session_privs;
create user cd_mobile identified by cd_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;
grant connect,resource to cd_mobile;
grant UNLIMITED TABLESPACE to cd_mobile;
增加数据库用户
SQL> create user cd_mobile identified by cd_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;
User created.
用户授权:
SQL> grant connect,resource to cd_mobile;
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to cd_mobile; #用户使用表空间的权限
Grant succeeded.
create user lz_mobile identified by lz_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;
grant connect,resource to lz_mobile;
grant UNLIMITED TABLESPACE to lz_mobile;
create user zg_mobile identified by zg_mobile default tablespace t_mobile quota unlimited on t_mobile temporary tablespace t_temp;
grant connect,resource to zg_mobile;
grant UNLIMITED TABLESPACE to zg_mobile;
更改数据库用户的密码：(将sys与system的密码改为test.)
alter user sys indentified by test;
alter user system indentified by test;
SQL> select username from dba_users; #查看数据库中的用户，sys用户登录sqlplus才能使用
USERNAME
------------------------------
GA_MOBILE
YB_MOBILE
BZ_MOBILE
CAMPUS
LZ_MOBILE
XC_BIGDATA
GT_MOBILE
ZG_MOBILE
PZH_MOBILE
CD_MOBILE
NJ_MOBILE
select username, default_tablespace,TEMPORARY_TABLESPACE from dba_users; #查看用户的表空间
select * from dba_tablespaces； #查看表空间属性。
创建表空间
/*分为四步 */ /*第1步：创建临时表空间 第一次必须创建，后面可以不创建*/ create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; #extent management local参数系统默认，可不写。 /*第2步：创建数据表空间 */ create tablespace user_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 500m autoextend on next 500m maxsize 20480m extent management local;
#LOGGING实际上是对象的一个属性，用来表示在创建对象时是否记录REDO日志，包括在做DML时是否记录REDO日志。默认会添加，可不写。 create user username identified by password default tablespace user_data temporary tablespace user_temp; #数据库创建以后，系统中只会存在一个名为DEFAULT的默认PROFILE，在用户创建之后，如果不做特殊指定，每个用户的PROFILE都会默认的使用个默认的PROFILE。 /*第4步：给用户授予权限 */ grant connect,resource,dba to username;
删除表空间
drop tablespace EPOINTBID_PSP including contents and datafiles;
drop user EPOINTBID_PSP cascade;
导出
expdp EPOINTBID_TP7_GY/epointgy123@orcl directory=dir_epoint dumpfile=EPOINTBID_TP7_GY08.dmp schemas=EPOINTBID_ASP logfile=EPOINTBID_ASP.log
impdp EPOINTFRAME/EPOINTFRAME123@localhost:1521 dumpfile=EPOINTBID_TP7_GY12.DMP remap_schema=EPOINTBID_TP7_GY:EPOINTFRAME remap_tablespace=EPOINTBID_TP7_GY:EPOINTFRAME (责任编辑：liangzh) |