Linux ORACLE相关

oracle启动关闭

http://www.cnblogs.com/CandiceW/p/6117408.html

 

oracle开机自启动

http://www.cnblogs.com/mophee/archive/2013/06/03/3115805.html

 

 

Oracle服务启动

 

#su oracle      --password:oracle

#sqlplus /nolog

#connect / as sysdba

#startup

 

Oracle服务停止

 

#shutdown

 

监听

 

启动监听

#lsnrctl start

停止监听 

#lsnrctl stop 

查看监听状态 

#lsnrctl status

 

用户管理

 

创建用户

create user "username" identified by "userpassword" ;

 

删除用户

drop user “username” cascade;

注：cascade 参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数

 

授权

grant connect,resource,dba to "username" ;

 

查看当前用户的角色

select * from user_role_privs;

select * from session_privs; 

 

查看当前用户的系统权限和表级权限

select * from user_sys_privs;

select * from user_tab_privs;

 

查询用户表

select username from dba_users; 

 

修改用户口令

alter user "username" identified by "password"; 

 

显示当前用户

show user;

 

查看用户当前连接数

select count(*) from sys.v_$session；

 

 

表及表空间

 

--创建临时表空间 

create temporary tablespace mydb_temp tempfile '/opt/app/oracle/oradata/orcl/mydb/_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;

 

--创建数据表空间

create tablespace mydb logging datafile '/opt/app/oracle/oradata/orcl/mydb/mydb_data01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;

 

--创建用户并指定表空间

create user myuser identified by 123456 default tablespace mydb temporary tablespace mydb_temp;

 

 

删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

 

修改表空间大小

alter database datafile '/path/NADDate05.dbf' resize 100M;

 

查询数据库文件

select * from dba_data_files; 

 

查询当前存在的表空间

select * from v$tablespace;

 

表空间情况

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

查询表空间剩余空间

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

查看表结构

desc table