oracle常用操作

sqlplus / as sysdba
登录oracle

查询所有表空间名称
select tablespace_name from dba_tablespaces;


查看表空间的名称和状态
select tablespace_name,status from dba_tablespaces;


查询当前表空间属性
select * from dba_tablespaces where tablespace_name='mtgyd'

删除表空间与表空间文件（注意：如果在创建表空间的时候带有双引号，则删除的时候也要带上）
DROP TABLESPACE REPORTPRINT_DB INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE HUANCHONG_DB INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE GuoHuoHang INCLUDING CONTENTS AND DATAFILES;
删除用户
drop user GuoHuoHang cascade;
drop user bstestcm cascade;
drop user hcText cascade;


查询所有用户
select username from dba_users;

连接
connect /as sysdba
sqlplus / as sysdba

创建表空间
create tablespace HUANCHONG_DB datafile 'D:\app\Administrator\oradata\orcl\HUANCHONG_DB.dbf' size 100m autoextend on next 32m EXTENT management local;

create tablespace REPORTPRINT_DB datafile 'D:\app\Administrator\oradata\orcl\REPORTPRINT_DB.dbf' size 100m autoextend on next 32m EXTENT management local;

create tablespace GuoHuoHang datafile 'F:\app\Administrator\oradata\orcl\GuoHuoHang.dbf' size 100m autoextend on next 32m EXTENT management local;

create tablespace busnisschool2011 datafile 'E:\app\Administrator\oradata\orcl\busnisschool2011.dbf' size 100m autoextend on next 50m EXTENT management local;

create tablespace jboss datafile 'E:\app\Administrator\oradata\orcl\jboss.dbf' size 100m autoextend on next 32m EXTENT management local;

create tablespace nxjw datafile '\opt\oradata\orcl\nxjw.dbf' size 100m autoextend on next 32m EXTENT management local;

create tablespace ba datafile 'D:\Program Files\oracle\DATE\basystem.dbf' size 100m autoextend on next 32m EXTENT management local;

create tablespace new_stu datafile 'D:\new_stu.dbf' size 100m autoextend on next 32m EXTENT management local;

创建用户并指定对应表空间
create user bstestcm identified by bstestcm default tablespace REPORTPRINT_DB;
create user hcText identified by hcText default tablespace REPORTPRINT_DB;
create user newpscms identified by ps default tablespace newpscms;
create user GuoHuoHang identified by GuoHuoHang default tablespace GuoHuoHang;


给用户授权dba权限
grant dba to GuoHuoHang;
grant dba to hcText
grant dba to newpscms;

导出exp 导入imp

imp GuoHuoHang/GuoHuoHang@ORCL file=I:/GuoHuoHang20130409.dmp full=y
imp bstestcm/bstestcm@ORCL file=D:/bstest20120924.dmp full=y
imp hcText/hcText@ORCL file=D:/hcText20120924.dmp full=y;
imp KM/KM@ORCL file=/opt/ky.dmp full=y
imp ba/ba@ORCL file=/opt/ba20111025.dmp full=y
imp nxjw/nxjw@orcl file=/opt/db08-10-10.dmp full=y
imp ba/ba@orcl file=G:\ba20111209.dmp full=y
imp newpscms/ps@orcl file=G:\expdat.dmp full=y


select username from dba_users;

select username,sid,serial# from v$session

alter system kill session '115,348';
USERNAME SID SERIAL#

select saddr,sid,serial#,paddr,username,status from v$session where username is not null


查询oracle的字符集 select userenv('language') from dual;
修改linux的用户的字符集命令
（临时） export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
(永久) 在 vim /etc/profile 中
加入 export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"


(1)查看用户的连接状况

select username,sid,serial# from v$session

(2)找到要删除用户的sid,和serial，并删除

alter system kill session'532,4562'
(3)删除用户

drop user username cascade