Tag Archives: oracle

[oracle] create new data file

To create a new data file in oracle 11g is pretty easy. Steps are: login as sysdba (you can use sqldeveloper or sqlplus, user sys/<password>@<SID> as SYSDBA) execute below statements, replacing values as per requirement on your side (this is creating auto extendable db files): — create a user tablespace to be assigned as the default tablespace for users CREATE TABLESPACE rddbfile LOGGING DATAFILE ‘/u01/app/oracle/oradata/rddbfile_data.dbf’ SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; — create a tablespace for indexes, separate from user tablespace CREATE TABLESPACE indx LOGGING DATAFILE ‘/u01/app/oracle/oradata/rddbfile_idx.dbf’ SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K […]

which version of oracle db am i using?

Simply run below query to identify which version you are currently running: SELECT * FROM PRODUCT_COMPONENT_VERSION; sources http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba004.htm#ADMIN11039

[oracle] set default schema

To set default schema for all queries (and avoid prefixing all tables), execute below statement in active db session: Alter session set current_schema = USR where “USR” is schema to use by default

oracle – update rows using joins and select statement

To update rows resulting from join of several tables, use following syntax in oracle: MERGE INTO table_to_update t USING (select field1, field2 from other_table) t2 ON (trim(t.field1 = t2.field1) WHEN MATCHED THEN UPDATE SET t.field3 = t2.field2 where t.field3 = t2.field2 and t.field4 like ‘%DUMMY%’; example: sources http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems004.htm http://stackoverflow.com/questions/14519933/want-to-add-some-conditions-in-merge-statement-in-oracle-sql-for-insert-update

Connect to Oracle DB from Excel

Steps to connect to oracle db using TNS on windows xp & excel 2010 [A. create and configure TNS] create file named tnsnames.ora under c:\oracle\tnsnames\ and store tns definition as per below (as example only): LOCALORCL11R2V = ( DESCRIPTION=( ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)( ADDRESS=(PROTOCOL=TCP)(HOST=<server1>)(PORT=1521) )( ADDRESS=(PROTOCOL=TCP)(HOST=<server2>)(PORT=1521) )( ADDRESS=(PROTOCOL=TCP)(HOST=<server3>)(PORT=1521) )( ADDRESS=(PROTOCOL=TCP)(HOST=<server4>)(PORT=1521) ) )( CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=<service_name>) ) ) set up global variable TNS_ADMIN to point to folder where tnsnames.ora file has been created click on start menu > right click on computer name > select “properties” as per below screenshot go to “Advanced” tab > click on “Environment Variables” > “New” and create variable TNS_ADMIN to make it […]

oracle 11g – load dump exported with expdp

Identifed steps to import dump on Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production Step 1. log in as sysadmin all steps below are to be done when logged as sysadmin (username: SYS, pwd:XXX) in case password is forgotten, log into oracle box with user used to install oracle and no password will be asked when connecting using below command line: sqlplus / as sysadmin note: if password for unix user used to install oracle is also forgottern, log in with any user then su to be root then su <user_used_to_install_oracle> Step 1. create same user as used […]

Oracle XE 11g R2 – activate audit / log queries

To log queries on oracle, proceed as follow: login as system (`sqlplus system`) check audit status show parameter AUDIT_TRAIL; // if NONE => no audit activated activate audit ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE; restart db for parameter to take effect /etc/init.d/oracle-xe restart from this point forward, activate audit on specific tables and/or actions and/or users using the AUDIT XXX statement, as `system`, run: AUDIT SELECT on userlambda.tablename // audit/log all SELECT queries on all tables db for user userlambda All audit entries are stored under sys.AUD$ table (login as system to access it). Note: to deactivate audit, run ALTER SYSTEM […]

install oracle sqlplus client – linux

When trying to get sqlplus client running, following error was generated: sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory This happens because environment variable LD_LIBRARY_PATH was empty. Make it point to ./lib folder where sqlplus client was extracted and this should solve the problem. In my case, oracle sqlplus client was generated under /opt/oracle LD_LIBRARY_PATH=/opt/oracle/bin export LD_LIBRARY_PATH And voila! sources http://betterlogic.com/roger/2010/11/how-to-install-sql-plus-in-linux/

installing sqldeveloper on centos

Steps to install sqldeveloper download rpm from oracle website or simply run `sudo apt-get install sqldeveloper-package` to install it from repository once sqldeveloper is installed, create connection, if test fails with error message “Network Adapter ….” this may simply mean: hostname is not in /etc/hosts and as such computer running oracle cannot be fetched port on oracle is blocked by firewall. To test this, simply run telnet <ip> <port> to check if port is opened If not open, edit vi /etc/sysconfig/iptables and add a line (same as for port 22) for oracle port (default port 1521) Then run service ipconfig […]

Database Configuration failed. Look into /u01/app/oracle/product/11.2.0/xe/config/log for details [solved]

This error occurred when I was installing oracle 11g R2 express. Post installing rpm file, next action is to run ‘sudo /etc/init.d/oracle-xe configure’. Following error message is displayed: Database Configuration failed. Look into /u01/app/oracle/product/11.2.0/xe/config/log for details When looking at log files, obscure messages were printed: Create controlfile reuse set database “XE” * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 BEGIN dbms_backup_restore.zerodbid(0); END; * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 ORA-01034: ORACLE not available ORA-27101: shared memory realm does not […]