Experiments & Automation

Tuesday, 27 January 2015

Table Space in Oracle

Table Space can be find in the below way using SQL Developer –

Right Click on the Connection
Click on Manage Database

The same can be achieved using the below SQL query –

Database link can be created using following command –

SELECT

DF.TABLESPACE_NAME "TABLESPACE_NAME",

TOTALUSEDSPACE "USED",

(DF.TOTALSPACE - TU.TOTALUSEDSPACE) "FREE",

DF.TOTALSPACE "TOTAL",

ROUND(100 * ( (DF.TOTALSPACE - TU.TOTALUSEDSPACE)/ DF.TOTALSPACE)) "PCT_FREE"

FROM

( SELECT

TABLESPACE_NAME,

ROUND(SUM(BYTES) / 1048576) TOTALSPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME

) DF,

( SELECT

ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACE, TABLESPACE_NAME

FROM DBA_SEGMENTS

GROUP BY TABLESPACE_NAME

) TU

WHERE DF.TABLESPACE_NAME = TU.TABLESPACE_NAME;

Space Consumend by Table

Space consumed by a Table can find out in below way in SQL Developer –

Go to View -> Report

Go to Data Dictionary Report -> Database Administration -> All Tables -> Storage -> Tables by Tablespace

Same can be achieved by below SQL Query—

SELECT

LOWER( OWNER ) AS OWNER

,LOWER(TABLE_NAME) AS TABLE_NAME

,TABLESPACE_NAME

,NUM_ROWS

,BLOCKS*8/1024 AS SIZE_MB

,PCT_FREE

,COMPRESSION

,LOGGING

FROM ALL_TABLES

WHERE OWNER = :1

ORDER BY 1,2;

Datafile Space in Oracle

Space consumed by a Datafile can find out in below way in SQL Developer –

Go to View -> Report
Go to Data Dictionary Report -> Database Administration -> Storage -> Datafile

SQL statement to achieve the same –

SELECT SUBSTR(DF.TABLESPACE_NAME,1,20) "TABLESPACE_NAME",

SUBSTR(DF.FILE_NAME,1,80) "FILE_NAME",

ROUND(DF.BYTES/1024/1024,0) "SIZE (M)",

DECODE(E.USED_BYTES,NULL,0,ROUND(E.USED_BYTES/1024/1024,0)) "USED (M)",

DECODE(F.FREE_BYTES,NULL,0,ROUND(F.FREE_BYTES/1024/1024,0)) "FREE (M)",

DECODE(E.USED_BYTES,NULL,0,ROUND((E.USED_BYTES/DF.BYTES)*100,0)) "% USED"

FROM DBA_DATA_FILES DF,

(SELECT FILE_ID,

SUM(BYTES) USED_BYTES

FROM DBA_EXTENTS

GROUP BY FILE_ID) E,

(SELECT MAX(BYTES) FREE_BYTES,

FILE_ID

FROM DBA_FREE_SPACE

GROUP BY FILE_ID) F

WHERE E.FILE_ID (+) = DF.FILE_ID

AND DF.FILE_ID = F.FILE_ID (+)

ORDER BY DF.TABLESPACE_NAME,

DF.FILE_NAME;

More on Tablespace – http://www.orafaq.com/wiki/Tablespace

Saturday, 24 January 2015

Database Link

Db link is a useful mechanism to pull data from external database.

Database link use TCP/IP connection during connection establishment with external database & fetching data.

To create Database like --

You must have CREATE DATABASE LIKE privilege
The Account using which your system will be connecting the remote database must have CREATE SESSION system privilege.

Both the above privileges are included as part of CONNECT role in oracle.

Database link can be created using following command –

CREATE [PUBLIC|PRIVATE|SHARED] DATABASE LINK NAME_OF_DB_LINK

CONNECT TO USER_ID IDENTIFIED BY PASSWORD

USING 'CONNECTION_STRING';

PUBLIC: - PUBLIC Database link are available for all user in the database.
PRIVATE: - PRIVATE link are available for only to the user who creating it.
SHARED: - Shared link are useful to establish many concurrent database connection.

There are 2 ways to specify the connect string --

Specify the TNS Description in place of ‘CONNECTION_STRING'. In this case the sql command would be --

CREATE [PUBLIC|PRIVATE|SHARED] DATABASE LINK NAME_OF_DB_LINK

CONNECT TO USER_ID IDENTIFIED BY PASSWORD

USING

‘(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = ABC7821.xyz.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = HWCRMSIT.xyz.com)

)

)’;

Create a Service Naming using Oracle Net Manager & specify the same service naming in ‘CONNECTION_STRING'

CREATE [PUBLIC|PRIVATE|SHARED] DATABASE LINK NAME_OF_DB_LINK
CONNECT TO USER_ID IDENTIFIED BY PASSWORD
USING ‘hwcrmqa’;

Import from Oracle

Import from Oracle into Excel using CMD

We can take export into excel file directly from SQL Developer. Here is one more method, using which we can take export of sql output into excel.

How to Implement?

Create a .sql file, say TakeExport.sql & add the below code into it.

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;

SPOOLoutput.xls;

SELECT 'a' AS A FROM DUAL UNION ALL SELECT 'b' AS A FROM DUAL UNION ALL SELECT 'c' AS A FROM DUAL; -- sample query

SPOOL OFF;

EXIT;

Create batch file & add the following code to call the TakeExport.sql

sqlplus abcd/welcome1@CRMPRD @ TakeExport.sql

Fast Import from Oracle using Vbscript

I created this utility to export large amount of data (> 100000) from oracle. We can export from SQL Developer in excel sheet/csv, but to export large amount of data from SQL Developer takes significant amount of time. This is a lightweight utility, which can be useful to export data very quickly (approx. . .1k data in 20 minutes).

Limitation:-

Export data not shown properly, when LONG column available in output.

Prerequisites:-

Create DSN in odbcad32

Procedure:-

Create folder “BULK_EXPORT”

Prepare the SQL statement; store it in a text file “sqlQuery.sql” in the folder created in step 2. The sql statement should be saved in a single line.

Create Init.vbs file & save the below text in it. This vbs file will call another vbs file “TakeExport_Notapad.vbs” using cscript.

Create Init.vbs file & save the below text in it. This vbs file will call another vbs file “TakeExport_Notapad.vbs” using cscript.

Set objShell = CreateObject("WScript.Shell")

returnParam = objShell.Run ("C:\Windows\SysWOW64\cscript.exe TakeExport_Notapad.vbs", 0, true)

Create a new vbs file named “TakeExport_Notapad.vbs” & add the below text into it.

TakeExport

Function TakeExport

On Error Resume Next

sqlQuery = ".\sqlQuery.sql"

traceFile = ".\EXPORT.txt"

connectionString = "Driver=(Oracle in OraHome92);" & "Data Source=PROD_ENT;UID=ABCD;Password=welcome1;"

Const ForReading = 1, ForWritting = 2, ForAppending = 8

Set objFso = CreateObject("Scripting.FileSystemObject")

Set file = objFso.OpenTextFile(sqlQuery, ForReading, True )

query = file.ReadLine

file.Close

Set file = objFso.OpenTextFile(traceFile, ForAppending, True )

file.WriteLine ("Export Start")

file.Close

Set connection = CreateObject("ADODB.Connection")

connection.Open connectionString

Set rs = connection.Execute(query)

input = "$"

Do Until rs.EOF

Set file = objFso.OpenTextFile(traceFile, ForAppending, True )

output= ""

for i = 0 To rs.Fields.Count - 1

output = output & "" & rs.Fields.Item(i) & "" & input

file.WriteLine (output)

file.Close

rs.MoveNext

Loop

Set file = objFso.OpenTextFile(traceFile, ForAppending, True )

file.WriteLine ("Export End")

file.Close

Set connection = Nothing

Set rs = Nothing

End Function