第1个回答 2016-12-10
1.cmd命令栏连接oracle
sqlplus system/manager as sysdba
2.查看所有用户
select * from all_users;
3.修改用户密码
alter user 用户名 identified by 密码
4.创建表空间
create tablespace 表间名 datafile '数据文件名' size 表空间大小;
5.创建新用户并分配表空间
create user 用户名identified by 密码 default tablespace 表空间
6.为新建用户授权
grant connect,resource,dba to 用户名
7.查看系统数据库文件路径
select name from v$datafile;
8.查看表空间
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;
9.删除ORACLE数据库用户及表空间
删除表空间:
可以先将其offline
alter tablespace xx offline;
将磁盘上的数据文件一同删除
drop tablespace xxx including contents and datafiles;
删除用户:
drop user xxx;
如果用户的schema中有objects ,需要加cascade参数,即drop user xxx cascade;
10.创建ORACLE表空间及用户
/*分为四步 */
/*第1步:创建临时表空间 */
create temporary tablespace travel_temp
tempfile 'D:\oradata\travel_temp.dbf'
size 50m
autoextend on
next 50m maxsize 1024m
extent management local;
/*第2步:创建数据表空间 */
create tablespace travel_data
logging
datafile 'D:\oradata\travel_data.dbf'
size 50m
autoextend on
next 50m maxsize 1024m
extent management local;
/*第3步:创建用户并指定表空间 */
create user travel identified by travel
default tablespace travel_data
temporary tablespace travel_temp;
/*第4步:给用户授予权限 */
grant connect,resource,dba to travel;
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO travel;
11.ORACLE登陆不区分用户名,密码大小写
alter system set sec_case_sensitive_logon=false;
show parameter sec_case_sensitive_logon;