Category Archives: mysql

[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 (awesome tutorial!) […]

[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

[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

sybase: multi-line comment generates following error: Missing end comment mark ”.

In sybsase transact-sql script, my multiline comment /* … */ generated the following error: Missing end comment mark ”. The reason is that: my script is in an external file loaded via sqsh sqsh relies on isql in isql, according to official doc, ‘go’ instruction should not be at the beginning of a line inside a comment => I change my code from /* … go */ to /* … **go */ and this solved my issue. sources

sybase: fetch table size and indexes size

Pretty easy, use the sp_spaceused stored procedure as follow: sp_spaceused my_table_name go To focus on indexes for a given table, you can call sp_helpindex <index_name> query as follow: exec sp_helpindex  ‘my_table_name’ go sources

sybase: left pad with whitespace

Below is a quick & dirty function to left-pad an integer (or anything you want if you adapt the function) with a blank space (or any other character). Solution is based on Sybase native ‘replicate’ and ‘len’ functions. declare @id int, @prefix char(2), @padding_width int set @padding_width = 10. set @id = 66. set @prefix = ‘PR’ select @prefix || replicate(‘ ‘, @padding_width – len(convert(varchar(10), @id))) || convert(varchar(10), @id) go ———— PR 66 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] sources    

sybase: how to quickly concatenate two columns

To quickly concatenate two columns in sybase, you can use the ‘||’ (OR) operator in your select statement as illustrated below: select column_1 || column_2 from my_table It would return a single column with content: ‘value_1 value2’  

sybase: how to retrieve current date in a specific format

To retrieve current date in sybase, simply call “getdate()” as in: select getdate() Thinks far more interesting when you make usage of the “convert()” function as well. For instance, to display current date in the following format: yyyymmdd (with leading zero for both month and day), simply proceed as follow: select convert(char(8), getdate(), 112) //output: 20111208 if today is Dec. 8th, 2011 sources