ORACLE import,export, tablespaces e gestione utenti

Sblocco di un utente looked:

da sqlplus /nolog
connect sys as sysdba
password: ....

alter user xxxx account unlock;

export da prompt dos:

exp system/xxxx file=protocollo.dmp log=protocollo.log owner=PROTOCOLLO, PROTOCOLLO0, DOCNOPROTO statistics=none consistent=Y buffer=4000000

cancellazione users come utente system o sys di db:

drop user protocollo cascade;
drop user protocollo0 cascade;
drop user prototest cascade;
drop user docnoproto cascade;

resize dei datafiles come utente system o sys di db:

alter database datafile 'D:\ORADATA\ORCL\DOCNOPROTO.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_I_01.DBF' resize 1M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_01.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_02.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_03.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_04.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_05.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_06.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_07.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_08.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_09.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_10.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_11.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_12.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_13.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_14.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_15.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO0_D_16.DBF' resize 10M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_01.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_02.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_03.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_04.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_05.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_06.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_07.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_08.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_09.DBF' resize 2000M;
alter database datafile 'D:\ORADATA\ORCL\PROTO_D_10.DBF' resize 3000M;

creazione degli users come utente system o sys di db:

create user PROTOCOLLO identified by PASSWORD default tablespace PROTO_D quota unlimited on PROTO_d quota unlimited on PROTO_I;
create user PROTOCOLLO0 identified by PASSWORD0 default tablespace PROTO0_D quota unlimited on PROTO0_d;
create user DOCNOPROTO identified by PASSWORD default tablespace DOCNOPROTO quota unlimited on DOCNOPROTO;

import da prompt dos:

imp system/xxxx file=protocollo.dmp log=protocollo.implog fromuser=PROTOCOLLO, PROTOCOLLO0, DOCNOPROTO touser=PROTOCOLLO, PROTOCOLLO0, DOCNOPROTO ignore=Y buffer=4000000

import di solo alcune tabelle:

imp system/xxxx file=protocollo.dmp log=protocollo.implog fromuser=PROTOCOLLO0 touser=PROTOCOLLO0 tables=up0_blob0 ignore=Y buffer=4000000


vedere tutti gli utenti importati:

select * from all_users;

ricompilare un vista che ha dato errore imp-00041 durante l'import (per problemi di dipendenze):

alter view "PROTOCOLLO"."UP0_VIEW_STATISTICHE_TIF" compile;

per provare la vista, ad es.:

select count(*) from "PROTOCOLLO"."UP0_VIEW_STATISTICHE_TIF";


Per cambiare la password di un user occorre utilizzare il comando:

alter user segretario identified by newpassword;

Come rinominare un datafile di una tablespace temporanea:

shutdown; (aspetta che nessun utente sia loggato)
o meglio:

shutdown immediate;

linux> mv 'OLDFILE.DBF ' 'NEWFILE.DBF'

sqlplus: startup mount;

sqlplus: ALTER DATABASE

RENAME file
'/u01/app/oracle/mysid/oldname.dbf'
TO
'/u01/app/oracle/mysid/newname.dbf'

shutdown immediate;

startup;

vedi anche: http://www.dba-oracle.com/t_rename_data_file.htm


vedere tutte le tabelle dell'utente con cui si è collegati:

select table_name from user_tables where rownum<=10; (x limitare la ricerca)


vedere lo spazio utilizzato su disco in tutte le tabelle di uno user:

select table_name, avg_row_len*num_rows/1024/1024 as Mbytes
from user_tables;

vedere lo spazio utilizzato su disco delle tabelle più grosse di uno user:

select table_name, avg_row_len*num_rows/1024/1024 as Mbytes
from user_tables where avg_row_len*num_rows/1024/1024>=10 ;

Dare i privilegi di CONNECT a uno user:

GRANT "CONNECT" TO "PROTOCOLLO0";



-------------------------------------------

Bel sito, con vari script per ORACLE:

http://www.orafaq.com/wiki/Scripts#Space_Management_Scripts

Altro Link utile:

http://www.harmanresearch.com/oracle/#ShowAllDatafilesForTablespaceAndOracleStuff

-------------------------------------------------------------------------------------------

PROCEDURA PER SBLOCCO LOGIN CLIENT

UPDATE UP0_U

SET PASSWORD = ''; /* vedi mail */

WHERE UTENTE = ‘PROTOCOLLO’;

UPDATE UP0_U

SET PASSWORD = ''; /* vedi mail */

WHERE UTENTE = ‘PROTOCOLLO0’;

COMMIT;