How To Check Redolog File Size in Oracle

SQL> set linesize 300
SQL> column REDOLOG_FILE_NAME format a45
SQL> SELECT
        a.GROUP#,
        a.THREAD#,
        a.SEQUENCE#,
        a.ARCHIVED,
        a.STATUS,
        b.MEMBER    AS REDOLOG_FILE_NAME,
        (a.BYTES/1024/1024) AS SIZE_MB
    FROM v$log a
    JOIN v$logfile b ON a.Group#=b.Group#
    ORDER BY a.GROUP# ASC;

    GROUP#    THREAD#  SEQUENCE#  ARC           STATUS                    REDOLOG_FILE_NAME                    SIZE_MB
---------- ---------- ---------- --- --------- --------------------------------------------- ---------------------------------------------------------------------
         1               1               17309           NO             CURRENT   +RECO/ieclive/onlinelog/group_1.257.860629021        200
         1               1               17309           NO              CURRENT   +DATA/ieclive/onlinelog/group_1.266.860629021       200
         2               1               17306          YES             INACTIVE  +RECO/ieclive/onlinelog/group_2.259.860629023        200
         2               1               17306          YES             INACTIVE  +DATA/ieclive/onlinelog/group_2.268.860629023        200
         3               2               30917          NO              CURRENT   +DATA/ieclive/onlinelog/group_3.261.860629087        200
         3               2               30917          NO              CURRENT   +RECO/ieclive/onlinelog/group_3.261.860629087        200
         4               2               30914         YES              INACTIVE  +DATA/ieclive/onlinelog/group_4.270.860629089         200
         4               2               30914         YES              INACTIVE  +RECO/ieclive/onlinelog/group_4.262.860629089         200
         5               1               17307         YES              INACTIVE  +RECO/ieclive/onlinelog/group_5.258.860629023         200
         5               1               17307         YES              INACTIVE  +DATA/ieclive/onlinelog/group_5.272.860629023         200
   

Enjoy Reading...

How To Check Undo Tablespace Size and Free Size in Oracle

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
     from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a,
     dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = 'UNDO'
     group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
     from DBA_UNDO_EXTENTS c where status <> 'EXPIRED'group by c.tablespace_name) b
     where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME     SIZEMB    USAGEMB        FREEMB
--------------- ---------- ---------- -------------------------------------------------
UNDOTBS1                        15360         50.6875           15309.3125
UNDOTBS2                        15360         40.5625           15319.4375

Enjoy Reading...

How to Exclude Constraints in oracle using Datapump Expdp Utility

D:\app\MOIN\product\11.2.0\dbhome_1\BIN>expdp dumpfile=PRSEMP_EXCONST directory=full_imp tables=fmis.prs_master_employee_main EXCLUDE=CONSTRAINT

Export: Release 11.2.0.3.0 - Production on Tue Nov 22 23:25:22 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=PRSEMP_EXCONST directory=full_imp tables=fmis.prs_master_employee_main EXCLUDE=CONSTRAINT
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "FMIS"."PRS_MASTER_EMPLOYEE_MAIN"           333.6 KB    1508 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded


Happy Learning....

Load Excel Data in Oracle Tables Using SQL-Loader

Hi Guys,

Today I will show you how simple is to load data in oracle table from Excel using 'SQL-LOADER' utility.

Steps:-

> First convert the Excel file into (.CSV) format.
> Table Structure like column names and Data types values in Excel sheet Should be same in the Database.
> Create Controlfile in notepad and mention parameters like..

LOAD DATA
INFILE 'C:\Users\Desktop\EMP1.csv'
INTO TABLE EMP1
FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY ","
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)


> Now run the sqlldr utility with username/password and controlfile (.ctl) path.
> Check the row count after you get 'COMMIT POINT REACHED' message.
> We have loaded 10 records from excel Sheet which displays the output 'logical record 10'



D:\app\oracle> sqlldr sam/s control=C:\Users\Desktop\load.ctl log=emp1.log
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 4 10:33:40 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 10


> Now check the table counts.

SQL> select count(*) from emp1;

  COUNT(*)
-----------
        10


Happy Learning...