To manage the database level tractions such as modifications of the data dictionary table that record information about the free space usage.
Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
Changes made to the records are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.
The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database’s data files.
An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.
Each index has an index segment that stores all of its data.
- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.
It is used to set the optimal length of a rollback segment.
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
Dictionary cache is information about the database objects stored in a data dictionary table.
To achive this use rownum pseudocolumn with query, like
SQL> select rownum, ename from emp;
Output:
-----------------
1 Scott
2 Millor
3 Jiyo
4 Smith
In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.
Group of tables physically stored together because they share common columns and are often used together is called cluster.
Too_many_rows,
No_Data_Found,
Value_Error,
Zero_Error,
Others
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
Integrity constraint is a rule that restricts values to a column in a table.
Every Oracle database has one or more physical data files. A database’s data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.
An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.
The System Global Area in an Oracle database is the area in memory to facilitate the trfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.
PCTFREE parameter
Row size also reduces no of rows per block.
Sql> show user;
There are four cursor attributes used in Oracle
cursor_name%Found,
cursor_name%NOTFOUND,
cursor_name%ROWCOUNT,
cursor_name%ISOPEN
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
- The space used traction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
- Trailing nulls and length bytes are not stored.
- Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.
months_between
Don’t grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
Shared pool in oracle contains cache information that collects, parses, interprets and executes SQL statements that goes against database. This shared pool acts like a buffer for these SQL statements.
We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.
The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
You can script the process to include it in a set of install scripts you deliver with a product.
You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.
You can log the output and review it for errors. You learn more about the process of database creation, such as what options are available and why.
They allow the database to maintain read consistency between multiple tractions.
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads – Physical Reads – Hits Misses)/ Logical Reads.
Database link is a named path through which a remote database can be accessed.
Views do not contain or store data.
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.
The set of redo log files for a database is collectively known as the database redo log.
Shutdown the database
Copy one of the existing controlfile to new location
Edit Config ora file by adding new control filename
Restart the database.
When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join – Joining the table with itself.
Equi Join – Joining two tables by equating two common columns.
Non-Equi Join – Joining two tables by equating two common columns.
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
There are tablespaces and database’s schema objects.
The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing into disk. Disk corruption will be very less.