oracle数据库语句积累二

Oracle登陆

 

sys 用户sqlplus / as sysdba 

添加用户 create user  username identified by password;

修改用户 alert  user  username identified by password;

 

添加登陆权限 grant create ssession to username;

用户登陆 sqlplus username/password;

建表权限 grant create table to username;

撤销权限 revoke create table to username;

表空间权限 grant unlimited tablespace to username;

查看当前用户的系统权限 select * from user_sys_privs;

插入数据 要提交 commit; 

授予对象查询权限 grant select on tablename to username; 谁拥有谁授权

给用户 表的所有权限  grant all on tablename to username; (all与public的区别)

给某表的所有权限     grant create table to public ; 

设置行宽度 set linesize 400; 

控制权限到列 grant update(rowname) on tablaname to username;

查看列权限  select * from user_col_privs;

授予权限并能分配权限 grant alert any table to username with admin option;

                               grant select on tablename to username with grant option;

 

创建角色  create role  rolename;

授予角色权限 grant create session to rolename;

授予角色给用户 grant rolename to username;

删除角色 drop role rolename;

 

检查数据库连通

TNSPING

 

lsnrctl start

启动数据库实例

oradim  -starup  -sid orcl

 

查看SID

SQL> SELECT * FROM V$INSTANCE;

SQL> SELECT * FROM V$DATABASE;

 

SQL> / 执行上个操作命令

SQL> EDIT读取命令缓存区

 

查看语言配置

SQL> show parameters nls

SQL> select * from V$NLS_PARAMETERS

 

查看启动参数文件

SQL> show parameters spfile;

 

查看数据块配置大小

SQL> show parameters db;

 

查看ORACLE版本

SQL> SELECT * FROM V$VERSION

显示所有组建版本

SQL> select * from product_component_version;

 

配置输出日志

SQL> spool c:\testora.log

SQL> spool off

 

切换归档日志

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

查看数据库对象结构

SQL> desc v$dbfile

 

查看Datafile

SQL>SELECT * FROM V$DBFILE;

SQL>SELECT * FROM V$DATAFILE;

 

查看所有的用户

SQL> SELECT USERNAME FROM DBA_USERS

 

查看控制文件

SQL> select * from v$controlfile;

 

设置输出的行宽

SQL> set linesize 20000

 

查看REDOLOG文件

SQL> SELECT * FROM V$LOGFILE;

 

查看表空间

SQL> SELECT * FROM V$tablespace;

 

手动删除归档日志后，同步CATALOG

SQL>CROSSCHECK ARCHIVELOG ALL

SQL>CHANGE ARCHIVELOG ALL CROSSCHECK