跳转至

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



回到页面顶部