Oracle

16 03 2020

Oracle Lock 제거 및 Connection Pool 관련

1.DB Conneciton Pool 관련 Pool조회 select * from v$resource_limit where resource_name in (‘processes’, ‘sessions’); Sessions 접속 정보 쿼리 SELECT username, program FROM v$session; 접속 사용자 제거

--alter system kill session 'session_id,serial#';
alter system kill session '32,11234';

1 2	--alter system kill session 'session_id,serial#'; alter system kill session '32,11234';

현재 Connection Pool 사용현황

SELECT  * 
  FROM  V$resource_limit 
 WHERE   resource_name IN ('processes','sessions');

SELECT *

FROM V$resource_limit

WHERE resource_name IN ('processes','sessions');

접속이 안되는 경우 INACTIVE 갯수가 많을 경우 삭제 하면 됨

SELECT  'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'
  FROM  v$session
 WHERE  username = 'NURIWARE'
   AND  status = 'INACTIVE'

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'

FROM v$session

WHERE username = 'NURIWARE'

AND status = 'INACTIVE'

나오는 결과를 아래처럼 실행

alter system kill session '13,46763';
alter system kill session '29,30175';
alter system kill session '39,21235';
alter system kill session '43,53103';
alter system kill session '45,40359';
alter system kill session '46,50781';
alter system kill session '62,65397';
alter system kill session '71,15497';
alter system kill session '256,13051';
alter system kill session '262,14689';

alter system kill session '13,46763';

alter system kill session '29,30175';

alter system kill session '39,21235';

alter system kill session '43,53103';

alter system kill session '45,40359';

alter system kill session '46,50781';

alter system kill session '62,65397';

alter system kill session '71,15497';

alter system kill session '256,13051';

alter system kill session '262,14689';

2.DB Lock 관련 lock 조회

SELECT  DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,

        VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE

FROM    V$LOCKED_OBJECT VO, DBA_OBJECTS DO

WHERE   VO.OBJECT_ID = DO.OBJECT_ID;

SELECT DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,

VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE

FROM V$LOCKED_OBJECT VO, DBA_OBJECTS DO

WHERE VO.OBJECT_ID = DO.OBJECT_ID;

[…]

8 01 2020

오라클 시작 과 종료

Haeng Ho Kang | OS, DataBase, Linux/Unix, Oracle

1. 1.su – oracle sqlplus ‘/as sysdba’ SQL>startup SQL> exit oracle>lsnrctl start

30 10 2018

Oracle impdp

Haeng Ho Kang | DataBase, Oracle | 0

일부 테이블만 의 경우 먼저 Directory Dump를 만든다.

SQL> CREATE DIRECTORY KWCYBERDUMP AS '/./../kwcyber';

Directory created.

SQL> GRANT read,write on directory KWCYBERDUMP to 오라클계정;

Grant succeeded.

SQL> CREATE DIRECTORY KWCYBERDUMP AS '/./../kwcyber';

Directory created.

SQL> GRANT read,write on directory KWCYBERDUMP to 오라클계정;

Grant succeeded.

oracle$]impdp 오라클계정/패스워드@SID directory=KWCYBERDUMP dumpfile=file.dmp tables=tu_progress_log table_exists_action=append;

1	oracle$]impdp 오라클계정/패스워드@SID directory=KWCYBERDUMP dumpfile=file.dmp tables=tu_progress_log table_exists_action=append;

Import: Release 11.2.0.1.0 - Production on Tue Oct 30 17:57:25 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "계정"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "계정"."SYS_IMPORT_TABLE_01": 계정/********@SID  directory=KWCYBERDUMP dumpfile=file.dmp tables=tu_progress_log table_exists_action=append
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "계정"."TU_PROGRESS_LOG" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "계정"."TU_PROGRESS_LOG" 541.8 MB 5260803 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "계정"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:59:32

Import: Release 11.2.0.1.0 - Production on Tue Oct 30 17:57:25 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "계정"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "계정"."SYS_IMPORT_TABLE_01": 계정/********@SID directory=KWCYBERDUMP dumpfile=file.dmp tables=tu_progress_log table_exists_action=append

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39152: Table "계정"."TU_PROGRESS_LOG" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "계정"."TU_PROGRESS_LOG" 541.8 MB 5260803 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "계정"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:59:32

21 03 2018

IMP-00038: Could not convert to environment character set’s handle

Haeng Ho Kang | DataBase, Oracle | 0

에러

[ktest] /home/FILE]imp userid=nuriware/password@ktest file='tbccm03c_table.dmp' tables='tbccm03c'

IMP-00038: Could not convert to environment character set's handle
IMP-00000: Import terminated unsuccessfully

[ktest] /home/FILE]imp userid=nuriware/password@ktest file='tbccm03c_table.dmp' tables='tbccm03c'

IMP-00038: Could not convert to environment character set's handle

IMP-00000: Import terminated unsuccessfully

해결 파일위치 [ktest] /home/FILE]ls tbccm03c_table.dmp 디레토리 생성

[ktest] /home/FILE]sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 21 13:44:20 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter user-name: nuriware
Enter password:
Last Successful login time: Wed Mar 21 2018 13:42:52 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create directory tabledump as '/home/FILE';
create directory tabledump as '/home/FILE'

Directory created.

[ktest] /home/FILE]sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 21 13:44:20 2018

Enter user-name: nuriware

Enter password:

Last Successful login time: Wed Mar 21 2018 13:42:52 +09:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create directory tabledump as '/home/FILE';

create directory tabledump as '/home/FILE'

Directory created.

이때 권한 확인 chown -R oracle:oinstall FILE 임포트

[ktest] /home/FILE]ls -al
합계 208
drwxr-xr-x. 2 oracle oinstall 31 3월 21 13:31 .
drwxr-xr-x. 5 root root 45 3월 21 13:30 ..
-rwxr-xr-x. 1 oracle oinstall 212992 3월 21 13:31 tbccm03c_table.dmp

[ktest] /home/FILE]impdp userid=nuriware/password@ktest directory=tabledump dumpfile='tbccm03c_table.dmp' tables='tbccm03c'

[ktest] /home/FILE]ls -al

합계 208

drwxr-xr-x. 2 oracle oinstall 31 3월 21 13:31 .

drwxr-xr-x. 5 root root 45 3월 21 13:30 ..

-rwxr-xr-x. 1 oracle oinstall 212992 3월 21 13:31 tbccm03c_table.dmp

[ktest] /home/FILE]impdp userid=nuriware/password@ktest directory=tabledump dumpfile='tbccm03c_table.dmp' tables='tbccm03c'

9 08 2017

Oracle Log 파일 삭제 – 용량확보

Haeng Ho Kang | DataBase, Oracle | 0

오라클이 /에 설치가 됬는데 사용량이 100% 였다 du -h –max-depth=1 로 추적을 하니

[oracle@디비1 ~]$ du -h --max-depth=1

50G ./diag
92K ./.gconf
359M ./admin
20K ./.nautilus
.....

[oracle@디비1 ~]$ du -h --max-depth=1

50G ./diag

92K ./.gconf

359M ./admin

20K ./.nautilus

.....

[oracle@디비1 alert]$ pwd
/home/oracle/diag/tnslsnr/디비1/listener/alert

1 2	[oracle@디비1 alert]$ pwd /home/oracle/diag/tnslsnr/디비1/listener/alert

여기에 데이타가 많을 경우 rm -rf * 로 삭제하면 된다. log.xml은 다시 생성된다.

9 08 2017

Oracle 용량 관련 DDL

Haeng Ho Kang | Uncategorized, DataBase, Oracle | 0

전체용량/남은용량

SELECT   A.TABLESPACE_NAME,
ROUND(SUM(A.BYTES) / (1024 * 1024)) || 'M' "전체", --3
ROUND(SUM(B.FREES) / (1024 * 1024)) || 'M' "여유" -- 4
FROM
(SELECT FILE_ID, TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES -- 1
GROUP BY FILE_ID, TABLESPACE_NAME) A
INNER JOIN
(SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) FREES
FROM DBA_FREE_SPACE -- 2
GROUP BY TABLESPACE_NAME, FILE_ID) B
ON   A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND   A.FILE_ID = B.FILE_ID
GROUP  BY A.TABLESPACE_NAME;

SELECT A.TABLESPACE_NAME,

ROUND(SUM(A.BYTES) / (1024 * 1024)) || 'M' "전체", --3

ROUND(SUM(B.FREES) / (1024 * 1024)) || 'M' "여유" -- 4

FROM

(SELECT FILE_ID, TABLESPACE_NAME, SUM(BYTES) BYTES

FROM DBA_DATA_FILES -- 1

GROUP BY FILE_ID, TABLESPACE_NAME) A

INNER JOIN

(SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) FREES

FROM DBA_FREE_SPACE -- 2

GROUP BY TABLESPACE_NAME, FILE_ID) B

ON A.TABLESPACE_NAME = B.TABLESPACE_NAME

AND A.FILE_ID = B.FILE_ID

GROUP BY A.TABLESPACE_NAME;

테이블스페이스와 데이터 파일 조회하기

select tablespace_name, bytes/1024/1024 MB, file_name
from dba_data_files;

1 2	select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

테이블스페이스 확장

alter tablespace TabseSpace add

datafile '/경로/DataBaseFiles/테이블스페이스02.dbf' size 5138022400;

alter tablespace TabseSpace add

datafile '/경로/DataBaseFiles/테이블스페이스02.dbf' size 5138022400;

22 05 2017

Oracle DDL문 생성및관리

Haeng Ho Kang | DataBase, Oracle | 0

http://cafe.naver.com/ocmkorea/book5085531/17192 테이블 수정

#>su - oracle
$>sqlplus /nolog
SQL>connect sys /as sysdba

SQL> alter table ABC_OPEN add(FILE_NAME varchar2(30));
SQL> alter table ABC_COURSECODE add(FILE_NAME1 varchar2(30), FILE_NAME2 varchar2(30), FILE_NAME3 varchar2(30), FILE_NAME4 varchar2(30), FILE_NAME5 varchar2(30));
SQL> alter table ABC_COMPLETEUSER add(DIPLOMANUM NUMBER(7,0));

#>su - oracle

$>sqlplus /nolog

SQL>connect sys /as sysdba

SQL> alter table ABC_OPEN add(FILE_NAME varchar2(30));

SQL> alter table ABC_COURSECODE add(FILE_NAME1 varchar2(30), FILE_NAME2 varchar2(30), FILE_NAME3 varchar2(30), FILE_NAME4 varchar2(30), FILE_NAME5 varchar2(30));

SQL> alter table ABC_COMPLETEUSER add(DIPLOMANUM NUMBER(7,0));

테이블 변경

ALTER TABLE ABC_OPEN ADD( birth DATE) ;
ALTER TABLE ABC_OPEN MODIFY ename VARCHAR2(30) ;
ALTER TABLE ABC_OPEN DROP COLUMN ename ;
ALTER TABLE ABC_OPEN SET UNUSED (eno) ;
ALTER TABLE ABC_OPEN DROP UNUSED COLUMNS ;

ALTER TABLE ABC_OPEN ADD( birth DATE) ;

ALTER TABLE ABC_OPEN MODIFY ename VARCHAR2(30) ;

ALTER TABLE ABC_OPEN DROP COLUMN ename ;

ALTER TABLE ABC_OPEN SET UNUSED (eno) ;

ALTER TABLE ABC_OPEN DROP UNUSED COLUMNS ;

2 03 2017

Oracle 용량확인

Haeng Ho Kang | DataBase, Oracle | 0

테이블스페이스 정보 조회 SELECT * FROM DBA_TABLESPACES; Oracle 테이블 스페이별 용량

select substr(a.tablespace_name,1,30) tablespace,
round(sum(a.total1)/1024/1024,1) "TotalMB",
round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
round(sum(a.sum1)/1024/1024,1) "FreeMB",
round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name,sum(bytes) total1,0,0,0
from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by tablespace;

select substr(a.tablespace_name,1,30) tablespace,

round(sum(a.total1)/1024/1024,1) "TotalMB",

round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",

round(sum(a.sum1)/1024/1024,1) "FreeMB",

round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"

from

(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt

from dba_free_space

group by tablespace_name

union

select tablespace_name,sum(bytes) total1,0,0,0

from dba_data_files

group by tablespace_name) a

group by a.tablespace_name

order by tablespace;

테이블스페이스별 현황 확인 쿼리문(MB 단위)

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes 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;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes 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;

테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)

SELECT    A.TABLESPACE_NAME "테이블스페이스명",
          A.FILE_NAME "파일경로",
           (A.BYTES - B.FREE)    "사용공간",
            B.FREE                 "여유 공간",
            A.BYTES                "총크기",
            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
      FROM
       (
         SELECT FILE_ID,
                TABLESPACE_NAME,
                FILE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
         GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       (
         SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
      WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
         AND A.FILE_ID = B.FILE_ID;

SELECT A.TABLESPACE_NAME "테이블스페이스명",

A.FILE_NAME "파일경로",

(A.BYTES - B.FREE) "사용공간",

B.FREE "여유 공간",

A.BYTES "총크기",

TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"

FROM

(

SELECT FILE_ID,

TABLESPACE_NAME,

FILE_NAME,

SUBSTR(FILE_NAME,1,200) FILE_NM,

SUM(BYTES) BYTES

FROM DBA_DATA_FILES

GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)

) A,

(

SELECT TABLESPACE_NAME,

FILE_ID,

SUM(NVL(BYTES,0)) FREE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME,FILE_ID

) B

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME

AND A.FILE_ID = B.FILE_ID;

테이블 용량 조회

<span class="kwd">SELECT</span><span class="pln">
   owner</span><span class="pun">,</span><span class="pln"> table_name</span><span class="pun">,</span><span class="pln"> TRUNC</span><span class="pun">(</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">)/</span><span class="lit">1024</span><span class="pun">/</span><span class="lit">1024</span><span class="pun">/</span><span class="lit">1024</span><span class="pun">)</span><span class="pln"> GB
</span><span class="kwd">FROM</span>
<span class="pun">(</span><span class="kwd">SELECT</span><span class="pln"> segment_name table_name</span><span class="pun">,</span><span class="pln"> owner</span><span class="pun">,</span><span class="pln"> bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_segments
</span><span class="kwd">WHERE</span><span class="pln"> segment_type </span><span class="kwd">in</span>  <span class="pun">(</span><span class="str">'TABLE'</span><span class="pun">,</span><span class="str">'TABLE PARTITION'</span><span class="pun">)</span>
<span class="kwd">UNION</span> <span class="kwd">ALL</span>
<span class="kwd">SELECT</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">owner</span><span class="pun">,</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_indexes i</span><span class="pun">,</span><span class="pln"> dba_segments s
</span><span class="kwd">WHERE</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">segment_name </span><span class="pun">=</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">index_name
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">owner </span><span class="pun">=</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">owner
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">segment_type </span><span class="kwd">in</span> <span class="pun">(</span><span class="str">'INDEX'</span><span class="pun">,</span><span class="str">'INDEX PARTITION'</span><span class="pun">)</span>
<span class="kwd">UNION</span> <span class="kwd">ALL</span>
<span class="kwd">SELECT</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner</span><span class="pun">,</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_lobs l</span><span class="pun">,</span><span class="pln"> dba_segments s
</span><span class="kwd">WHERE</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">segment_name </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">segment_name
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">owner </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">segment_type </span><span class="kwd">IN</span> <span class="pun">(</span><span class="str">'LOBSEGMENT'</span><span class="pun">,</span><span class="str">'LOB PARTITION'</span><span class="pun">)</span>
<span class="kwd">UNION</span> <span class="kwd">ALL</span>
<span class="kwd">SELECT</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner</span><span class="pun">,</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_lobs l</span><span class="pun">,</span><span class="pln"> dba_segments s
</span><span class="kwd">WHERE</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">segment_name </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">index_name
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">owner </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">segment_type </span><span class="pun">=</span> <span class="str">'LOBINDEX'</span><span class="pun">)</span>
<span class="com">---WHERE owner in UPPER('&amp;owner')</span>
<span class="kwd">GROUP</span> <span class="kwd">BY</span><span class="pln"> table_name</span><span class="pun">,</span><span class="pln"> owner
</span><span class="kwd">HAVING</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">)/</span><span class="lit">1024</span><span class="pun">/</span><span class="lit">1024</span> <span class="pun">&gt;</span> <span class="lit">10</span>  <span class="com">/* Ignore really small tables */</span>
<span class="kwd">ORDER</span> <span class="kwd">BY</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">)</span> <span class="kwd">desc</span>

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type in ('TABLE','TABLE PARTITION')

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type in ('INDEX','INDEX PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION')

UNION ALL

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX')

---WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

12 10 2016

Oracle PK, INDEX등 처리

Haeng Ho Kang | DataBase, Oracle | 0

Primary Key (1)생성 CREATE UNIQUE INDEX 테이블명_UK ON 테이블 (column1, column2) TABLESPACE 테이블스페이스 ; ALTER TABLE 테이블명 ADD ( PRIMARY KEY (column1, column2) USING INDEX 테이블명_UK ENABLE VALIDATE); (2)삭제 alter table 테이블명 drop primary key; drop index 테이블명_UK; INDEX CREATE INDEX IDX_CMM_USER ON TB_CMM_USER(SVCID);

3 06 2016

Oracle – ORA-12514

JongWon Kim | DataBase, Oracle | 0

1.오라클 접속이 안될때 (1)리스너는 시작되었는가? – lsnrctl 로 들어가서 start를 하면된다. (2)데이타베이스는 시작되었는가? oracle service > sqlplus /nolog > connect sys/ as sysdba > startup (시작명령어)