DATABASE

Load data from excel sheet to oracle table

  In this article, we will explain different methods to load data from excel sheet into Oracle database. Below are the methods that can be used. SQL DEVELOPER  SQLCL utility  TOAD SQLLOADER Here we will explain how to do this with SQL developer and SQLCL utility. USING SQLDEVELOPER TOOL: SQL developer is a free tool […]

Force logging in oracle

What is force logging: If force logging is enabled, all the database changes will be logged in redo log files, even for nologging operations.The above means that if FORCE LOGGING is enabled at a higher level, the NOLOGGING at a lower level has no effect. That means that even for a nologging table redo information […]

resumable_timeout parameter in oracle database

resumable_timeout is an initialization parameter introduced in Oracle 9i.  This parameter defines the duration in seconds, for which the statements will be suspended if there is no space available in the tablespace for that operation. Once we add space to that tablespace, those transactions will resume their operation. Let’s see the below example: SQL> show […]

Schedule expdp job in dbms_scheduler

We usually schedule expdp jobs in a standard shell script.  But the same can be achieved using dbms_scheduler utility also. Below are steps for scheduling expdp job. Here we will schedule a full expdp backup of the database, which will run daily at 11:30 HRS. 1. First, prepare the parfile cat expdp_tab.par userid=system/oracle dumpfile=FULL_DB.dmp logfile=FULL_DB.log […]

Scheduler jobs in oracle

To schedule a job at a particular time in the database, first we need to create a schedule, then a program and then job. 1. Create a schedule A schedule defines the start date, end time and repeat interval details BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE ( Schedule_name => ‘DAILYBILLINGJOB’, Start_date => SYSTIMESTAMP, Repeat_interval =>’FREQ=DAILY;BYHOUR=11; BYMINUTE=30′, Comments => ‘DAILY […]

User Management in Oracle

User is basically used to connect to database. All db objects like table,index,view etc can be created under that user.In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, […]

Tablespace Management in oracle

What is a tablespace and datafile: Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(DISKS) . So when we create a datafile of 30G in database, it will eat 30G of space from defined storage. All the table or […]

Cloning Oracle home from one server to another server

    Below are the steps for cloning ORACLE_HOME from one server to another server. 1. Check the oracle_home inventory [ SOURCE DB] $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2017, Oracle Corporation. All rights reserved. Oracle Home : /oracle/app/oracle/product/12.1.0.2_CRMTST/dbhome_1 Central Inventory : /oracle/app/oraInventory from : /oracle/app/oracle/product/12.1.0.2_CRMTST/dbhome_1/oraInst.loc OPatch version : 12.1.0.1.10 OUI […]

Clone a RAC database to Standalone database using RMAN backup

                    Cloning from a RAC database to standalone database, can be done either using ACTIVE CLONE or by taking  a RMAN backup from RAC database and restoring in standalone database. SEE ALSO – RMAN ACTIVE CLONING Here in this article,we will show db cloning by taking […]

Expdp Impdp Tutorial

1. Import using NETWORK_LINK 2. QUERY CLAUSE in EXPDP 3. SQLFILE parameter in impdp 4. TABLE_EXISTS_ACTION option in IMPDP 5. EXCLUDE/INCLUDE option 6. DATA_OPTION=SKIP_CONSTRAINT_ERRORS 7.LOGTIME=ALL Parameter – Oracle 12c new feature 8. Views_as_tables Parameter In Datapump Of Oracle 12c 9. TRANSFORM=DISABLE_ARCHIVE_LOGGING – Oracle 12c new feature 10. ENCRYPTION in export Import 11. CONTENT parameter in […]