Top articles
-
[Space] draw the contents of a datafile - version SVG
HIDDEN CONTENT b_svg.sql --> spool b.svg clear screen drop table majda_segments; drop table majda_extents; drop table majda_free_space; drop table majda_data_files; create table majda_segments tablespace main_tbs as select * from dba_segments; create...
-
[Space] : Free space in all datafiles
HIDDEN CONTENT spool b.html prompt prompt prompt spool off spool b.svg set verify off serveroutput on lines 400 trimspool on...
-
[Optim]: Adjust System statistics Setting
*** HIDDEN CONTENT *** 1)CRON : to collect system statistics on all RAC node or on a single node. 2)SQL : to list collected statistics. 3)EXEL : to draw the graphical to decide the right values to set of any workload system statistics #!/bin/sh #---------------------------------------------------------...
-
[Analyze a query- 1/3] : Dessin Plan execution
HIDDENT CONTENT Input : plan_majda Script SQL: set feed on lines 200 trimspool on drop table pos; create table pos ( id number, parent_id number, x number, y number); truncate table pos; set pages 500 feed off prompt
-
[Perf]: How to check the usefulness of autoextend option
HIDDEN CONTENT How to check if the autoextend option is correctly implemented : 1- Space checking : check maxbytes and increment select tablespace_name, file_name, AUTOEXTENSIBLE, ceil(MAXBYTES/1024/1024)||'M' taille_max, INCREMENT_BY ||'octet' from dba_data_files...
-
[script] export.sh
HIDDEN CONTENT Four input files : ------------------------- export.sh : static ( local server ) export_transfert.param : static majda.cnf : parameter file --> different for every export/import import.sh : static ( local server ) Six output files : --------------------------...
-
[script] rman.sh
HIDDEN CONTENT #!/bin/sh . ~ora102/.bash_profile nbr_arguments=$# arguments=$* rep_backup_database=/data/EXPORT_DATAPUMP/backup_rman/full rep_backup_archivelog=/data/EXPORT_DATAPUMP/backup_rman/archives rep_log=/data/EXPORT_DATAPUMP/backup_rman/logs identifiant=`basename...
-
[Compare structure] : constraints
0/3 : sql.bsq usefull information /* please check this on sql.bsq file -------------------------------- 1 = table check, 2 = primary key, 3 = unique, 4 = referential, 5 = view with CHECK OPTION, 6 = view READ ONLY check 7 - table check constraint associated...
-
[Collection scripts] : Additional scripts for data collection
HIDDEN CONTENT dba_tab_modifications v$bh dbms_stats.GATHER_SYSTEM_STATS dba_segments #-- Majda monitoring 00 * * * * /home/ora102/majda/mod.ksh #00,30 * * * * /home/ora102/majda/mon.ksh 0 * * * * /home/ora102/majda/system_stat_history.sh #mod.sh log=/home/ora102/majda/mod.log...
-
[Graphical Interface ] : Show current database activity
In progress MAJDA Console at Monday May 17, 2010 10:37:49 ================================================= SQL: TEXTE ACCESSED_OBJ LOCKED_OBJ EVENT WAIT_S INST Duree SID USERNAME SPID TEMP_USED_M UNDO_USED_M bfhbb9pp59uap:select s.username, decode(s.status,:"SYS_B_00",:"SYS_B_01",:"...
-
[RMAN] : master rman quickly
HIDDEN CONTENT A- Quelques notions : channel = connection between rman and instance rman can backup : database tablespace datafile archivelog controlfile B- Installation rman : voir article [RMAN] Installation C- Première connexion rman : D- Faire connaissance...
-
[Analyze a query- 2/3] : Info objects query
HIDDEN CONTENT Input : Plan_majda -- 1/4 : plan exécution ( fait ) -- 2/4 : info table/index/columns and their statistics, and historic of statistics -- 3/4 : dessin des la query en mode relation entre tables -- 4/4 : vérification Plan exécution : cardinality,...
-
Forecasting oracle Performance
Salut à tous, Thème ultra-rarissime. Prévenir les performances d'une base de données Oracle Désolée, il y a du racisme dans l'air : Ici, on ne traite que de l'Oracle. Bon, voilà. 1) Comment quantifier les performances d'une application hébérgée dans une...
-
[Pratical] How to list hidden parameters
For RAC environnement, I sill don't know How to see them without connecting directly to the instance ... set lines 200 pages 500 col value for a10 col isdefault for a10 col name for a40 col DESCRIPTION for a60 col SESMOD for a10 col SYSMOD for a10 SELECT...
-
[Supervision] :Current state of database Or Supervision M Console
Que le DBA à qui l'on n'a pas posé cette question, au moins, une fois dans sa vie, me contacte en toute urgence. C'est LA question à laquelle, le DBA n'échappe pas, au moins une fois dans sa vie professionnelle. et pas qu'une seule fois. Mais, en fait,...
-
[Optim]: Setting db_file_multiblock_read_count efficiently
***** HIDDEN CONTENT ***** Used by the optimizer to compute the COST only if the system statistics are not available. a- determine Maxvalue : Steve Adams method : SQL> alter session set tracefile_identifier=dbfmbrc32768; SQL> alter session set events...
-
[Space] Contents of a datafile - version2HTML
HIDDEN CONTENT (***) : le meilleur script pour l'instant DATAFILE [36] /data/backup/datafiles/main_tbs_05.dbf 28644M 20385M Free -- 8252M Used 576M : FREE 21M : [I]AUTOPROD.TEMP_IMPORT_EMAIL_INDX 1M : [T]AUTOPROD.QUESTION_TASK_TMP_LIV 2M : [T]AUTOPROD.ANSWER_TASK_TMP_LIV...
-
[Datapump] : good use of IMPDP
HIDDEN CONTENT 1) clean target schema if FULL import.(check recycle_bin=off) drop user XXXX cascade; 2) clean NOT RUNNING datapump jobs set verify off define job= SYS_EXPORT_SCHEMA_04 exec DBMS_DATAPUMP.STOP_JOB (DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_04','SYS'),1,0);...
-
[Script] import.sh
HIDDENT CONTENT 1) Que faire import.sh : Importe le dumpfile dans la base locale en FULL. Si la taille du dump dépasse 20G, le script recrée les indexes et les constraintes en dehors du impdp Dans tous les cas, le CODE est crée après, pour éviter le message...
-
[Space]: Global size of a table including its lobs and indexes
HIDDEN CONTENT set pages 100 lines 200 feed off col segment_name for a30 col size_M for a10 set verify off define own=AS2CLIENT define tab=SURVEY prompt DETAILS prompt ======= select table_owner, table_name, segment_name, segment_type , ceil(bytes/1024/1024)||'M'...
-
[Datapump] expdp and impdp, good use
HIDDEN CONTENT DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, TABLE_EXPORT_OBJECTS. Each of these views, if queried, will give you a list and short description on the specific paths to object types that you can expect INCLUDE or EXCLUDE to be dependent...
-
[Script] : Useful script for monitoring
HIDDENT CONTENT #--rman backup 00 20 * * 0-5 /home/ora102/TOOLS/DBA/rman_backup.sh archivelog 00 20 * * 6 /home/ora102/TOOLS/DBA/rman_backup.sh full #--backup voting disk 00 14 * * 7 /home/ora102/TOOLS/DBA/votingbak.sh #-- Calcul statistics 00 20 * *...
-
[Compare structure] : indexes
HIDDEN CONTENT 1/3 Should create a table which contains the complete necessary information by row drop table t_ind; create table t_ind ( index_owner varchar2(30), index_name varchar2(30), index_type VARCHAR2(27), table_owner varchar2(30), table_name varchar2(30),...
-
[Compare structure] : Tables & columns
HIDDEN CONTENT --> Compare tables --> Compare column_name ( could be extented simply by adding column_id, data_type, DATA_LENGTH) No matter about position, type, lenght of the columns prompt comparaison TABLES select '&util','Miss', table_name from dba_tables...
-
A usefull spool file
HIDDEN CONTENT spool toto set time on termout on flush on timing on trimspool on echo on ..... spool off hope this help