Administration Base de Données
select object_name,object_type from user_objects;
select owner,table_name from dba_tables where table_name = '$TABLE';
select tablespace_name ,file_name,bytes/(1024*1024) \"TAILLE EN MO\" from dba_data_files order by tablespace_name,file_name;
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
SQL> set line 132
SQL> select * from v$resource_limit;
SQL> show sga
SQL> select * from v$sag
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
sessions=process+35
sqlplus /nolog
Connect / as sysdba
alter system set processes=300 scope=spfile;
alter system set sessions=335 scope=spfile;
sqlplus /nolog
Connect / as sysdba
show parameter;
http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/
SQL> alter database datafile '/oradata1/ORADB2/data_db_act5_1.dbf' resize 2000M ;
Database altered.
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
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#;
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.
select index_name from all_indexes;
drop index "DELPHINE_INDEX1_TEST";
select SEQUENCE_NAME from all_sequences;
select TRIGGER_NAME from all_triggers;
alter trigger "TES_ALP"."TRIGGERDELPHINETEST" disable
select CONSTRAINT_NAME from all_constraints where TABLE_NAME='DELPHINE_KEYS_TEST2';
alter table <TABLE> disable constraint <NOM DE LA CONTRAINTE>
alter table TES_ACT.DELPHINE_KEYS_TEST2 disable constraint "TES_ACT.DELPHINE_PK_TEST";
alter table "DELPHINE_KEYS_TEST" drop constraint "SYS_C0015937";
sqlplus / as sysdba
show parameter log_archive_dest_1
show parameter recovery
exit
rman target / nocatalog
delete noprompt archivelog until time 'SYSDATE - 15'; exit