신문지한장

oracle hotbackup check

set line 200 col name for a50 col status for a15 select a.file#,a.name,b.status,to_char(b.time,'YYYY-MM-DD:HH24:MI:SS') as time from v$datafile a,v$backup b where a.file#=b.file#; SQL> @hot FILE# NAME STATUS TIME ---------- -------------------------------------------------- --------------- ------------------- 1 /oracle/app/oracle/oradata/CHANGE19/system01.dbf NOT ACTIVE 3 /oracle/app/oracle/orad..
Oracle/sql&script 2024. 4. 18. 15:38
[SQL] redo log 그룹, 멤버 조회

현재 logfile 그룹, 멤버 조회 set line 200 col group# for 999 col mb for 999 col member for a60 col seq# for 999 col status for a8 col arc for a5 select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" FROM v$logfile a, v$log b where a.group#=b.group# order by 1,2;
Oracle/sql&script 2024. 1. 4. 10:36
[SQL] Tablespace 및 Datafile 조회

현재 Tablespace 조회 select tablespace_name, status, contents, extent_management, segment_space_management from dba_tablespaces; 현재 Tablespace size 조회 SELECT ddf.tablespace_name "Tablespace Name", ddf.bytes/1024/1024 "Bytes(MB)", (ddf.bytes - dfs.bytes)/1024/1024 "Used(MB)", ROUND(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent(%)", dfs.bytes/1024/1024 "Free(MB)", ROUND((1 - ((ddf.bytes - d..
Oracle/sql&script 2024. 1. 4. 10:31
[SQL] oracle schema 및 tablespace 삭제 추출문

OS : CentOS7.9 (64bit) DB : Oracle Database 11g ( Release - 11.2.0.4 ) [모든 버전 적용] 1. schema 삭제 추출문 select 'DROP USER ' || username || ' cascade;' From dba_users where account_status>= 'OPEN' order by created; 2. tablespace 삭제 추출문 select distinct 'DROP TABLESPACE ' || tablespace_name || ' INCLUDING CONTENTS AND DATAFILES;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','USERS..
Oracle/sql&script 2023. 11. 30. 17:43
[SQL] datapump(exp/imp) 이관시 tablespace & temptablespace 생성 추출문

OS : CentOS7.9 (64bit) DB : Oracle Database 11g ( Release - 11.2.0.4 ) [모든 버전 적용] 1. tablespace 생성 추출문 * none OMF 1-1. none OMF (none TDE) - alter set line 200 pages 1000 col info for a110 SELECT case when rank = 1 then ' create tablespace ' || tablespace_name || ' datafile ''' || file_name || ''' size '|| round(bytes/1024/1024,0) || 'm' else ' alter tablespace ' || tablespace_name || ' add data..
Oracle/sql&script 2023. 11. 30. 15:55
이전 Prev 1 ··· 3 4 5 6 7 Next 다음