Jobs Corriendo Oracle

JOBS  CORRIENDO ORACLE
/*
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

*/

Auditoría Oracle

comprobar si una instancia de Oracle tiene activada la auditoría

La activación de la auditoría en Oracle Database viene definida por el valor del parámetro: audit_trail. Para comprobar si la auditoría de la base de datos está activada ejecutaremos el siguiente comando SQL:

select name, value
from v$parameter
where name like 'audit_trail'

Posibles valores del parámetro audit_trail:

none: desactiva la auditoría de la base de datos.

os: activa la auditoría de la base de datos. Los sucesos auditados se escribirán en la pista de auditoría del sistema operativo, no se auditará en Oracle sino en el sistema operativo anfitrión. Esta opción funcionará dependiendo del sistema operativo.

db: activa la auditoría y los datos se almacenarán en la taba SYS.AUD$ de Oracle.

db, extended: activa la auditoría y los datos se almacenarán en la taba SYS.AUD$ de Oracle. Además se escribirán los valores correspondientes en las columnas SQLBIND y SQLTEXT de la tabla SYS.AUD$.

xml: activa la auditoría de la base de datos, los sucesos será escritos en ficheros XML del sistema operativo.

xml, extended: activa la auditoría de la base de datos, los sucesos será escritos en el formato XML del sistema operativo, además se incluirán los valores de SqlText y SqlBind.

Para activar la auditoría:

ALTER SYSTEM SET audit_trail = "DB" SCOPE=SPFILE;

Para desactivar la auditoría ejecutaremos el siguiente comando:

ALTER SYSTEM SET audit_trail = "NONE" SCOPE=SPFILE;



EL CORRECTO

ALTER SYSTEM SET audit_trail = NONE SCOPE = SPFILE;


REINICIAR LA BASE DE DATOS. 

http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/798/sql-server-%E2%80%93-what-is-identity_insert

http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases

http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/770/sql-server-using-powershell-to-move-databases-between-instances

sinonimos de varias tablas de un usuario

/*


select 'create synonym .' || table_name 
     || ' for .' || table_name || ';'
from user_tables



select 'create synonym cads.' || table_name
     || ' for esquema1.' || table_name || ';'
from user_tables

      */

consultas gral.

SELECT * FROM dba_objects  WHERE status = 'INVALID'

 SELECT * from all_indexes where   STATUS='UNUSABLE'  ;


ALTER TABLE esquema1.tabla1 MOVE TABLESPACE tbspace1;
ALTER TABLE esquema1.tabla2 MOVE TABLESPACE tbspace1;
ALTER TABLE esquema1.tabla3 MOVE TABLESPACE tbspace1;
ALTER TABLE esquema1.tabla4 MOVE TABLESPACE tbspace1;
COMMIT;

alter index esquema1.index1  rebuild;
alter index esquema1.index2  rebuild;
alter index esquema1.index3  rebuild;
alter index esquema1.index4  rebuild;
COMMIT;

SELECT * FROM DBA_TABLESPACE_GROUPS
SELECT * FROM  Dba_free_space
select file_name, autoextensible from dba_data_files;
select * from dba_data_files ORDER BY TABLESPACE_NAME,FILE_ID,FILE_NAME;


select * from dba_ts_quotas
select tablespace_name from dba_tablespaces;
select * from dba_tablespaces;
select username from all_users minus select username from dba_ts_quotas;
select * from USER_TS_QUOTAS;
SELECT MIN (snap_id), MAX (snap_id) from dba_hist_snapshot;

SELECT *
FROM   TABLE(DBMS_SPACE.object_growth_trend ('esquema','tabla1','TABLE'))
ORDER BY timepoint;

SELECT * FROM dba_extents
SELECT * FROM DBA_TABLESPACE_GROUPS
SELECT * FROM  Dba_free_space


INSERT INTO  esquema1.tabla1  SELECT *  from esquema1.tabla1 @CBDBLK ;
 COMMIT;

GRANT SELECT ON esquema1.tabla1 TO "CBDBLK";




SELECT * FROM dba_objects  WHERE OWNER = 'esquema1'


select owner as usuario, count(*) num_tablas
from dba_tables
where owner not in ('APEX_030200', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'FLOWS_FILES', 'MDSYS', 'OLAPSYS', 'ORDDATA', 'ORDSYS', 'OUTLN', 'OWBSYS', 'SCOTT',
'SYS', 'SYSMAN', 'SYSTEM', 'WMSYS', 'XDB')
group by owner
order by owner

select owner, table_name, tablespace_name, num_rows
from dba_tables
where owner not in ('APEX_030200', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'FLOWS_FILES', 'MDSYS', 'OLAPSYS', 'ORDDATA', 'ORDSYS', 'OUTLN', 'OWBSYS', 'SCOTT',
 'SYS', 'SYSMAN', 'SYSTEM', 'WMSYS', 'XDB')
order by owner, table_name

select sum(BYTES)/1024/1024 MB from DBA_EXTENTS

SELECT 1905.6875 * 100  FROM DUAL;         190568.75

select a.status, count(a.index_name) , sum(b.bytes)/1024/1024
from dba_indexes a, dba_segments b
where a.index_name=b.segment_name
group by a.status;

SELECT Sum(BYTES) from DBA_EXTENTS  WHERE TABLESPACE_NAME='esquema1'

                 SELECT 935854080 /1024/1024 FROM DUAL;   892.5


select a.status as \”ESTATUS\”, count(a.index_name) as \”TOTAL\”, sum(b.bytes)/1024/1024 as \”TAMAÑO (MB)\”
from dba_indexes a, dba_segments b
where a.index_name=b.segment_name
group by a.status;

select sum(bytes)/1024/1024 MB from dba_data_files

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_TYPE

system

 ALTER TABLESPACE SYSTEM
    ADD DATAFILE '/tary.../system_02.dbf' SIZE 488281k
      AUTOEXTEND ON
      NEXT 10240K;
COMMIT;




 SELECT a.tablespace_name,
       b.size_MB,
       a.free_MB,
       Trunc((a.free_MB/b.size_MB) * 100) "FREE_%"
FROM   (SELECT tablespace_name,
               Trunc(Sum(bytes)/1024/1024) free_MB
        FROM   dba_free_space
        GROUP BY tablespace_name) a,
       (SELECT tablespace_name,
               Trunc(Sum(bytes)/1024/1024) size_MB
        FROM   dba_data_files
        GROUP BY tablespace_name) b
WHERE  a.tablespace_name = b.tablespace_name
order by 4
/

 SELECT * FROM DBA_DATA_FILES  ORDER BY TABLESPACE_NAME,FILE_ID ;



SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
      ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM (bytes) FREE_SPACE,
       ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;


with segs as (select owner, segment_type, segment_name, partition_name, ceil(bytes/1024/1024) mb
--SELECT *
from dba_segments where tablespace_name = 'SYSTEM'
order by 5 desc)
select * from segs where rownum <= 10;


select (select sum(bytes)/1024/1024  from dba_data_files where tablespace_name = 'SYSTEM') MB,
(select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'SYSTEM') MB_USED,
(select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'SYSTEM') MB_FREE
from dual;



alter database datafile '/tary.../system_02.dbf' RESIZE 1G;
  ----    1G;
COMMIT;


 SELECT * FROM DBA_DATA_FILES  ORDER BY TABLESPACE_NAME,FILE_ID ;

consulta de tablespaces, indices e indices unusables

--- CONSULTA los tablespaces 

  

--    SELECT * FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

 -- consulta indices de esquema en el tablaspace  tbspacex

--  SELECT * from all_indexes where TABLESPACE_NAME = 'tbspacex'    AND  STATUS='UNUSABLE'

-- consulta indices de esquemas en el tablaspace  tbspacex2, 

--  SELECT * from all_indexes where TABLESPACE_NAME = 'tbspacex2'   AND  STATUS='UNUSABLE'  ;