Oracle TableSpace
Tablespace
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
- Read more
- 2738 reads
-
Materialized Views
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;
- Read more
- 2603 reads
-
Oracle PL/SQL to delete child records
In this article lets discuss about how to delete a record(or row) from a table, if it has a tree of child tables (like child table has one or more child tables).
- Read more
- 11952 reads
-
Load Data from Excel to Oracle database
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.
- Read more
- 29436 reads
-
Oracle Create DB Link
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.
- Read more
- 3397 reads
-
Oracle Table Partition
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.
- Read more
- 3932 reads
-
ORACLE Query to find child tables
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.
- Read more
- 15709 reads
-
SQL Query Optimization
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.
- Read more
- 3442 reads
-
Oracle - DB Startup Failure unavailability of Datafile
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
SQL> startup
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
Database mounted.
- Read more
- 2807 reads
-
Oracle Create a New DB Instance
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
1% complete
3% complete
- Read more
- 2922 reads
-