oracle常见命令大全,ORACLE常用命令大全

检查数据库连通

TNSPING

数据库侦听

lsnrctl

查看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

1、  查看数据库的名字和归档状态

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

--------- ------------

HB130000  ARCHIVELOG

2、  查看数据库的instance名字和状态

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

hb130000         OPEN

3、查看表空间名称、状态和管理方式

SQL> select tablespace_name,status,extent_management from dba_tablespaces;

TABLESPACE_NAME                STATUS    EXTENT_MAN

SYSTEM                         ONLINE    LOCAL

UNDOTBS1                       ONLINE    LOCAL

SYSAUX                         ONLINE    LOCAL

TEMP                           ONLINE    LOCAL

USERS                          ONLINE    LOCAL

GFB                            ONLINE    LOCAL

YSZX130000002004               ONLINE    LOCAL

RMAN                           ONLINE    LOCAL

8 rows selected.

4、查看控制文件的位置和名字

SQL> select name from v$controlfile;

NAME

/oracle/app/oracle/OraHome/dbs/hb130000/control01.ctl

/oradata/hb130000/control02.ctl

/oradata/hb130000/control03.ctl

5、查看日志文件的组名、成员数量、状态和大小

SQL> select group#,members,bytes,status from v$log;

GROUP#    MEMBERS      BYTES STATUS

1          2   52428800 INACTIVE

2          2   52428800 INACTIVE

3          2   52428800 CURRENT

6、查看日志文件的位置

SQL> select group#,member from v$logfile;

GROUP#

MEMBER

3

/oradata/hb130000/redo03.log

3

/oracle/app/oracle/OraHome/dbs/hb130000/redo03.rdo

2

/oradata/hb130000/redo02.log

GROUP#

MEMBER

2

/oracle/app/oracle/OraHome/dbs/hb130000/redo02.rdo

1

/oradata/hb130000/redo01.log

1

/oracle/app/oracle/OraHome/dbs/hb130000/redo01.rdo

6 rows selected.

7、查看数据文件的位置和大小

SQL> select name,bytes from v$datafile;

NAME

BYTES

/oradata/hb130000/system01.dbf

492830720

/oradata/hb130000/undotbs01.dbf

41943040

/oradata/hb130000/sysaux01.dbf

346030080

NAME

BYTES

/oradata/hb130000/users01.dbf

5242880

/oradata/hb130000/GFB.DBF

20971520

/oradata/hb130000/YSZX130000002004.DBF

176160768

NAME

BYTES

/oradata/hb130000/rman.dbf

104857600

7 rows selected.

8、查看用户拥有的对象和类型

SQL> select object_name,object_type from dba_objects where owner='SCOTT';

OBJECT_NAME

OBJECT_TYPE

DEPT

TABLE

PK_DEPT

INDEX

EMP

TABLE

PK_EMP

INDEX

BONUS

TABLE

SALGRADE

TABLE

6 rows selected.

9、查看临时文件的信息

SQL> select name,bytes from v$tempfile;

NAME

BYTES

/oradata/hb130000/temp01.dbf

20971520

10、查看归档日志信息

SQL> select name, FIRST_CHANGE#,next_change# from v$archived_log;

NAME

FIRST_CHANGE# NEXT_CHANGE#

/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_53_532853380.dbf

1669178      1707392

/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_54_532853380.dbf

1707392      1734524

/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_55_532853380.dbf

1734524      1772848

NAME

FIRST_CHANGE# NEXT_CHANGE#

/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_56_532853380.dbf

1772848      1798092

/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_57_532853380.dbf

1798092      1831478

/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_58_532853380.dbf

1831478      1865033

10、查看一个库中的所有用户，表，表空间等信息？

con  sys as sysdba

select * from all_users;

select * from all_tables;

select * from dba_tablespaces;

select TABLESPACE_NAME from dba_tablespaces;

11、查看oracle的版本信息

(1)用客户端连接到数据库，执行select * from v$instance

查看version项

(2)select * from product_component_version

(3)或查询V$VERSION查看组件级信息