Posts Tagged ‘Unix’
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
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 DBgrep -v OBJECT_NAME | grep -v "-" | grep -v "^$"
: this removes empty lines and useless trace from Oracle SQLPlussed "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 filesort | uniq
: sort lines and remove duplicatesexp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
: exports the content of the table $tableecho "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
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"
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:]'
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 |