#oracle

How to find diskspace used of objects with Oracle

Published 19th Jun 2014 // #oracle

How to find diskspace used of objects with Oracle select owner, segment_name,sum(bytes/1024/1024) from dba_segments group by owner, segment_name order by sum(bytes/1024/1024) desc If you only need the diskspace by owner : select owner,sum(bytes/1024/1024) from dba_segments group by owner order by sum(bytes/1024/1024) desc Source : https://community.oracle.com/thread/2364575?tstart=0

How to find long running queries with Oracle

Published 3rd Mar 2014 // #oracle

How to find long running queries with Oracle select elapsed_time/1000000 seconds, gv$sql.* from gv$sql order by elapsed_time desc; If you need to check the content of the placeholders (:paramX) then try this query : select * from v$sql_bind_capture bc where bc.sql_id = 'SQL ID' and bc.child_number = XXXX Source : Stackoverflow but lost the direct link.

Enable Oracle DBConsole 10g with Windows Server 2008 R2

Published 22nd Jul 2013 // #oracle #windows

Enable Oracle DBConsole 10g with Windows Server 2008 R2 Use theses links : http://oracle-mssql-dba.blogspot.fr/2012/05/oracle-10gr2reconfigure-data-control-by.html http://linuxtechres.blogspot.fr/2011/08/how-to-recreate-em-repository-for.html http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=47298&DestinationA=RSS

Oracle listener / tnsnames

Published 11th Feb 2013 // #oracle

Oracle listener / tnsnames If you have some difficulties getting around those files : http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

Error ORA-12516 with Oracle XE with lots of connections

Published 15th May 2012 // #oracle

Error ORA-12516 with Oracle XE with lots of connections Oracle XE 10g has no hard limit on the number of processes but by default it is limited to 20 or 40 processes. You can change it : Start sqlplus and connect to your database using system user Execute the following code : alter

Some useful queries / tricks around Oracle Materialized Views

Published 28th Mar 2012 // #oracle

Some useful queries / tricks around Oracle Materialized Views Get all materialized views select * from user_mviews Get latest refresh times for all materialized views select * from user_mview_refresh_times Get information on a log select count(*) from mlog$_MyTable; Get the list of all materialized views on a view log SELECT master, owner, NAME, snapshot_site, TO_CHAR(current_snapshots,'mm/dd/yyyy hh24:mi') current_snapshots FROM user_registered_snapshots,…

How to force an update of Oracle's statistics

Published 10th Mar 2012 // #oracle

How to force an update of Oracle's statistics exec dbms_stats.gather_schema_stats( ownname => '&ownername',estimate_percent => 20, method_opt => 'for all columns size auto',options => 'Gather' ,cascade => true,degree => 4); replace &ownername by the name of the schema you want to update.

How to find unindexed foreign key with Oracle

Published 28th Dec 2011 // #oracle

How to find unindexed foreign key with Oracle All credits to : this post. Here is the script : SELECT table_name, constraint_name, cname1 || NVL2 (cname2, ',' || cname2, NULL)…

How to install Oracle XE 10g with Windows 2008

Published 24th Oct 2011 // #oracle #windows

How to install Oracle XE 10g with Windows 2008 Easy ... : right click on your OracleXE.exe file select properties select tab compatibility and set it to Windows 2003 Sp1 click the checkbox run as administrator you are ready to install Source : https://forums.oracle.com/forums/thread.jspa?messageID=2711385

Oracle et les fonctions analytiques et autre fonctions à connaitre

Published 20th Oct 2011 // #oracle

Oracle et les fonctions analytiques et autre fonctions à connaitre Les fonctions analytiques Je me suis rendu compte qu'elles étaient assez peu connues (bien que très pratiques) et comme j'ai trouvé un très bon tutoriel sur les fonctions du style AVG, LAG et LEAD, CORR, MAX et MIN, RATIO_TO_REPORT, STDDEV et VARIANCE,…

Install Oracle 10g on Centos 5.6

Published 17th Jun 2011 // #centos #oracle #tips

Install Oracle 10g on Centos 5.6 See : http://ivan.kartik.sk/oracle/install_ora10gR2_redhat.html The only thing you have to change is the startup scripts that does not correctly stop the service. With Centos it seems that the Kill script is not called if there is no file in /var/lock/subsys/. Instead you can use this one :…

Comment faire pour que Sqlplus s'arrête en cas d'erreur

Published 28th May 2011 // #oracle

Comment faire pour que Sqlplus s'arrête en cas d'erreur Dans le cas de requête automatique (notamment d'intégration continue) il est intéressant d'arrêter le script à la première erreur ce qui n'est pas le mode de fonctionnement par défaut de sqlplus. Il suffit d'ajouter en début du script à exécuter : WHENEVER OSERROR

Sqlplus en mode console et les caractères accentués

Published 27th May 2011 // #oracle

Sqlplus en mode console et les caractères accentués Invite de commande Windows Il faut savoir que l'encodage de caractères dans une invite de commande Windows n'est pas la même que dans Windows (CP850 en ligne de commande et Win1252 pour le reste). Donc si vous voulez voir correctement les caractères spéciaux il…

How to get your command history in Sqlplus with Linux

Published 19th May 2011 // #oracle #tips

How to get your command history in Sqlplus with Linux Install rlwrap aptitude install rlwrap Add an alias to your profile alias sqlplus='rlwrap sqlplus' Reload your profile and enjoy

Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr

Published 17th May 2011 // #oracle #tips

Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr Edit the file $ORACLE_HOME/bin/dbstart to change this line ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle to ORACLE_HOME_LISTNER=$ORACLE_HOME More info here.

Error when starting Oracle Db Console (EMCTL)

Published 17th May 2011 // #oracle #tips

Error when starting Oracle Db Console (EMCTL) Error : OC4J Configuration issue. /opt/oracle/102/oc4j/j2ee/OC4J_DBConsole_oracle_NET not found. My SID is NET but the previous file does not exist and is replaced by OC4J_DBConsole_oracle_net. So simply rename the file to what he wants. More information here.

How to get the list of locks with Oracle

Published 15th Mar 2011 // #oracle #tips

How to get the list of locks with Oracle Detection To be executed as SYSTEM : select session_id , serial# , c.status , substr(oracle_username,1,20) User_ORA , os_user_name User_os , substr(object_name,1,20) Objet ,…

How to get the list of open session for an username

Published 15th Mar 2011 // #oracle #tips

How to get the list of open session for an username To be executed as SYSTEM : select username, osuser, machine, terminal, status from v$session where username = 'MYUSER'

How to install Oracle 10g with Windows Server 2008 R2 x64

Published 7th Feb 2011 // #oracle #tips #windows

How to install Oracle 10g with Windows Server 2008 R2 x64 Be sure to first start a command prompt with Administrator privilege and start the Oracle installer within this command prompt.

Configuration de Oracle Instant Client sous Windows

Published 25th Oct 2010 // #oracle

Configuration de Oracle Instant Client sous Windows Installation J'ai installé le contenu de ces deux fichiers : instantclient-basic-win32-10.2.0.5.zip instantclient-sqlplus-win32-10.2.0.5.zip dans le répertoire c:\oracle. J'ai ensuite ajouté mes fichiers : tnsnames.ora slqnet.ora dans ce même répertoire Configuration Il ne reste plus qu'à mettre à jour les variables d'environnement : Aller dans les propriétés du Poste de travail Onglet Avancé Cliquer sur Variables d'environnement Modifier la