Basico Linux/Oracle

Estudo SQL \\\\\\\\\

\\\\\\\\\\\\\\\\\\\\\

## Para enxergar as instancias ativas ##

$ ps -ef | grep [pmon - smon]

## verificar nome de instâncias antigas ##

select * from dba_db_links;

## Enxergar diskgroups ##

set line 300

select * from v$logfile

order by group#;

============================================

============================================

EXERCÍCIO 01 -

Crie 4 tablespaces com 2 datafiles cada sendo:

tamanho inicial de 100MB e extensões de 500MB até o máximo de 30GB.

============================================

============================================

Resposta:

## Criação das tablespaces no caminho /u02/database/MYDATA/ ##

SQL> create tablespace tbspace01

2 datafile '/u02/database/MYDATA/dbfile01.dbf'

3 size 100m

4 autoextend on

5 next 500m maxsize 30000m;

Tablespace created.

=======================================================

## Criação de mais um datafile dentro da tablespace ##

SQL> alter tablespace tbspace04

2 add datafile 'dbfile44.dbf'

3 size 100m

4 autoextend on

5 next 500m maxsize 30000m;

Tablespace altered.

=======================================================

## visualizar as tablespaces ##

SQL> select name from v$tablespace;

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

TBSPACE01

TBSPACE02

TBSPACE03

TBSPACE04

9 rows selected.

========================================================

## visualizar os datafiles ##

SQL> select file_name from dba_data_files where tablespace_name like 'TBS%';

FILE_NAME

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

/u02/database/MYDATA/dbfile01.dbf

/u02/database/MYDATA/dbfile02.dbf

/u02/database/MYDATA/dbfile03.dbf

/u02/database/MYDATA/dbfile04.dbf

/opt/product/11.2.0/oracle/db_1/dbs/dbfile11.dbf

/opt/product/11.2.0/oracle/db_1/dbs/dbfile22.dbf

/opt/product/11.2.0/oracle/db_1/dbs/dbfile33.dbf

/opt/product/11.2.0/oracle/db_1/dbs/dbfile44.dbf

8 rows selected.

OBS: Comando - selecionar os datafiles onde as tablespaces iniciam com TBS

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

============================================

============================================

EXERCÍCIO 02 -

Elimine as tablespaces criadas incluindo seu conteúdo

============================================

============================================

Resposta:

## Deletar tablespace incluindo conteúdo e datafiles ##

SQL> drop tablespace tbspace01

2 including contents

3 and datafiles;

Tablespace dropped.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

============================================

============================================

EXERCÍCIO 03 -

Mover as tablespaces criadas e seu conteúdo para o diretório /u01/database/MYDATA

============================================

============================================

Resposta: DATAFILES, REDO LOGS, TEMP FILES

Baixar a Instância

MV no SO

Startup Mount

alter database rename file '<caminho antigo>indx01.dbf' to '<caminho novo>indx01.dbf';

alter database open

Resposta: CONTROL FILES

alter system set control_files='/u01/database/MYDATA/control01.ctl'