Oracle常用命令

1.查看所有用户：

select * from dba_users where username='pjptdb';   
select username from dba_users  ;
select * from all_users;   
select * from user_users;

2.采用oracle创建用户并授予dba权限

1）创建用户： create user pjptdb identified by pjptdb;

2）授予dba权限  grant connect,resource,dba to pjptdb ;

3.sqlplus远程连接

sqlplus dbuser/dbpassword@ip/sid

4.只赋予用户select inset表（而不是表空间）权限
步骤：

SQL> create user u1 identified by u1;
User created.
SQL> grant create session to u1;
Grant succeeded.
SQL> grant insert, select on table to u1;

5.改变用户默认使用的表空间

alter user username  default tablespace userspace;

6.删除非空表空间

drop tablespace tablespace_name including contents and datafiles

7.创建表空间并赋予新的用户

create bigfile tablespace PJPTDBTS
datafile '/home/oracle/oradata/pjptdb/pjptdbts.dbf' size 500M autoextend on;

CREATE USER pjptusr IDENTIFIED BY pjptusr
alter user pjptusr  default tablespace PJPTDBTS;
grant create table to pjptusr;
grant resource,connect to pjptusr