Categoria: Banco de Dados

Índices select ‘alter index ‘|| owner ||’.’||segment_name||’ rebuild tablespace USERS;’from dba_segmentswhere 0=0and owner = ‘xxxx’and tablespace_name<>’USERS’and segment_type=’INDEX’; Tabelas select ‘alter table ‘||owner ||’.’||segment_name||’ move tablespace USERS;’from dba_segmentswhere owner=’xxxxx’and tablespace_name<>’USERS’and segment_type=’TABLE’;...

Backupexp user/pass@server file=’c:\folder\file.dmp’ owner=XXXX Restoreimp user/pass@server file=’c:\folder\file.dmp’ fromuser=XXXX touser=YYYY...

DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE...

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id FROM sys.dm_os_waiting_tasks WHERE blocking_session_id <> 0...

SELECT sqltext.TEXT,req.session_id,req.status,req.command,req.cpu_time,req.total_elapsed_time,start_time,right(convert(varchar,dateadd(ms, datediff(ms, start_time, getdate()), ‘1900-01-01’),121), 12) as ‘batch_duration1’,wait_timeFROM sys.dm_exec_requests reqCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext...

CREATE USER usuario IDENTIFIED BY senha DEFAULT TABLESPACE USERS; GRANT CONNECT,RESOURCE TO usuario; GRANT DBA TO usuario WITH ADMIN OPTION; obs: no oracle 12 é necessário executar o comando abaixo antes de criar o usuário: alter session set “_ORACLE_SCRIPT”=true;...

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;...

SELECT W.INST_ID, W.SID,S.serial#, W.EVENT, W.SECONDS_IN_WAIT, SQL.SQL_TEXTFROM GV$SESSION_WAIT W, GV$SESSION S, GV$PROCESS P, GV$SQLTEXT SQLWHERE W.SID = S.SID ANDS.PADDR = P.ADDR ANDSQL.ADDRESS = S.SQL_ADDRESS ANDSQL.HASH_VALUE = S.SQL_HASH_VALUE ANDW.WAIT_CLASS != ‘Idle’ ANDW.INST_ID = S.INST_ID ANDW.INST_ID = P.INST_ID ANDW.INST_ID = SQL.INST_IDORDER BY W.SECONDS_IN_WAIT, W.INST_ID,...

expdp (backup) expdp usuario/senha@database directory=nome_diretorio dumpfile=nome_arquivo.DMP schemas=usuario_backup Obs. quando o backup for feito no oracle12 para ser restaurado no oracle11 é necessário acrescentar o parametro da versao, por exemplo: expdp usuario/senha@database directory=nome_diretorio dumpfile=nome_arquivo.DMP schemas=usuario_backup VERSION=11.2 impdp (restore) impdp usuario/senha@database directory=nome_diretorio dumpfile=nome_arquivo.DMP...

Listar SELECT * FROM all_directories Criar CREATE DIRECTORY nome_diretorio AS ‘C:/pasta/subpasta’; Apagar DROP DIRECTORY nome_diretorio;...