Oracle 常用Sql语句

2014-08-05 by dongnan

表相关

创建表，create table student( name varchar2(20), age number(3) );
插入表，insert into student values('william zong',30);
查询表，select * from student;
表结构，desc student;
删除表，drop table student;
查询所有表，select table_name from all_tables;
返回当前用户的所有表(1)，select table_name from user_tables;
返回当前用户的所有表(2)，select table_name from tabs;
带有where的查询语句，select table_name from all_tables where owner='dbuser';

数据库相关

当前用户

SQL> show user;

USER is "SYS"

用户系统权限

select * from user_sys_privs;

USERNAME               PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
TESTUSER               UNLIMITED TABLESPACE            NO

数据库实例名称

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
pntest

查看字符集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

查看数据文件

select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hpns/system01.dbf
/u01/app/oracle/oradata/hpns/sysaux01.dbf
/u01/app/oracle/oradata/hpns/undotbs01.dbf
/u01/app/oracle/oradata/hpns/users01.dbf
/u01/app/oracle/oradata/hpns/example01.dbf

查看日志文件

select member from v$logfile where type<>'STANDBY';

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hpns/redo03.log
/u01/app/oracle/oradata/hpns/redo02.log
/u01/app/oracle/oradata/hpns/redo01.log

查看控制文件

select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hpns/control01.ctl
/u01/app/oracle/flash_recovery_area/hpns/control02.ctl

状态

select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

数据库版本

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

归档日志格式

show parameter log_archive_format;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_format             string     %t_%s_%r.dbf