Oracle常用命令

Linux 查看数据库当前用户
root下 cat /etc/passwd


oracle查看用户状态
select username,account_status from dba_users;


查看所有角色： select * from dba_roles;


锁定用户
alter user XXX account lock;


Linux下新建用户
useradd -d /home/XXX -s /usr/bin/ksh -m XXX
passwd XXX


用户创建
useradd -d /home/XXX -g users -s /bin/bash -m XXX -G users 
passwd XXX


删除用户
userdel -r XXX


Linux查看进程
ps -e|grep ***
kill -9 ***(PID)


linux查看版本信息
lsb_release -a


Linux oarcle数据库启动和关闭
http://www.cnblogs.com/ricksun/articles/1750729.html
退出sqlplus exit


赋予debug权限给用户
grant debug any procedure, debug connect session to prodb;


oracle查询用户
select username,password from dba_users;

oracle删除用户

drop user XXX cascade;

如果遇到报错
oracle cannot drop a user that is currently connected

则执行以下命令

以sysdba登陆
shutdown immediate;
startup restrict;
drop user *** cascade;
alter system disable restricted session;

oracle各种模式切换

http://www.2cto.com/database/201204/128559.html


Oracle监听
lsnrctl start 即可启动


如果出现以sysdba用户连接无法连接到数据库的现象 
检查环境变量ORACLE_HOME,ORACLE_SID
 
sqlplus下的删除符号
ctrl+backspace


sqlplus远程连接
sqlplus 用户名/密码@10.10.10.10：1521/orcl


查询表空间和对应数据文件的关系
select t1.name, t2.name  from v$tablespace t1, v$datafile t2 where  t1.ts# = t2.ts#;

删除表空间

  drop tablespace **** including contents and datafiles;

取数据重复插入表

insert into table_A(X,X,X,X) select X,X,X,X from table_B where XXX

table_A和table_B可以是一张表，数据当然要经过更新之后再插入 

为用户赋debug权限

GRANT debug any procedure, debug connect session TO USER XXX;

查看不同用户的连接数

select username,count(username) from v$session where username is not null group by username;

查看执行日志

select t."SQL_TEXT",t."SQL_FULLTEXT",t."LAST_ACTIVE_TIME" from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('XXX') and t."SQL_TEXT" like '%XXX%' and t.LAST_ACTIVE_TIME > to_date('201707130623', 'yyyymmddhhmi') order by t.LAST_ACTIVE_TIME;