POP Keywords: apache oracle sybase ...

[oracle] 11g – import data with impdp

Approach is as follow: create directory object inside oracle grant import rights perform import step by step login as system admin into sqlplus: $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba create oracle directory object pointing to folder where dump is located: create directory db_dumps as ‘/u01/app/oracle/dbs_to_load'; grant read,write on directory db_dumps to <dbuser>; grant import rights to db user: grant create any directory to <dbuser>; grant imp_full_database to <dbuser>; import dump file: $ORACLE_HOME/bin/impdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log remap_schema=<old_schema>:<new_schema> remap_tablespace=<old_schema>:<new_schema> note: replace values between <xxx> with your own values Importing with data filtering Data filtering can be added using QUERY=<table_name>:”<where clause>” When using […]

[oracle] 11g – export data with expdp

Approach is as follow: create directory object inside oracle grant export rights perform export step by step create physical directory on the drive where dump will be created: sudo mkdir /u01/app/oracle/exported_dbs sudo chown oracle:dba /u01/app/oracle/exported_dbs login as system admin into sqlplus: $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba create oracle directory object: create directory db_dumps  as ‘/u01/app/oracle/exported_dbs'; grant read,write on directory db_dumps to <dbuser>; grant export rights to db user: grant create any directory to <dbuser>; grant exp_full_database to <dbuser>; generate dump file: $ORACLE_HOME/bin/expdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log note: replace values between <xxx> with your own values source http://el.web.id/how-to-export-and-import-oracle-database-using-impdp-and-expdp-in-linux-212 (awesome tutorial!) http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#i1009049 […]

[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

create zip file from shell

pretty easy! open shell and run: zip <path_to_file_to_create>.zip <elements_to_add> Example: zip ../mycontent.zip *.xml

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

/bin/cp: cannot execute [Argument list too long] – [solved]

When executing simple command line statements such as rm, cp or ls on a folder containing large number of files, following error is thrown: cannot execute [Argument list too long] Workaround this issue is to use the “find” process along with “-exec” argument, as per below example on copy statement: find <src_path> -name “*.xml” -exec cp “{}” <destination_path> \; note: replace “cp” with “rm” or whichever command failing due to large number of entries. sources http://stackoverflow.com/questions/11289551/argument-list-too-long-error-for-rm-cp-mv-commands http://stackoverflow.com/questions/143171/how-can-i-use-xargs-to-copy-files-that-have-spaces-and-quotes-in-their-names

unix – count number of files inside a folder

Simply run following command line: ll *.xml | wc -l in above example, we are counting number of files ending with .xml => feel free to adapt this query

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 […]