emctl start dbconsole
Category: Oracle
stop_server.sh
#!/bin/bash hostname uptime echo ===== arret Oracle ===== echo ----- ps oracle ------ ps -ef | grep listener | grep -v grep ps -ef | grep smon | grep -v grep echo ----- uptime uptime echo ----- stop database su -l ora102 -l /exploit/scripts/stop_oracle.sh
echo ===== arret Mysql====== echo ----- ps mysqld ------ ps -ef | grep mysqld | grep -v grep echo ----- uptime uptime echo ----- stop database /exploit/scripts/my_stop_database.sh echo ----- tempo 600 sleep 600
echo ----- check process oracle ps -ef | grep listener | grep -v grep ps -ef | grep smon | grep -v grep
echo ----- check process mysql ps -ef | grep mysqld | grep -v grep
echo ----- halt halt
start_server.sh
#!/bin/bash hostname echo ===== start Oracle ===== echo ----- start database su -l ora102 -l /exploit/scripts/start_oracle.sh echo ----- ps oracle ------ ps -ef | grep lsnr | grep -v grep ps -ef | grep smon | grep -v grep
echo ===== start Mysql====== echo ----- ps mysqld ------ ps -ef | grep mysqld | grep -v grep echo ----- uptime uptime echo ----- start database /exploit/scripts/my_start_database.sh
echo ----- check process mysql ps -ef | grep mysqld | grep -v grep
stop_listener.sh
#!/bin/bash # ================================================== # Nom : stop_listener.sh # Objet : arret du listener # Cree le : 28/01/2010 # Syntaxe : stop_listener.sh # ==================================================
# ---------------------------------- # Fonction d'affichage de la syntaxe # ---------------------------------- syntaxe() { echo "Usage : $0" }
lsnrctl status lsnrctl stop
start_listener.sh
#!/bin/bash # ================================================== # Nom : start_listener.sh # Objet : demarrage du listener # Cree le : 28/01/2010 # Syntaxe : start_listener.sh # ==================================================
# ---------------------------------- # Fonction d'affichage de la syntaxe # ---------------------------------- syntaxe() { echo "Usage : $0" }
lsnrctl start lsnrctl status
stop_oracle.sh
#!/bin/bash # ================================================== # Nom : stop_oracle.sh # Objet : arret des listeners et bases de donnees # Cree le : 28/01/2010 # Syntaxe : stop_oracle.sh # ==================================================
# ---------------------------------- # Fonction d'affichage de la syntaxe # ---------------------------------- syntaxe() { echo "Usage : $0" }
/exploit/scripts/stop_listener.sh /exploit/scripts/stop_database.sh KTP # /exploit/scripts/stop_database.sh STRPRD /exploit/scripts/stop_database.sh KTPTST /exploit/scripts/stop_database.sh HYPDEV /exploit/scripts/stop_database.sh HYPPROD /exploit/scripts/stop_database.sh EPS /exploit/scripts/stop_database.sh BIPROD /exploit/scripts/stop_database.sh ABEL /exploit/scripts/stop_database.sh BITEST /exploit/scripts/stop_database.sh SISDEV
start_oracle.sh
#!/bin/bash # ================================================== # Nom : start_oracle.sh # Objet : demarrage des listeners et bases de donnees # Cree le : 28/01/2010 # Syntaxe : start_oracle.sh # ==================================================
# ---------------------------------- # Fonction d'affichage de la syntaxe # ---------------------------------- syntaxe() { echo "Usage : $0" }
sh /exploit/scripts/start_listener.sh sh /exploit/scripts/start_database.sh KTP # sh /exploit/scripts/start_database.sh STRPRD sh /exploit/scripts/start_database.sh KTPTST sh /exploit/scripts/start_database.sh HYPDEV sh /exploit/scripts/start_database.sh HYPPROD sh /exploit/scripts/start_database.sh EPS sh /exploit/scripts/start_database.sh BIPROD sh /exploit/scripts/start_database.sh ABEL sh /exploit/scripts/start_database.sh BITEST sh /exploit/scripts/start_database.sh SISDEV
Convert secondes en heures minutes
en SQL Server
9294 s ==> 2.5816666 h ===> 02:34::53
SELECT
ElapsedTimeSeconds
,(CAST ( ElapsedTimeSeconds AS decimal(10,2))/ 3600)
,RIGHT(‘0’ + CAST (FLOOR((CAST ( ElapsedTimeSeconds AS decimal(10,4))/ 3600)) AS VARCHAR), 2) + ‘:’ +
RIGHT(‘0’ + CAST(FLOOR(((((CAST ( ElapsedTimeSeconds AS decimal(10,2))/ 3600) * 3600) % 3600) / 60)) AS VARCHAR), 2) + ‘:’ +
RIGHT(‘0’ + CAST (FLOOR(((CAST ( ElapsedTimeSeconds AS decimal(10,2))/ 3600) * 3600) % 60) AS VARCHAR), 2)
from BEDB.dbo.JobHistorySummary
where TaskTypeID = 200
9294 s ==> 2.34
SELECT
ElapsedTimeSeconds
,(CAST ( ElapsedTimeSeconds AS decimal(10,2))/ 3600)
,RIGHT(‘0’ + CAST (FLOOR((CAST ( ElapsedTimeSeconds AS decimal(10,4))/ 3600)) AS INT), 2) + ‘.’ +
RIGHT(‘0’ + CAST(FLOOR(((((CAST ( ElapsedTimeSeconds AS decimal(10,2))/ 3600) * 3600) % 3600) / 60)) AS VARCHAR), 2) –+ ‘:’ +
–RIGHT(‘0′ + CAST (FLOOR(((CAST ( ElapsedTimeSeconds AS decimal(10,2))/ 3600) * 3600) % 60) AS VARCHAR), 2)
–,DATEADD(mi, (ElapsedTimeSeconds – FLOOR(ElapsedTimeSeconds)) * 60, DATEADD(hh, FLOOR(ElapsedTimeSeconds), CAST (’00:00:00’ AS TIME)))
from BEDB.dbo.JobHistorySummary
where TaskTypeID = 200
SQLPlus fichier CSV
Options de formatage des résultats
Ici se trouve une présentation très détaillée.
1. Largeur des colonnes
Un formatage élémentaire consiste à donner une largeur aux colonnes, comme ceci :
set recsep off (pour éviter qu’il n’y ait quelque chose entre chaque ligne, le quelque chose étant défini par la variable recsepchar)
column format a20 (la colonne aura 20 caractères de large)
puis entrer la requête.
nom_de_colonne est égal au nom des champs qu’on sélectionne.
Quel est l’équivalent du AS sur sql server ?
Réponse : c’est la commande column heading
Par exemple : column doris_key heading exemplaire
2. Largeur des lignes
La commande set linesize permet de limiter la largeur de la page.
Attention, il faut que la somme des largeurs de colonnes n’excède pas la largeur de la ligne ! Sinon, sqlplus reprend son formatage par défaut.
Où se trouve le fichier de formatage par défaut ?
Réponse : en tapant la commande show all, on a tout le formatage par défaut.
On peut sauvegarder tous ces paramètres dans un fichier en envoyant la commande store set
Voici le contenu de ce fichier.
Au démarrage, sqlplus lit le contenu du fichier glogin.sql.
Voici le contenu de ce fichier.
Ce fichier est dans : oraclev8:app:oracle:product:8.1.7:sqlplus:admin
Si on veut conserver un paramétrage pour toutes les futures utilisations de sqlplus, il faut les enregistrer dans un fichier login.sql placé dans le répertoire de base : à essayer.
3. Longueur des pages
La commande set pagesize permet de préciser tous les combiens de lignes va revenir le titre des colonnes. Dans le paramétrage par défaut, cette variable est à 14, c’est pour ça que les noms des colonnes reviennent si souvent !
Pour supprimer tout retour des titres de colonnes, taper set pagesize 0
Formatage pour csv
1. Enregistrer les options de formatage dans un fichier externe qu’on appellera avec @. Voici par exemple le contenu d’un fichier csv.sql :
set LIN 1000 set HEADING OFF set TRIMSPOOL ON set PAGESIZE 0 set EMBEDDED OFF set HEADS OFF set NEWP NONE set ECHO OFF set FEEDBACK OFF set SPACE 0 set COLSEP " " set TAB OFF
2. Avant de lancer la requête, faire spool .csv (pas besoin d’indiquer tout le chemin si on lance sqlplus dans le même répertoire où l’on veut envoyer le fichier).
3. Appeler le fichier de formatage : @csv.sql
4. Taper la requête en faisant une concaténation des colonnes comme ceci :
select col1||’;’||col2 from
5. Taper spool off
Signification des réglages :
TRIMSPOOL ON : suppression des espaces en fin de ligne
TAB OFF : non utilisation des tabulations
EMBEDDED OFF : pas de contrôle sur l’endroit de la page où doit commencer le rapport
HEADING OFF : en-tête du résultat (= nom des colonnes)
Nombre de session sur chaque machine.
Epoch
http://www.epochconverter.com/
What is epoch time?
The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but ‘epoch’ is often used as a synonym for ‘Unix time’. Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038).
Human readable time | Seconds |
1 hour | 3600 seconds |
1 day | 86400 seconds |
1 week | 604800 seconds |
1 month (30.44 days) | 2629743 seconds |
1 year (365.24 days) | 31556926 seconds |
How to get the current epoch time in …
PHP | time() more … |
Python | import time first, then time.time() |
Ruby | Time.now (or Time.new ). To display the epoch: Time.now.to_i |
Perl | time more … |
Java | long epoch = System.currentTimeMillis()/1000; |
Microsoft .NET C# | epoch = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000; |
Objective-C | [[NSDate date] timeIntervalSince1970]; (returns double) or NSString *currentTimestamp = [NSString stringWithFormat:@"%f", [[NSDate date] timeIntervalSince1970]]; |
VBScript/ASP | DateDiff("s", "01/01/1970 00:00:00", Now()) |
Delphi | Epoch := DateTimetoUnix(Now); Tested in Delphi 2010. |
R | as.numeric(Sys.time()) |
Erlang | calendar:datetime_to_gregorian_seconds(calendar:now_to_universal_time( now()))-719528*24*3600. |
MySQL | SELECT unix_timestamp(now()) more … |
PostgreSQL | SELECT extract(epoch FROM now()); |
Oracle PL/SQL | SELECT (SYSDATE - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) * |
SQL Server | SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) |
JavaScript | Math.round(new Date().getTime()/1000.0) getTime() returns time in milliseconds. |
Tcl/Tk | clock seconds |
Unix/Linux Shell | date +%s |
PowerShell | Get-Date -UFormat "%s" Produces: 1279152364.63599 |
Other OS’s | Command line: perl -e "print time" (If Perl is installed on your system)
|
Convert from human readable date to epoch
PHP | strtotime("15 November 2012") (converts most English date texts) or:date_create('01/15/2010')->format('U') (PHP5 DateTime class) more … |
Python | import time first, then int(time.mktime(time.strptime('2000-01-01 12:34:00', '%Y-%m-%d %H:%M:%S'))) - time.timezone |
Ruby | Time.local(year, month, day, hour, minute, second, usec ) (or Time.gm for GMT/UTC input). To display add .to_i |
Perl | Use the Perl Epoch routines |
Java | long epoch = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse("01/01/1970 01:00:00").getTime() / 1000; |
VBScript/ASP | DateDiff("s", "01/01/1970 00:00:00", time field) |
Delphi | Epoch := DateTimeToUnix(StrToDateTime(myString)); |
C | Use the C Epoch Converter routines |
R | as.numeric(as.POSIXct("MM/dd/yyyy HH:mm:ss", origin="1970-01-01")) |
MySQL | SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDDMore on using Epoch timestamps with MySQL |
PostgreSQL | SELECT extract(epoch FROM date('2000-01-01 12:34')); With timestamp: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); With interval: SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); |
SQL Server | SELECT DATEDIFF(s, '1970-01-01 00:00:00', time field) |
JavaScript | Use the JavaScript Date object |
Unix/Linux Shell | date +%s -d"Jan 1, 1980 00:00:01" Replace ‘-d’ with ‘-ud’ to input in GMT/UTC time. |
Convert from epoch to human readable date
PHP | date(output format, epoch); Output format example: ‘r’ = RFC 2822 date more … |
Python | import time first, then time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.localtime(epoch)) Replace time.localtime with time.gmtime for GMT time. |
Ruby | Time.at(epoch) |
Perl | Use the Perl Epoch routines |
Java | String date = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000)); |
VBScript/ASP | DateAdd("s", epoch, "01/01/1970 00:00:00") |
Delphi | myString := DateTimeToStr(UnixToDateTime(Epoch)); Where Epoch is a signed integer. |
C | Use the C Epoch Converter routines |
Objective-C | NSDate * myDate = [NSDate dateWithTimeIntervalSince1970:epoch]; NSLog(@"%@", date); |
R | as.POSIXct(epoch, origin="1970-01-01", tz="GMT") |
MySQL | FROM_UNIXTIME(epoch, optional output format) Default output format is YYY-MM-DD HH:MM:SS. If you need support for negative timestamps: DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),"%Y-%m-%d") (replace -315619200 with epoch) more … |
PostgreSQL | PostgreSQL version 8.1 and higher: SELECT to_timestamp(epoch); Older versions: SELECT TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second'; |
Oracle PL/SQL | SELECT to_date('01-JAN-1970','dd-mon-yyyy')+(1326357743/60/60/24) from dual Replace 1326357743 with epoch. |
SQL Server | DATEADD(s, epoch, '1970-01-01 00:00:00') |
Microsoft Excel | =(A1 / 86400) + 25569 Format the result cell for date/time, the result will be in GMT time (A1 is the cell with the epoch number). For other time zones: =((A1 +/- time zone adjustment) / 86400) + 25569. |
Crystal Reports | DateAdd("s", {EpochTimeStampField}-14400, #1/1/1970 00:00:00#) -14400 used for Eastern Standard Time. See Time Zones. |
JavaScript | Use the JavaScript Date object |
Tcl/Tk | clock format 1325376000 |
Unix/Linux Shell | date -d @1190000000 Replace 1190000000 with your epoch, needs recent version of ‘date’. Replace ‘-d’ with ‘-ud’ for GMT/UTC time. |
PowerShell | Function get-epochDate ($epochDate) { [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($epochDate)) } , then use: get-epochDate 1279152364 . Works for Windows PowerShell v1 and v2 |
Other OS’s | Command line: perl -e "print scalar(localtime(epoch))" (If Perl is installed) Replace ‘localtime’ with ‘gmtime’ for GMT/UTC time. |
CREATE [OR REPLACE] DIRECTORY directory AS ‘pathname’
Pour afficher les DIRECTORY
CREATE DIRECTORY
Create a directory – an alias for a filesystem pathname – used to store LOBs.
Syntax:
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
Example
Create or replace DIRECTORY dir_test as ‘c:\test’;
declare
MyFile utl_file.file_type;
begin
MyFile := utl_file.fopen(‘DIR_TEST’, ‘some_filename.txt’, ‘w’);
utl_file.put_line(MyFile, ‘Here is some example text’);
utl_file.put_line(MyFile, ‘Another line of text’);
utl_file.fclose(MyFile);
end;
/
exemple :
CREATE OR REPLACE DIRECTORY DBA_DIR AS ‘C:\Batch’;
GRANT READ,WRITE ON DIRECTORY DBA_DIR TO OPS$DBO;
Pour lister les directories
select OWNER,DIRECTORY_NAME,DIRECTORY_PATH
from DBA_DIRECTORIES
order by OWNER,DIRECTORY_NAME;
Aide spooler SQPlus
Paramètres pour le spool SQLplus.
set tab off | Pas de tabulation : |
set trims off | pas d’espace à la fin de la ligne |
set line 100 | longueur de ligne de 100 |
set heading on | affiche les entêtes de colonne |
set newpage 10 | créé une nouvelle page toutes les 10 lignes |
set pagesize 0 | |
set feedback off | compte le nombre d’enregistrement |
set colsep « ; » | séparateur de colonne ; |
set termout off | |
set pause « tapez RETURN pour continuer « | attente d’une intervention clavier |
set sqlnumber off | supprime les numéros de ligne SQL |
set sqlprefix off |
SET TERM OFF -- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
— ECHO = ON will Display the command on screen (+ spool)
— ECHO = OFF will Display the command on screen but not in spool files.
— Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
— TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
— TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
— HEADING = OFF will hide column headings
SET FEEDBACK OFF
— FEEDBACK = ON will count rows returned
SET PAUSE OFF
— PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
— PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80
— LINESIZE = width of page (80 is typical)
SET VERIFY OFF
— VERIFY = ON will show before and after substitution variables
— Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
—
— The rest of the SQL commands go here
—
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
Aide spooler SQPlus
Paramètres pour le spool SQLplus.
set tab off | Pas de tabulation : |
set trims off | pas d’espace à la fin de la ligne |
set line 100 | longueur de ligne de 100 |
set heading on | affiche les entêtes de colonne |
set newpage 10 | créé une nouvelle page toutes les 10 lignes |
set pagesize 0 | |
set feedback off | compte le nombre d’enregistrement |
set colsep « ; » | séparateur de colonne ; |
set termout off | |
set pause « tapez RETURN pour continuer « | attente d’une intervention clavier |
set sqlnumber off | supprime les numéros de ligne SQL |
set sqlprefix off |
SET TERM OFF -- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
— ECHO = ON will Display the command on screen (+ spool)
— ECHO = OFF will Display the command on screen but not in spool files.
— Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
— TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
— TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
— HEADING = OFF will hide column headings
SET FEEDBACK OFF
— FEEDBACK = ON will count rows returned
SET PAUSE OFF
— PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
— PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80
— LINESIZE = width of page (80 is typical)
SET VERIFY OFF
— VERIFY = ON will show before and after substitution variables
— Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
—
— The rest of the SQL commands go here
—
SELECT * FROM GLOBAL_NAME;
SPOOL OFF