Tag Archives: oracle

Delete old files in Windows and Oracle

24 Jul

Oracle produces a lot of trace and log files. Take a backup of the files before renaming/removing them. For the trace files – it’s wise to keep 1-2 week of alert/trace files in the same directory. Here’s an example to create a bat-file which you can schedule to delete old files (older than 14 days:

forfiles /p D:\App\Oracle\diag\rdbms\ora01\ora01\alert\ /s /m *.* /D -14 /C “cmd /c del /F /Q @path”

Oracle will automatically create a new alert log files when you rename/delete existing file.

Read more

https://community.oracle.com/thread/3544775?start=0&tstart=0

http://dba.stackexchange.com/questions/15715/how-to-free-up-disk-space-which-logs-directories-to-clean

https://community.oracle.com/thread/2297940?tstart=0

http://ss64.com/nt/forfiles.html

http://ss64.com/nt/del.html

http://stackoverflow.com/questions/51054/batch-file-to-delete-files-older-than-n-days

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:402360068979

http://dba-masters.blogspot.no/2012/08/11g-r2-trace-files-and-alert-logs.html

http://stackoverflow.com/questions/7160342/how-to-skip-are-you-sure-y-n-when-deleting-files-in-batch-files

http://www.databasejournal.com/features/oracle/article.php/3875896/Purging-Oracle-Databases-Alert-Log-with-ADRCI—Usage-and-Warning.htm

Advertisements

Differences in syntax between Oracle and MS SQL

15 Jan

 

Description Oracle MS SQL Server
Smallest integer >= n CEIL CEILING
Modulus MOD %
Truncate number TRUNC <none>
Max or min number or string in list GREATEST,
LEAST
<none>
Translate NULL to n NVL ISNULL
Return NULL if two values are equal DECODE NULLIF
String concatenation CONCAT(str1,str2) str1 + str2
Convert ASCII to char CHR CHAR
Capitalize first letters of words INITCAP <none>
Find string in string INSTR CHARINDEX
Find pattern in string INSTR PATINDEX
String length LENGTH DATALENGTH
Pad string with blanks LPAD,
RPAD
<none>
Trim leading or trailing chars other than blanks LTRIM(str,chars),
RTRIM(str,chars)
<none>
Replace chars in string REPLACE STUFF
Convert number to string TO_CHAR STR, CAST
Convert string to number TO_NUMBER CAST
Get substring from string SUBSTR SUBSTRING
Char for char translation in string TRANSLATE <none>
Date addition ADD_MONTH or + DATEADD
Date subtraction MONTHS_BETWEEN or – DATEDIFF
Last day of month LAST_DAY <none>
Time zone conversion NEW_TIME <none>
Next specified weekday after date NEXT_DAY <none>
Convert date to string TO_CHAR DATENAME, CONVERT
Convert string to date TO_DATE CAST
Convert date to number TO_NUMBER(TO_CHAR(d)) DATEPART
Date round ROUND CONVERT
Date truncate TRUNC CONVERT
Current date SYSDATE GETDATE
Convert hex to binary HEXTORAW CAST
Convert binary to hex RAWTOHEX CONVERT
If statement in an expression DECODE CASE … WHEN
or COALESCE
User’s login id number or name UID, USER SUSER_ID, SUSER_NAME
User’s database id number or name UID, USER USER_ID, USR_NAME
Current user USER USER

 

Read more below

http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm

http://www.bristle.com/Tips/SQL.htm#Differences%20in%20Built-In%20Functions

http://www.bristle.com/Tips/SQL.htm#Differences%20Between%20Oracle%20and%20MS%20SQL%20Server

Connect Excel to Oracle through ODBC

16 Aug

Steps to setup:

  1. Install Oracle client http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
  2. Install odbc http://www.oracle.com/technetwork/topics/winsoft-085727.html
  3. Use the odbc administrator in the syswow64 directory to create a DSN pointing to oracle

 

 

4. Configuring Excel to Connect to Oracle

Open a new book or sheet in Excel. Follow the Data>Import External Data>Import Data path to select a data source. Highlight +Connect to New Data Source and click Open (or double-click it) to bring up the Data Connection Wizard.

 

 

 

 

 

 

 

 

 

 

 

 

Useful links:

http://www.databasejournal.com/features/oracle/article.php/10893_3358411_1/Connecting-with-Oracle-Accessing-Oracle-via-Access-and-Excel.htm

https://forums.oracle.com/thread/2558996

https://forums.oracle.com/message/10703232

 

 

Databaselink from Oracle to MS SQL database (heterogeneous database connections)

14 Feb

You have to do the following (Windows2008R2 is the OS on both of the servers: Oracle server (11G) and MS SQL server 2012):

    1. Install Oracle ODBC driver on the Oracle database server where the instance(database) you are using to access the remote MS SQL server database
    2. Use “ODBC Data Source Administrator” to create “System DSN” for the connection:
      • Choose the SQL Server driver
      • Supply the name for the connection (ODBCNAME)
      • Supply the sql server (and or with instance name like sqlserver1\database1
      • Choose either Windows authentication or create a user in that database to with read access in MS SQL (SQL_USER with db_datareader)
      • Choose default database to access
      • Finish and test the connection
    3. Run this command to check for global names paremeter in Oracle is set to false:
      • show parameter global
    4. Create initODBCNAME.ora in $ORACLE_HOME$\dbhome_1\hs\admin
      • HS_FDS_CONNECT_INFO=ODBCNAME
    5. Modify the listener.ora
      • for Oracle 11g: (SID_DESC = (SID_NAME = ODBCNAME) (ORACLE_HOME = E:\Oracle\product\11.2.0\dbhome_1) (PROGRAM = dg4odbc))
      • for Oracle 10g: (SID_DESC = (SID_NAME = ODBCNAME) (ORACLE_HOME = E:\Oracle\product\10.2.0\dbhome_1) (PROGRAM = hsodbc))
    6. Modify the tnsnames.ora
      • ODBCNAME.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLESERVER)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ODBCNAME) ) (HS=OK) )
    7. Restart the listener.ora (through services or through the command line)
    8. Create database link in Oracle database
      • CREATE PUBLIC DATABASE LINK “ODBCNAME.WORLD” CONNECT TO “SQL_USER” IDENTIFIED BY “XXX” USING ‘ODBCNAME.WORLD’;
    9. Test run a sql query which uses the database linke e.g
      • select sysdate from dual@ODBCNAME.WORLD

Other useful links:

http://dbaforums.org/oracle/lofiversion/index.php?t21694.html

http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

http://www.orafaq.com/forum/t/171463/0/

http://docs.oracle.com/cd/B28359_01/gateways.111/b31042/configodbc.htm

http://www.datadirect.com/resources/odbc/oracle-heterogeneous/common-error.html

http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm

How to tune SQL or Identify Performance Problem and Bottleneck

10 Nov

http://www.orafaq.com/wiki/Category:Performance_Tuning

http://www.orafaq.com/faq/performance_tuning

http://www.orafaq.com/forum/t/84315/74940/

Oracle blogs

4 Oct

http://www.askmaclean.com/

http://hoopercharles.wordpress.com/archived-blog-articles/

 

7 Tuning Windows to Optimize Oracle Database

3 Oct

http://docs.oracle.com/cd/B28359_01/win.111/b32010/tuning.htm