Search
Calendar
November 2017
S M T W T F S
« Sep    
 1234
567891011
12131415161718
19202122232425
2627282930  
Your widget title
Archives

Posts Tagged ‘Unix’

PostHeaderIcon Display the Unix server name of a Sybase DB

  • Let’s consider a Sybase server on which you are logged.
  • You need know the Unix server name.
  • Launch the SQL query:

select @@servername

PostHeaderIcon Copy all tables from a schema to another one

Case:

I need copy the content of all tables from one Oracle schema to another one, specifically: I must copy raw data from a production DB to a developper one.

Context:

  • The DBA do not allow DBLinks
  • Oracle source and destination schemas share the same structure (name of the tables, names and types of columns, etc.

Fix:

Here is a small script I wrote, of course it can be improved:

#!/usr/bin/bash
SRC_LOGIN=XYZ
SRC_PASSWORD=XYZ
SRC_DB=production
DBDEST_LOGIN=XYZ
DEST_PASSWORD=XYZ
DEST_DB=developmentDB

echo "select object_name from user_objects where object_type = 'TABLE' order by object_name;" | \
	sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
	grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" | \
	sed "1,9d" | tac | sed "1,3d" | tac | \
	sort | uniq > allTables.txt

for table in `more allTables.txt`
do
rm -rf $table.sql
echo "exporting $table"
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
echo "importing $table"
echo "truncate table $table;" | \
    sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
    buffer=10485867 tables="$table" \
    fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y
rm -rf $table.sql
done

rm -rf allTables.txt

A little more explanations:

  • echo "select object_name from user_objects where object_type = 'TABLE';" | sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB: this allow to retrieve the names of all the tables from the DB
  • grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" : this removes empty lines and useless trace from Oracle SQLPlus
  • sed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones. tac in Unix allow to reverse the content of a file
  • sort | uniq : sort lines and remove duplicates
  • exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $table
  • echo "truncate table $table;" | sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB: truncates destination table. This is useful in case the script must be played more than once, for instance.
  • imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql buffer=10485867 tables="$table" fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y: import the content of the table

Some improvement ideas:

  • handle sequences, triggers, stored procedures, etc.
  • create the complete destination DB owing to source DB, ie do not assume anymore that the DBs share the same structure

PostHeaderIcon Comment supprimer tous les accents, cedilles, etc. d’un texte en francais sous Unix?

Il suffit de lancer la commande suivante, en passant eventuellement en parametre le fichier source (sinon c’est l’entree standard qui sera utilisee):

tr "àçéèêëîïôöùüÂÇÉÈÊËÎÏÔÖÙÜ" "aceeeeiioouuACEEEEIIOOUU"

PostHeaderIcon How to replace all lower case with upper case in Unix?

Use one of the three following commands:

  • tr abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ
  • tr a-z A-Z
  • tr '[:lower:]' '[:upper:]'

PostHeaderIcon Remove duplicated lines from a file

Abstract: you have to remove duplicate lines in one file. For instance, you have a file:

foo1
foo2
foo2

Solution: use this command:

uniq myFile.txt

You’ll get:

foo1
foo2
uniq myFile.txt