tutu etek

tutu etek: tutu etek

tutu etek

tutu etek: tutu etek

What is data files?

What is a data file in oracle?

In this article, we are going to discuss what is the use of data files in oracle and why they are so important.

Every data in the oracle database stores in the data file physically. TO access the data file in oracle database we should be adding the data file in a tablespace which is the logical representation for the data file. 

We can see the data file in the dba_data_files view or in V$datafile.

loading...

How to set environment for oracle in Linux.

How to set the environment for oracle in Linux.

In this article, we will see how to set the environment for oracle database.

For this make sure you have installed the oracle database in your Linux machine.

If you have installed the oracle database in Linux machine you should find this file named 'oratab' in configurations.

Steps to be followed :

1. If you don't know the SID you can open the oratab file by using the below command.

            cat  /etc/oratab

   The output of the commands looks like below :

   

Here we could see the last line as in the format of 

                        

                            $ORACLE_SID:$ORACLE_HOME: Y

2. By using below command you can set the Oracle SID.

       export ORACLE_SID=db11g

3. Once we set the oracle SID we can set the oracle home.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

4. Once We set the oracle home then we are also most ready just one set to connect to the database. We need to set the oracle path by using the below command.

export PATH=$PATH:$ORACLE_HOME/bin

5. Now we can able to connect the database by using.

sqlplus username@SID/password

  if you are using the windows authentication method you can give.

sqlplus / as sysdba

(or)

sqlplus sys as sysdba

  The above command let you connect as sys user with sysdba as the role.

Note: We can also use. '. oraenv' command to set the environment we can give the sid but sometimes it won't work. at that time we can use the above process to connect the database.

loading...

Oracle Database files

Files in oracle database

In this article, we can see the important files in the oracle database. Oracle database is a popular RDBMS database. It has some important files in which the database runs.

• Control File
• Parameter File
• Data File
• Redo logs
• Archive Logs
• Alert log
• Trace File

Control File : 

In Oracle database, the control file is the most important file which contains the information about the database. It contains information like

• Database name
• Number of datafiles
• Location of all the datafiles
• Last SCN Number
• Date of the database created.

So it is always good to keep the control file multiplexed.  And also this helps the database to open as consistent with data.

Parameter File:

The parameter file is the important file in oracle database Which contains the information about the SGA and PGA memory size.  And the location of the control files. Without the parameter file, we cant startup the database. The parameter file is also called as 'init.ora'.

Data file:

These files are used to store data physically on disks Which is logical present as tablespace in oracle database.

Redo logs:

These files are used to store the logs which record every transaction happening in the oracle database. These files will work in a circular fashion if a group gets filled another group will Come online. The group which currently records the transactions is called the online redo log. and the other is called offline redo logs.

Redo logs and Archive logs help to restore the database if the database is unexpectedly crashed with the help of the control file. These files are managed in a group. A redo log group contains 2 files and we always manage two or more groups and the size is based on the number of transactions happening in the database.

There are more things about the redo log. We will learn more about this later.

Archive Logs:

These files are the backup of the redo log files. As we have seen redo log will work in a circular fashion if a group comes to offline the archive log writer takes the backup of that group and stores it that is called the archive logs.

Backup File:

As we know backup is important in every working environment. However, we can able to restore the database with redo logs and archive logs with the current transactions. We should have 

Alert Logs:

This file is automatically generated by the oracle database. In this file, we can find the errors that happened in the oracle database. 

Trace files:

These files are also generated by Oracle database in some conditions like When the database id crashed and if a session gets failure.

And will post more information about each file in the next article.

loading...

dba_data_files

Dba_data_files

Dba_data_files is a default view in Oracle database. This contains information about the data files in the database.

Some important columns in this view are:

File_name:

Contains the path of the data file

File_id:

Contains the unique id for each file to identify the files. By using this id we can able to find the files.

Tablespace_name:

Contains the tablespace name which uses the data file to store data.

Bytes:

Contains the size of the data file in bytes.

Online_Status:

Contains the status of the file like offline, online.

Auto extensible:

Contains the information about the auto extensible is set on or off for the file

Incremented By:

Contains the information about if the file got filled by which size it needs to get growing based upon the Auto extensible parameter.

below query, gives the information about files and size of the files in GB.

       Select tablespace_name , file_name, bytes/1024/1024/1024 from dba_data_files;

below query, gives the information about the size of tablespaces.

       Select tablespace_name, sum(bytes)/1024/0124/1024 from dba_data_files group by tablespace_name;

loading...

DBA_USER

DBA_USERS

DBA_USERS is a default view in oracle database which describes all users of the database.

This view contains information about the users in the oracle database like username, user_id, password, account_status, lock_date, expiry_date, default_tablespace, temporary_tablespace, local_temp_tablespace, created, profile, password_versions.

Column description:

Username:

Contains the name of the user.

User_id:

Contains ID number of the user.

Password:

This column is deprecated in favour of the AUTHENICATION_TYPE column.

Account_status:

This column shows the status of the account as below:

• open
• expired
• expired (grace)
• locked (timed)
• locked
• expired & locked (timed)
• expired (grace) & locked (timed)
• expired & locked

Lock_date:

show the date of account locked if the account was in locked status.

Expiry_date:

Shows the date of expiration of the account.

Default_tablesapce:

Contains the default tablespace name which is assigned to user as default for data.

Temporary_tablespace:

Contains the default tablespace name which is assigned to user as default for temporary data.

Local_temp_tablspace:

Contains the default local temporary tablespace name for the user.

Note: This is available from oracle database 12c.

Created:

Contains the date on which the user has been created.

Profile:

Contains the profile name for the user.

Initial_RSRC_Consumer_group:

Initial resource consumer group for the user.

External_Name:

Contains user external name.

Also, there is a view similar to dba_users is user_users which has information only about the user which you have logged in.

loading...