Oracle Database Configuration and Structure Information Retrieval

QL> set line 130

SQL> select name, created, log_mode, open_mode from v $ database;

 

Name created LOG_MODE OPEN_MODE

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

MDSP 30-OCT-10 ARCHIVELOG READ WRITE

 

SQL> col versionfor a20

SQL> select host_name, instance_name, version from v $ instance;

 

HOST_NAME INSTANCE_NAME VERSION

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

Mdsps1 mdsp 11.2.0.1.0

 

SQL> select * from v $ version;

 

BANNER

Certificate ----------------------------------------------------------------------------------------------------------------------------------

Oracle Database 11 gEnterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version11.2.0.1.0-Production

 

SQL>/

 

BANNER

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

Oracle Database 11 gEnterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version11.2.0.1.0-Production

SQL> select * from v $ controlfile;

 

Status name IS_REC BLOCK_SIZE FILE_SIZE_BLKS

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

/Home/oracle/oradata/mdsp/control01.ctl NO 16384 606

/Home/oracle/recovery_area/mdsp/control02.c NO 16384 606

Tl

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

 

GROUP # MEMBERS BYTES STATUS ARCHIV

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

1 1 524288000 INACTIVE YES

2 1 524288000 CURRENT NO

3 1 524288000 INACTIVE YES

SQL> col member for a50

SQL> select * from v $ logfile;

 

GROUP # status type member IS_REC

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

3 ONLINE/home/oracle/oradata/mdsp/redo03.log NO

2 ONLINE/home/oracle/oradata/mdsp/redo02.log NO

1 ONLINE/home/oracle/oradata/mdsp/redo01.log NO

SQL> archive loglist;

Database logmode Archive Mode

Automaticarchival Enabled

Archivedestination/home/oracle/archivelog

Oldest online logsequence 30

Next log sequenceto archive 32

Current logsequence 32

SQL> coltablespace_name for a15

SQL> selecttablespace_name, block_size, status, contents, logging from dba_tablespaces;

 

TABLESPACE_NAMEBLOCK_SIZE STATUS CONTENTS LOGGING

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

SYSTEM 8192 ONLINE PERMANENT LOGGING

SYSAUX 8192 ONLINE PERMANENT LOGGING

UNDOTBS1 8192 ONLINE UNDO LOGGING

TEMP 8192 ONLINE TEMPORARY NOLOGGING

USERS 8192 ONLINE PERMANENT LOGGING

ZCHBKJ 8192 ONLINE PERMANENT LOGGING

 

6 rows selected.

SQL> col file_name for a50

SQL> select file_id, file_name, tablespace_name, status, bytesfrom dba_data_files;

 

FILE_ID FILE_NAME TABLESPACE_NAME STATUS BYTES

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

4/home/oracle/oradata/mdsp/users01.dbf users available 36241408000

3/home/oracle/oradata/mdsp/undotbs01.dbf UNDOTBS1 AVAILABLE 99614720

2/home/oracle/oradata/mdsp/sysaux01.dbf sysaux available 744488960

1/home/oracle/oradata/mdsp/system01.dbf system available 765460480

5/home/oracle/oradata/mdsp/ZCHBKJ. dbf zchbkj available 524288000

SQL> select username, created from dba_users;

 

 

USERNAME CREATED

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

MGMT_VIEW 15-AUG-09

SYSTEM 15-AUG-09

DBSNMP 15-AUG-09

SYSMAN 15-AUG-09

IMUSE01 30--10 -10

ZCH 10-NOV-10

SYS 15-AUG-09

OUTLN 15-AUG-09

FLOWS_FILES 15-AUG-09

MDSYS 15-AUG-09

ORDSYS