How to check where my oracle database files are?

Oracle database files are stored in binary files having extension *.dbf.

In order to find where these files are stored, and what is the size of these files, you need to query the table daba_data_files.

 

SQL> select bytes/1024/1024 "MB", file_name from dba_data_files;

MB         FILE_NAME
--------- -----------------------------------------------------

5           C:APPORADATAORCLUSERS01.DBF
55          C:APPORADATAORCLUNDOTBS01.DBF
510         C:APPORADATAORCLSYSAUX01.DBF
680         C:APPORADATAORCLSYSTEM01.DBF
100         C:APPORADATAORCLEXAMPLE01.DBF


 

To check when these files were created, you may query the table v$datafile

SQL> select creation_time, name from v$datafile;

CREATION_ NAME
--------- --------------------------------------------

30-MAR-10 C:APPORADATAORCLSYSTEM01.DBF
30-MAR-10 C:APPORADATAORCLSYSAUX01.DBF
30-MAR-10 C:APPORADATAORCLUNDOTBS01.DBF
30-MAR-10 C:APPORADATAORCLUSERS01.DBF
06-NOV-11 C:APPORADATAORCLEXAMPLE01.DBF


 

Using the SQLPLUS buffer

While logged in to sqlplus, you may find the following useful:

  • use the forward slash to repeat the last executed query
  • /

  • use l or list to view the last executed SQL statement
  • l
    list

  • use the change command to modify the last executed sql statement
  • c/initial_word/ReplaceBy_word

  • Change one line of the sql statement
  • 2 order by colname3, colname4

  • Use your default editor to change the last sql statement.
  • ed

  • Save the last used sql statement
  • save myfile.sql

  • Executing an SQL file
  • @myfile

How to check my oracle connection parameters?

If you are already connected to oracle through sqlplus, type define, and you will know what is the connect_identifier, user, pervilages, oracle version….

SQL> define

You should receive something similar to this


DEFINE _DATE = "06-NOV-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.
1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1102000100" (CHAR)

How to check what is my default database?

If you have more than one database installed on your server, the default database name which sqlplus or any application trying to access a database on a local machine or a remote server is stored in an environment variable called oracle_sid.

In order to know what is the oracle_sid environment variable,

Step1: go to a windows command prompt or Linux shell

Step 2: Type the following command and press Enter

On windows type:

echo %oracle_sid%

on unix/linux type:

echo $ORACLE_SID

Oracle Identifier will be returned, for example:

orcl

How to connect to oracle?

The easiest and fastest way to connect to oracle is by using sqlplus from a windows command line or Linux terminal:

Step 1: Open the windows command line / Linux shell

Step 2: Type sqlplus and hit Enter.

Ideally, if you have installed oracle properly on your machine, your operating system will automatically recognize the “sqlplus” command and prompt you for entering the user name. If this doesn’t happen, it means you have not properly installed oracle yet.

Step 3: Type the username, press Enter

Step 4: Type the password, press Enter

You may use username: scott, password: tiger, which is one of the oracle built in username/ password which you have the option to enable during oracle installation. Otherwise use any username/ password you have for your database.


C:>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 6 17:26:01 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>