Jonathan Lalou's Blog

Posts Tagged ‘Unix’

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

select @@servername

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:
[bash]
#!/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[/bash]

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

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"

Use one of the three following commands:

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

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

Case: you have to sort the content of a file in Unix.

Solution

sort -d sourceFile > destinationFile

Abstract: we need to retrieve all the files in current folder (and its sub-folders) of which size is greater than 10 Mo

Use:

find . -type f -size +10000 -print