Comandos Oracle
Comandos básicos:
- sudo su – = Conectar no usuário root
- su – oracle = Conectar no usuário oracle
- show pdbs = Mostrar os PDBs
- sqlplus / as sysdba = Conectar no SQLPLUS
- vi tnsnames.ora = Alterar arquivo TSNAME do Oracle
- lsnrctl status = Verificar o Status do Listener
- ps – ef | grep smon = Verificar os Status dos PDBs
- ALTER SESSION SET CONTAINER= NOME_DO_PDB; = Mudar de sessão do PDB
- chown oracle:oinstall NOME_DO_ARQUIVO = alterar o usuário proprietário do arquivo
- echo $ORACLE_SID = Identificar o SID do banco
- echo $ORACLE_HOME = Identificar o caminho de instalação do Oracle
______________________________________________________________________________________________________________________
Verificar número de sessões conectadas
SELECT COUNT() FROM v$session WHERE status=’ACTIVE’;
Verificar Sessões em espera por I/O (waits) (Pode indicar problemas de performance)
SELECT event, COUNT() FROM v$session_wait GROUP BY event;
Verificar se há travamentos entre sessões
SELECT COUNT(*) FROM dba_blockers;
Ver tarefas que estão demorando
SELECT * FROM v$session_longops WHERE time_remaining > 0;
______________________________________________________________________________________________________________________
Fazer um select das tablespaces no sqlplus: (Select especial para visualizar melhor as tablespaces).
set wrap off
set lines 130
set pages 100
col tablespace_na format a9 heading Contents
col extent_management format a10 heading Extent|management
col allocation_type format a10 heading Allocation|type
col segment_space_management format a10 heading ‘Segment|space|management’
select tablespace_name, block_size, contents, extent_management, allocation_type, segment_space_management
from dba_tablespaces
order by tablespace_name;
______________________________________________________________________________________________________________________
Criar Table Spaces: (Criar linha por linha)
create tablespace datafile size 10G autoextend on next 2048M maxsize 32767M;
create tablespace datafile size 10G autoextend on next 2048M maxsize 32767M;
create tablespace datafile size 10G autoextend on next 2048M maxsize 32767M;
create tablespace datafile size 10G autoextend on next 2048M maxsize 32767M;
create tablespace datafile size 10G autoextend on next 2048M maxsize 32767M;
create tablespace datafile size 10G autoextend on next 2048M maxsize 32767M;
______________________________________________________________________________________________________________________
Visualizar o diretórios do Oracle.
set lines 200
set pages 200
col DIRECTORY_NAME for A40
col DIRECTORY_PATH for A80
col OWNER for A10
select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
______________________________________________________________________________________________________________________
Comando para analisar o Valor Caracter do Banco.
set lines 100
set pages 200
col parameter for a20
col vaue dor 10
col con_id for 999999
select * from v$NLS_PARAMETERS where parameter= ‘NLS_CHARACTERSET’;
______________________________________________________________________________________________________________________
Alterar para a sessão do PDB do banco
ALTER SESSION SET CONTAINER=<PDB_NAME>;
______________________________________________________________________________________________________________________
Expandir uma tablespace.
select file_name,tablespace_name,bytes/1024/1024 MB, autoextensible
from dba_data_files
where tablespace_name = ”
order by tablespace_name;
alter tablespace add datafile ‘+DATA’ size 10m autoextend on next 100m maxsize 32767m;
______________________________________________________________________________________________________________________
Comando para dropar um usuário;
drop user <username> cascade;
drop user <username> cascade;
______________________________________________________________________________________________________________________
Verificar o formato de data no Oracle
Select sysdate from dual;
ALTER SYSTEM SET NLS_DATE_FORMAT=’YYYY/MM/DD’ scope=spfile;
______________________________________________________________________________________________________________________
Verificar o tamanho das tablespaces.
SELECT
df.tablespace_name,
ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS allocated_mb,
ROUND(SUM(CASE
WHEN df.maxbytes = 0 THEN df.bytes
ELSE df.maxbytes
END) / 1024 / 1024, 2) AS max_size_mb,
ROUND(NVL(fs.free_mb, 0), 2) AS free_mb,
ROUND((SUM(df.bytes) – NVL(fs.free_bytes, 0)) / 1024 / 1024, 2) AS used_mb,
ROUND(((SUM(df.bytes) – NVL(fs.free_bytes, 0)) / SUM(df.bytes)) * 100, 2) AS pct_used,
CASE
WHEN MAX(df.autoextensible) = ‘YES’ THEN ‘YES’
ELSE ‘NO’
END AS autoextensible
FROM
dba_data_files df
LEFT JOIN (
SELECT
tablespace_name,
SUM(bytes) AS free_bytes,
SUM(bytes) / 1024 / 1024 AS free_mb
FROM
dba_free_space
GROUP BY
tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
GROUP BY
df.tablespace_name, fs.free_mb, fs.free_bytes
ORDER BY
pct_used DESC;
*Cuidado: Hífens, aspas ou virgulas pode ser alterados automáticamente ao ser copiado e colado no SQL Editor
____________________________________________________________________________________________________________________
Encontrar caminho dos datafiles.
SELECT file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name = ‘NOME_DA_TABLESPACE’;
Se preferir listar todas as tablespaces e seus datafiles:
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
ORDER BY tablespace_name;
Ou se preferir visualizar os datafiles com mais detalhes de armazenamento digite:
SELECT
df.file_name,
df.tablespace_name,
ROUND(df.bytes / 1024 / 1024) AS allocated_mb,
ROUND(NVL(f.free_space, 0) / 1024 / 1024) AS free_mb,
ROUND((df.bytes – NVL(f.free_space, 0)) / 1024 / 1024) AS used_mb,
ROUND(((df.bytes – NVL(f.free_space, 0)) * 100) / df.bytes, 2) AS pct_used
FROM
dba_data_files df
LEFT JOIN (
SELECT
file_id,
SUM(bytes) AS free_space
FROM
dba_free_space
GROUP BY
file_id
) f ON df.file_id = f.file_id
ORDER BY
df.tablespace_name, df.file_name;
____________________________________________________________________________________________________________________
Expandir um datafile
ALTER DATABASE DATAFILE ‘/oracle/oradata/CAMINHODAPASTA/NOMEDABASE/NOMEDODATAFILE_03.dbf’ RESIZE 37G;
*Explicação: Supondo que tamanho atual é cerca de 31,7 GB.
Para aumentar 5 GB, some 31,7 + 5 = 36,7 GB.
____________________________________________________________________________________________________________________
Criar um novo datafile
ALTER TABLESPACE NOME_DA_TABLESPACE
ADD DATAFILE ‘/oracle/oradata/CDBTST/dbtst/nomedodatafilelogdt_05.dbf’
SIZE 5G;
Caso queria que ele seja auto expansivo use:
ALTER TABLESPACE NOME_DA_TABLESPACE
ADD DATAFILE ‘/oracle/oradata/CDBTST/dbtst/nomedodatafilelogdt_05.dbf’
SIZE 5G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
____________________________________________________________________________________________________________________
*Cuidado: Hífens, aspas ou virgulas pode ser alterados automáticamente ao ser copiado e colado no SQL Editor