테이블스페이스 생성조회

SELECT 'create tablespace ' || TABLESPACE_NAME || ' datafile ' || '''c:\oracle_data\' || substr(file_name, instr(file_name,'/', -1)+1) || ''' size ' || BYTES/1024/1024 || 'm

 

AUTOEXTEND ON NEXT 30M'

       || ' default storage('

       || ' initial          80k'

       || ' next             80k'

       || ' minextents        1'

       || ' maxextents        121'

       || ' pctincrease        80'

       || '  )online;'

       as table_space

FROM

  (

  SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT

  FROM DBA_DATA_FILES E, DBA_FREE_SPACE F

  WHERE E.FILE_ID = F.FILE_ID

  GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES

  ) A

union

select 'create temporary tablesapce npqs_temp tempfile ''c:\oracle_data\npqs_temp.dbf'' size 10240m autoextend on;'

from dual;  

  ;

 

 

 

 

SELECT 'create tablespace ' || TABLESPACE_NAME || ' datafile ' || '''c:\oracle_data\' || substr(file_name, instr(file_name,'/', -1)+1) || ''' size ' || '10' || 'm

 

AUTOEXTEND ON NEXT 30M'

       || ' default storage('

       || ' initial          80k'

       || ' next             80k'

       || ' minextents        1'

       || ' maxextents        121'

       || ' pctincrease        80'

       || '  )online;'

       as table_space

FROM

  (

  SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT

  FROM DBA_DATA_FILES E, DBA_FREE_SPACE F

  WHERE E.FILE_ID = F.FILE_ID

  GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES

  ) A

union

select 'create temporary tablesapce npqs_temp tempfile ''c:\oracle_data\npqs_temp.dbf'' size 10M autoextend on;'

from dual; 

  ;

 

 

select *

from dba_data_files

where 1=1

 and tablespace_name like 'ODS%'

;

 

 

select *

from dba_tablespaces

where 1=1

  and tablespace_name like '%TEMP%'

 

 

;

 

select *

from dba_users

;

 

select distinct tablespace_name

from dba_data_files

 

;

 

select distinct tablespace_name

from dba_tablespaces

minus

select distinct tablespace_name

from dba_data_files

 

 

;

 

 

create temporary tablesapce npqs_temp tempfile '' size 10M autoextend on;