A Tablespace in Oracle represents a location where the actual data related to tables, indexes will be stored. A database consists of one or more tablespace (tablespace made up of one or more datafiles).
Types of Tablespace
The different types of tablespace are as follows,
1. Permanent tablespace
- It contains the persistent schema objects which are stored in the data files.
2. Undo tablespace
In this article let’s discuss about using Materialized views in Oracle.
What is Materialized View?
Materialized view is similar to a view but the actual table data will exist in the Database. These views will be refreshed automatically when the master tables are updated.
REFRESH COMPLETE START WITH SYSDATE
AS SELECT EMP_ID,EMP_NAME,DEPT_ID,MAX(SALARY) FROM EMPLOYEES@orcl GROUP BY EMP_ID,EMP_NAME,DEPT_ID;
In this article lets discuss about how to load data from an excel file in to oracle database table.
In many application projects you may be faced with a requirement to export data from a database table to an excel sheet or load data from an excel or csv file in to a database table. The former can be done using the help of any oracle client like TOAD. The latter can be done using an utility available in oracle called SQL Loader.
Let go through an example of how to import data from excel or csv file in to oracle db.
In this section lets see about how to access DB objects from one Database to another database.
Assume that you have two databases testlocalServ and testremoteServ. Log-in in to the database testlocalServ. If you want to access the tables from testremoteServ database, first of all you need to create a DB link to that remote database testremoteServ. OK, How to create a DB link? its very simple using the following DDL statement.
Consider a scenario where you have huge number of records(say 1 million) in a database table and you are required to select 100,000 records from that table based on a condition given in where clause. In most cases it will take more time to retrieve the records from that huge table as the DBMS should select a subset from the huge super set. One simple solution is to use SQL query hints, forcing indexes etc in the select query. But the query running time changed based on certain DB parameters. Another sophisticated solution will be to partition DB table.
In this section we will see about how to find the child tables for a given parent table.
In many projects where we deal with databases, we would like to find the list of child tables for a parent table. A parent table will have a primary key and one or more child tables will be referencing the parent table using a foreign key. But how will you find the list of child tables referencing the parent table.
Often you may be faced with a requirement to optimize a SQL query (increase query running time) to be executed in Oracle database. Query Optimization in general is a huge topic to discuss. Let me provide some basic steps to be followed while optimizing a SQL query.
1. If the query has only one table and certain where conditions, then try to index the columns given in the where condition. By default primary key columns are indexed. Then try to reorder the set of where conditions based on number of rows for the column values in descending order.
Consider a scenario where you have accidentally deleted a DATA-FILE in oracle when the instance is running. It will throw error when you restart the DB as follows.
1. Log-in to oracle DB server
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2020288 bytes
Variable Size 285215808 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
Creating a New DB Instance
Steps for Creating New DB instance:
1) Log-in to the DB server as oracle user.
2) Move to the location $ORACLE_HOME/bin
3) Execute the following command (oradb1 is the new db name)
After running the above command , the following output will be generated