常用SQL

sys账户以sysdba角色登录数据库，这种登陆方式启用的是OS验证方式，因此不用输入用户名、密码
SQL>sqlplus / as sysdba;       or SQL>sqlplus /nolog;   SQL>conn / as sysdba;

查看当前登录用户
SQL>select user from dual;

创建用户并赋予权限
SQL>create user usrname identified by passwd default tablespace tablespacename;

给用户赋予权限
SQL>grant connect, resource, create session, create view, create tablecreate procedure to dbuser;

撤销某个用户的某项权利
SQL>revoke [select] on tablename from usrname;

删除用户
SQL>drop user usrname cascade;

更改用户密码
SQL>alter user sys identified by new_password;

查看当前数据库实例
SQL>select name from v$database; 

查看所有数据库实例名
SQL>select instance_name from v$instance;
SQL>select * from v$thread;

锁定账户
SQL>conn system/orcldb as sysdba; 以DBA身份登录
SQL>alter user scott account lock;

查看数据库系统字符集设置
SQL>select userenv('language') from dual;

查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;

查看当前用户的角色
SQL>select * from user_role_privs;

查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

查看用户下所有的表
SQL>select * from user_tables;

显示当前会话所具有的权限
SQL>select * from session_privs;

显示指定用户所具有的系统权限
SQL>select * from dba_sys_privs where grantee='GAME';

查看用户下所有的表
SQL>select * from user_tables;

查看序列号，last_number是当前值
SQL>select * from user_sequences;

查看视图的名称
SQL>select view_name from user_views;