viernes, 7 de junio de 2013

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


No hay comentarios: