APPunti

set linesize 1000 set head off set pagesize 10000 define owner_schema='BAAN' (char)

spool lista.txt

select count(*) from (SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB,SUM(DS.BYTES) AS TOTAL_BYTES

 FROM DBA_SEGMENTS DS
 WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES where owner like 'BAAN')
GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME) where MB < 1000 order by MB desc;

spool off

select count(*) from (SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB,SUM(DS.BYTES) AS TOTAL_BYTES

 FROM DBA_SEGMENTS DS
 WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES where owner like 'BAAN')
GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME);

SELECT COUNT(*) FROM DBA_TABLES where owner like 'BAAN')

select count(*) from (SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB,SUM(DS.BYTES) AS TOTAL_BYTES FROM DBA_SEGMENTS DS WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES where owner like 'BAAN' AND NUM_ROWS>0) GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME) where MB < 1000 order by MB desc;

9828

3158

define owner_schema='BAAN' (char) define directory_dump=' DATA_PUMP_DIR_OGG' (char) define datapump_file='export.dmp' (char) define datapump_log='export.log' (char) set linesize 1000 set echo off set term off set feed off set trims on set head off set feedback off set pagesize 1000 set verify off

spool datapump.sql select 'DIRECTORY=&directory_dump' from dual; select 'DUMPFILE=&datapump_file' from dual; select 'LOGFILE=&datapump_log' from dual;

select 'TABLES=&owner_schema'||'.'||SEGMENT_NAME||from (SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB,SUM(DS.BYTES) AS TOTAL_BYTES FROM DBA_SEGMENTS DS WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES where owner like '&owner_schema' AND NUM_ROWS>0) GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME) where MB < 1000 order by MB desc; spool off

SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES where owner like 'BAAN' and NUM_ROWS = 0;

spool controllo.sql SELECT 'select count(*) from BAAN.'||TABLE_NAME||';' FROM DBA_TABLES where owner like 'BAAN' and NUM_ROWS > 0; spool off spool risultato.txt start controllo.sql spool off

DECLARE

  TYPE lv_student_ID IS TABLE OF NUMBER;

BEGIN

  -- assuming you are trying to find all students who have not yet paid, you can use a nested table
  SELECT StudentIDNumber
    BULK COLLECT INTO lv_student_ID
    FROM Lease INNER JOIN Invoice ON Invoice.LeaseID = Lease.LeaseID
   WHERE IsPaid = 'N';

  -- use value as you want
  DBMS_OUTPUT.put_line (lv_student_ID.COUNT);

END;

mysql -u root -p -h127.0.0.1 --one-database test < last_backup.dmp