Skip to content

Instantly share code, notes, and snippets.

@DaffyDuke

DaffyDuke/Oracle.md

Created March 4, 2017 15:01
Show Gist options
  • Save DaffyDuke/88c04848dd58d13d8871109446a6b611 to your computer and use it in GitHub Desktop.
Save DaffyDuke/88c04848dd58d13d8871109446a6b611 to your computer and use it in GitHub Desktop.
Download ZIP
Administration Base de Données
Raw
Oracle.md

Lister les objets dans un schéma

select object_name,object_type from user_objects;

Trouver le propriétaire d'une table

select owner,table_name from dba_tables where table_name = '$TABLE';

Voir les tablespaces et leurs index

select tablespace_name ,file_name,bytes/(1024*1024) \"TAILLE EN MO\" from dba_data_files order by tablespace_name,file_name;

faire des select exportable sous excel .....

set show off
ttitle off
set heading off
set pagesize 999
set linesize 250
set pause off time on verify off feedback off trims on
spool /tmp/result
select UTI_ID||';'||PRO_ID||';'||LNG_ID||';'||UTI_CIVILITE||';'||UTI_NOM_MEDIA||';'||UTI_FONCTION||';'||UTI_NOM||';'||UTI_PRENOM||';'||UTI_EMAIL||';'||UTI_CARTE_PRESSE_NUM||';'||UTI_NUMRUE||';'||UTI_ADRESSE1||';'||UTI_ADRESSE2||';'||UTI_CODEPOSTAL||';'||UTI_VILLE||';'||UTI_TELEPHONE||';'||UTI_LOGIN||';'||UTI_PAYS||';'||UTI_IPN||';'||UTI_NEWSLETTER||';'||UTI_PASSWORD||';'||UTI_ACCREDITE||';'||TME_ID||';' from TD_UTILISATEUR where UTI_ACCREDITE='N';
spool off;
quit

Ressources

SQL> set line 132
SQL> select * from v$resource_limit;
SQL> show sga
SQL> select * from v$sag

Voir les comptes système oracle

SQL> select USERNAME, ACCOUNT_STATUS from dba_users;
USERNAME                      ACCOUNT_STATUS
------------------------------ --------------------------------
OUTLN                         OPEN
CSMIG                         OPEN
DELPHINE                      OPEN
MGMT_VIEW                     OPEN
PJ                            OPEN
ALPHA                         OPEN
SYS                           OPEN
SYSTEM                        OPEN
DBSNMP                        OPEN
LBO                           OPEN
SYSMAN                        OPEN
USERNAME                      ACCOUNT_STATUS
------------------------------ --------------------------------
DIP                           EXPIRED & LOCKED
ORACLE_OCM                    EXPIRED & LOCKED
TSMSYS                        EXPIRED & LOCKED

Max_connect

augmenter les param process et sessions.

sessions=process+35

sqlplus /nolog

Connect / as sysdba

alter system  set processes=300 scope=spfile;


alter system  set sessions=335 scope=spfile;

restart bdd

verif

sqlplus /nolog

Connect / as sysdba

show parameter;

Shrink volumétrie

http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/

SQL> alter database datafile '/oradata1/ORADB2/data_db_act5_1.dbf' resize 2000M ;


Database altered.

Quelques requetes :

select file_id, bytes from dba_free_space where tablespace_name ='DATA_DB_ACT3';


SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES;


SELECT * FROM DBA_DATA_FILES;

SQL> select t.name tabname, d.name dataname from v$datafile d,
SQL> v$tablespace t where t.ts#=d.ts#;

TABNAME
------------------------------
DATANAME
--------------------------------------------------------------------------------
SYSTEM
/database/ORADTES3/system01.dbf
UNDOTBS1
/database/ORADTES3/undotbs01.dbf
SYSAUX
/database/ORADTES3/sysaux01.dbf
TABNAME
------------------------------
DATANAME
--------------------------------------------------------------------------------
DATA_TES_ACT
/database/ORADTES3/data_tes_act01.dbf
DATA_TES_ACT2
/database/ORADTES3/DATA_TES_ACT2.dbf
DATA_TES_ACT3
/database/ORADTES3/DATA_TES_ATC3.dbf
TABNAME
------------------------------
DATANAME
--------------------------------------------------------------------------------
DATA_TES_ACT4
/oradata1/ORADTES3/data_tes_act4_1.dbf
DATA_TES_ACT5
/oradata1/ORADTES3/data_tes_act5_1.dbf
DATA_TES_ACT
/oradata1/ORADTES3/data_tes_act_2.dbf
TABNAME
------------------------------
DATANAME
--------------------------------------------------------------------------------
DATA_TES_ACT2
/oradata1/ORADTES3/data_tes_act2_2.dbf
D_MIGRDELP
/oradata1/ORADTES3/data_migrdelp1.dbf
DATA_TES_ACT
/oradata1/ORADTES3/data_tes_act_3.dbf

12 rows selected.

SQL> !df -h

Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p7 1012M 208M 753M 22% /
/dev/cciss/c0d0p1 99M 14M 81M 14% /boot
/dev/cciss/c0d0p5 1012M 34M 927M 4% /tmp
/dev/cciss/c0d0p2 2.0G 958M 955M 51% /usr
/dev/cciss/c0d0p6 1012M 74M 887M 8% /var
/dev/cciss/c0d0p8 9.9G 7.2G 2.7G 74% /oracle
/dev/cciss/c0d0p10 43G 39G 3.3G 93% /database
none 2.0G 0 2.0G 0% /dev/shm
/dev/mapper/vg01-oradata1

68G 57G 11G 84% /oradata1



SQL> !ls -l /oradata1/ORADTES3/data_tes_act5_1.dbf
-rw-r----- 1 oracle oinstall 10737426432 Jul 27 12:11 /oradata1/ORADTES3/data_tes_act5_1.dbf

SQL> alter database datafile '/oradata1/ORADTES3/data_tes_act5_1.dbf'
SQL> resize 5G;

Database altered.

SQL> !ls -l /oradata1/ORADTES3/data_tes_act5_1.dbf
-rw-r----- 1 oracle oinstall 5368717312 Jul 27 14:53 /oradata1/ORADTES3/data_tes_act5_1.dbf

SQL> !ls -l /database/ORADTES3/
total 20455576
-rw-r----- 1 oracle oinstall 5242888192 Jul 27 14:48 DATA_TES_ACT2.dbf
-rw-r----- 1 oracle oinstall 5242888192 Jul 27 14:53 DATA_TES_ATC3.dbf
-rw-r----- 1 oracle oracle 14368768 Jul 27 14:54 control01.ctl
-rw-r----- 1 oracle oracle 14368768 Jul 27 14:54 control02.ctl
-rw-r----- 1 oracle oracle 7654612992 Jul 27 14:29 data_tes_act01.dbf
-rw-r----- 1 oracle oracle 104858112 Jul 27 12:06 redo01.log
-rw-r----- 1 oracle oracle 104858112 Jul 27 14:54 redo02.log
-rw-r----- 1 oracle oracle 104858112 Jul 26 22:00 redo03.log
-rw-r----- 1 oracle oracle 314580992 Jul 27 14:09 sysaux01.dbf
-rw-r----- 1 oracle oracle 314580992 Jul 27 14:54 system01.dbf
-rw-r----- 1 oracle oracle 602939392 Jul 26 22:01 temp01.dbf
-rw-r----- 1 oracle oracle 1211113472 Jul 27 14:54 undotbs01.dbf

Voir la volumétrie

set sqlprompt " "
set trimout off
set echo off
set linesize 110
set pagesize 66
set FEEDBACK off
column tablespace_name format A22 heading 'Tablespaces'
column sbytes format 999,999,999,999 heading 'Total (Mo)' ;
column lbytes format 999,999,999,999 heading 'Libre (Mo)' ;
column obytes format 999,999,999,999 heading 'Occupe (Mo)' ;
column percent format 999.99 heading ' % ' ;
break on report
compute sum label TOTAL of sbytes obytes lbytes on report
  select
       tmp.tablespace_name,
       round(tmp.total/(1024*1024)) sbytes,
       round(tmp.total/(1024*1024) - (fs.TOTAL_BYTES)/(1024*1024)) obytes,
       round(fs.TOTAL_BYTES/(1024*1024)) lbytes,
       round((1- fs.TOTAL_BYTES/tmp.total )*100) percent
  from
   (select
      df.tablespace_name,
      sum(dff.bytes) total
    from dba_data_files df, v\$datafile dff
    where df.FILE_NAME = dff.NAME
    group by df.tablespace_name) tmp, dba_free_space_coalesced fs
  where tmp.tablespace_name = fs.tablespace_name
  order by 5 desc;
set head on
col dataname format A60 heading 'datafile'
col tabname format A20 heading 'tablespace'
select t.name tabname, d.name dataname  from v\$datafile d, v\$tablespace t
where t.ts#=d.ts#;

Créer un user

SQL> create tablespace D_MIGRDELP datafile '/oradata1/ORADTES2/data_migrdelp1.dbf' size 2G autoextend on;

Tablespace created.

SQL> create user MIGRDELP identified by "MIGRDELP"  default tablespace D_MIGRDELP temporary tablespace TEMP;

User created.

Lister les index d'une BDD Oracle :

select index_name from all_indexes;

Supprimer un index :

drop index "DELPHINE_INDEX1_TEST";

Lister les sequences :

select SEQUENCE_NAME from all_sequences;

Lister les triggers :

select TRIGGER_NAME from all_triggers;

Desactiver un trigger :

alter trigger "TES_ALP"."TRIGGERDELPHINETEST" disable

Lister les contraintes d'une table :

select CONSTRAINT_NAME from all_constraints where TABLE_NAME='DELPHINE_KEYS_TEST2';

Désactiver une contrainte sur une table :

alter table <TABLE> disable  constraint <NOM DE LA CONTRAINTE>

alter table TES_ACT.DELPHINE_KEYS_TEST2 disable  constraint "TES_ACT.DELPHINE_PK_TEST";

Suppression de contrainte :

alter table "DELPHINE_KEYS_TEST" drop constraint "SYS_C0015937";

Ménage archivelogs

sqlplus / as sysdba

show parameter log_archive_dest_1

show parameter recovery

exit

rman target / nocatalog

delete noprompt archivelog until time 'SYSDATE - 15'; exit
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment