강좌
클라우드/리눅스에 관한 강좌입니다.
데이터베이스 분류

ORACLE 기본 관리기법

작성자 정보

  • 웹관리자 작성
  • 작성일

컨텐츠 정보

본문

icon01.gif 기본관리법_1

[super:/ 27 ] id

uid=0(root) gid=1(daemon) groups=0(system),3(mem),7(terminal),9(opr),12(lp)

[super:/ 28 ] su - oracle

[super:/app/oracle] sqlplus

SQL*Plus: Release 3.3.3.0.0 - Production on Sat Aug 14 14:37:28 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.

Enter user-name: sys

Enter password: ********

 

Connected to:

Oracle7 Server Release 7.3.3.0.0 with the 64-bit option - Production Release

With the distributed, replication and Spatial Data options

PL/SQL Release 2.3.3.0.0 - Production

SQL> select * from all_users;

 

USERNAME USER_ID CREATED

------------------------------ ---------- ---------

SYS 0 25-JUL-98
SYSTEM 5 25-JUL-98
SCOTT 11 25-JUL-98
DBSNMP 9 25-JUL-98
TRACESVR 10 25-JUL-98
YILEE 18 22-SEP-98
MDSYS 12 25-JUL-98
PERL 15 19-AUG-98
IPHONE 24 18-DEC-98
WOOJUNG 19 24-SEP-98
 

USERNAME USER_ID CREATED

------------------------------ ---------- ---------

BUILD 20 13-OCT-98
JNET 22 13-NOV-98
DSWEBMAS 25 06-JAN-99
MAGIC 33 24-MAY-99
WEATHER 27 02-MAR-99
CLOUD9 28 09-MAR-99
KHRCPR 29 27-MAR-99
HAUSING 32 18-MAY-99
SSPARK 31 17-APR-99
COMPARA 34 22-JUN-99
BIZMAIL 35 09-JUL-99

23 rows selected.

SQL>

 

SQL> alter user sys identified by alssmd;

User altered.

SQL> alter user system identified by alssmd;

User altered.

SQL> create user testuser identified by alssmd;

User created.

SQL> grant create session to testuser;

Grant succeeded.

SQL> grant create tablespace to testuser;

Grant succeeded.

SQL> grant create table to testuser;

Grant succeeded.

SQL> grant create view to testuser;

Grant succeeded.

SQL> grant create synonym to testuser;

Grant succeeded.

SQL> grant create database link to testuser;

Grant succeeded.

SQL> grant resource to testuser;

Grant succeeded.

SQL> grant unlimited tablespace to testuser;

Grant succeeded.

SQL> alter user testuser quota unlimited on users;

User altered.

SQL> alter user testuser quota 1M on users;

User altered.

SQL> create role developers;

Role created.

SQL> desc dba_users;

Name Null? Type

------------------------------- -------- ----

USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)

 

SQL> select username, password from dba_users;

USERNAME PASSWORD
------------------------------ ------------------------------
SYS            8EDC91BB0593668A
SYSTEM    9D294448F2563DAA
SCOTT       F894844C34402B67
DBSNMP    E066D214D5421CCC
TRACESVR F9DA8977092B7B81
YILEE EF3F22EF4373FA9D
MDSYS 7A467BA6123F896B
WSCHURCH 07C09AF7741D15CD
PERL F0F2FFCE2EB4C446
IPHONE 69A669B7A55DA87E
WOOJUNG D3C8005E322C25A6

USERNAME PASSWORD
------------------------------ ------------------------------
BUILD 2CD2ACB7C7CDAFF0
JNET EFDCDC5622E1E915
DSWEBMAS 6BBB2F98724B6EC4
MAGIC D9ADCD681724170E
WEATHER 8B61551F838998EB
CLOUD9 E5922256029F3184
KHRCPR 02FD3CBF8952FD00
HAUSING 4FB638B476E6D34E
SSPARK DE6F22D5E410B8A2
COMPARA CF3C8D913C56B419
BIZMAIL C7726EB3405FE73F 
USERNAME PASSWORD
------------------------------ ------------------------------
MINOZ CB5027FC63554E9F
TESTUSER 478020754D66061F
 

24 rows selected.

SQL> select * from v$datafile;

FILE# STATUS ENABLED CHECKPOINT_CHANGE# BYTES CREATE_BYTES

---------- ------- ---------- ------------------ ---------- ------------

NAME

--------------------------------------------------------------------------------

19 ONLINE READ WRITE 818642 104857600 104857600

/user2/phone/ORADATA/roll1.dbf

 

20 ONLINE READ WRITE 818642 104857600 104857600

/user2/phone/ORADATA/roll2.dbf

 

21 ONLINE READ WRITE 818642 10485760 10485760

/user5/khrcpr/ORA/khrcpr.dbf

 

FILE# STATUS ENABLED CHECKPOINT_CHANGE# BYTES CREATE_BYTES

---------- ------- ---------- ------------------ ---------- ------------

NAME

--------------------------------------------------------------------------------

22 ONLINE READ WRITE 818642 10485760 10485760

/user1/magic/ORA/magic.dbf

 

23 ONLINE READ WRITE 818642 10485760 10485760

/user5/compara/ORA/compara.dbf

24 ONLINE READ WRITE 818642 10485760 10485760

/user4/bizmail/ORA/bizmail.dbf

FILE# STATUS ENABLED CHECKPOINT_CHANGE# BYTES CREATE_BYTES

---------- ------- ---------- ------------------ ---------- ------------

NAME

--------------------------------------------------------------------------------

25 ONLINE READ WRITE 818642 10485760 10485760

/home/minoz/ORA/minoz.dbf

 

25 rows selected.

SQL> desc v$datafile;

Name Null? Type

------------------------------- -------- ----

FILE# NUMBER

STATUS VARCHAR2(7)

ENABLED VARCHAR2(10)

CHECKPOINT_CHANGE# NUMBER

BYTES NUMBER

CREATE_BYTES NUMBER

NAME VARCHAR2(257)

SQL> desc v$datafile;

Name Null? Type

------------------------------- -------- ----

FILE# NUMBER

STATUS VARCHAR2(7)

ENABLED VARCHAR2(10)

CHECKPOINT_CHANGE# NUMBER

BYTES NUMBER

CREATE_BYTES NUMBER

NAME VARCHAR2(257)

 

SQL> select name from v$datafile;

 

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/ORA733/system01.dbf
/app/oracle/oradata/ORA733/rbs01.dbf
/user2/phone/ORADATA/temp.dbf
/app/oracle/oradata/ORA733/tools01.dbf
/app/oracle/oradata/ORA733/users01.dbf
/user1/wschurch/ORA/wschurch.dbf
/user1/mosehan/ORA/perl.dbf
/user1/sspark/ORA/sspark.dbf
/usr/staff/webadmin/ORADATA/webadmin.dbf
/user4/woojung/ORA/woojung.dbf
/user1/build/ORA/build.dbf

NAME
--------------------------------------------------------------------------------
/user3/dswebmas/ORA/dswebmas.dbf
/user2/phone/ORADATA/iphone.dbf
/app/oracle/oradata/ORA733/rbs02.dbf
/user1/hausing/ORA/hausing.dbf
/user3/jongpil/ORA/weather.dbf
/user1/cloud9/ORA/cloud9.dbf
/user4/jnet/ORA/jnet.dbf
/user2/phone/ORADATA/roll1.dbf
/user2/phone/ORADATA/roll2.dbf
/user5/khrcpr/ORA/khrcpr.dbf
/user1/magic/ORA/magic.dbf

NAME
--------------------------------------------------------------------------------
/user5/compara/ORA/compara.dbf
/user4/bizmail/ORA/bizmail.dbf
/home/minoz/ORA/minoz.dbf
 
25 rows selected.

 

SQL> select * from tab;

 

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
V_$SHARED_SERVER VIEW
V_$SORT_SEGMENT VIEW
V_$SQL VIEW
V_$SQLAREA VIEW
V_$SQLTEXT VIEW
V_$SQLTEXT_WITH_NEWLINES VIEW
V_$SQL_BIND_DATA VIEW
V_$SQL_BIND_METADATA VIEW
V_$SQL_CURSOR VIEW
V_$SQL_SHARED_MEMORY VIEW
V_$STATNAME VIEW
 

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
V_$SYSSTAT VIEW
V_$SYSTEM_CURSOR_CACHE VIEW
V_$SYSTEM_EVENT VIEW
V_$SYSTEM_PARAMETER VIEW
V_$THREAD VIEW
V_$TIMER VIEW
V_$TRANSACTION VIEW
V_$TYPE_SIZE VIEW
V_$VERSION VIEW
V_$WAITSTAT VIEW
V_$_LOCK VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------

_default_auditing_options_ TABLE

 

518 rows selected.

 

SQL>

 

SQL> select username, default_tablespace, temporary_tablespace from dba_users;

 

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

TEMPORARY_TABLESPACE

------------------------------

DBSNMP SYSTEM

SYSTEM

 

TRACESVR SYSTEM

SYSTEM

 

YILEE WEBADMIN

TEMP

 

 

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

TEMPORARY_TABLESPACE

------------------------------

MDSYS SYSTEM

SYSTEM

 

WSCHURCH WSCHURCH

TEMP

 

PERL PERL

TEMP

 

 

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

TEMPORARY_TABLESPACE

------------------------------

JNET JNET

TEMP

DSWEBMAS DSWEBMAS

TEMP

MAGIC MAGIC

TEMP

 

24 rows selected.

 

SQL> desc dba_objects;

Name Null? Type

------------------------------- -------- ----

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(13)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(75)

STATUS VARCHAR2(7)

 

 

SQL> select object_name, object_type from dba_objects where owner='scott';

no rows selected

SQL> select object_name, object_type from dba_objects where owner='sys';

no rows selected

SQL> desc dba_data_files;

Name Null? Type

------------------------------- -------- ----

FILE_NAME VARCHAR2(257)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)

 

SQL> select file_name, file_id, bytes from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

FILE_ID BYTES

---------- ----------

/user3/jongpil/ORA/weather.dbf

16 10485760

 

/user1/cloud9/ORA/cloud9.dbf

17 5242880

 

/user4/jnet/ORA/jnet.dbf

18 10485760

FILE_NAME

--------------------------------------------------------------------------------

FILE_ID BYTES

---------- ----------

/user1/magic/ORA/magic.dbf

22 10485760

 

/user5/compara/ORA/compara.dbf

23 10485760

 

/user4/bizmail/ORA/bizmail.dbf

24 10485760

 

25 rows selected.

 

SQL> desc dba_data_files;

Name Null? Type

------------------------------- -------- ----

FILE_NAME VARCHAR2(257)

FILE_ID NUMBER

TABLESPACE_NAME VARCHAR2(30)

BYTES NUMBER

BLOCKS NUMBER

STATUS VARCHAR2(9)

 

 

SQL> select tablespace_name, sum(bytes), max(bytes) from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME SUM(BYTES) MAX(BYTES)

------------------------------ ---------- ----------

BIZMAIL 9244672 9101312

BUILD 4769792 4769792

CLOUD9 3960832 3807232

COMPARA 10483712 10483712

DSWEBMAS 5240832 5240832

HAUSING 10452992 10452992

IPHONE 375631872 197632000

JNET 10022912 10022912

KHRCPR 10319872 10319872

MAGIC 10483712 10483712

MINOZ 10483712 10483712

 

TABLESPACE_NAME SUM(BYTES) MAX(BYTES)

------------------------------ ---------- ----------

PERL 9142272 9142272

RBS 66637824 66516992

RBS3 117766144 75939840

SSPARK 10483712 10483712

SYSTEM 23400448 23400448

TEMP 104855552 104855552

TOOLS 13228032 13228032

USERS 1046528 1046528

WEATHER 10483712 10483712

WEBADMIN 31444992 31444992

WOOJUNG 10483712 10483712

 

TABLESPACE_NAME SUM(BYTES) MAX(BYTES)

------------------------------ ---------- ----------

WSCHURCH 10176512 10176512

 

23 rows selected.

 

SQL> select file_name from dba_data_files;

 

FILE_NAME

--------------------------------------------------------------------------------

/app/oracle/oradata/ORA733/system01.dbf

/app/oracle/oradata/ORA733/rbs01.dbf

/user2/phone/ORADATA/temp.dbf

/app/oracle/oradata/ORA733/tools01.dbf

/app/oracle/oradata/ORA733/users01.dbf

/user1/wschurch/ORA/wschurch.dbf

/user1/mosehan/ORA/perl.dbf

/user1/sspark/ORA/sspark.dbf

/usr/staff/webadmin/ORADATA/webadmin.dbf

/user4/woojung/ORA/woojung.dbf

/user1/build/ORA/build.dbf

 

FILE_NAME

--------------------------------------------------------------------------------

/user3/dswebmas/ORA/dswebmas.dbf

/user2/phone/ORADATA/iphone.dbf

/app/oracle/oradata/ORA733/rbs02.dbf

/user1/hausing/ORA/hausing.dbf

/user3/jongpil/ORA/weather.dbf

/user1/cloud9/ORA/cloud9.dbf

/user4/jnet/ORA/jnet.dbf

/user2/phone/ORADATA/roll1.dbf

/user2/phone/ORADATA/roll2.dbf

/user5/khrcpr/ORA/khrcpr.dbf

/user1/magic/ORA/magic.dbf

 

FILE_NAME

--------------------------------------------------------------------------------

/user5/compara/ORA/compara.dbf

/user4/bizmail/ORA/bizmail.dbf

/home/minoz/ORA/minoz.dbf

 

25 rows selected.

 

SQL> select value from v$parameter where name='control_files';

 

VALUE

--------------------------------------------------------------------------------

/app/oracle/oradata/ORA733/control01.ctl, /app/oracle/oradata/ORA733/control02.c

tl, /app/oracle/oradata/ORA733/control03.ctl

 

 

SQL> host

[super:/app/oracle]

[super:/app/oracle] exit

SQL>

SQL> host

[super:/app/oracle] id

uid=13(oracle) gid=26(DBA)

 

[super:/app/oracle] tar cvf /dev/rmt/Omextra2/extra2/userDB/INSADB.dbf

tar: cannot open /dev/rmt/Omextra2/extra2/userDB/INSADB.dbf: No such file or directory

[super:/app/oracle] cp /app/oracle/*.dbf /user18

No match.

 

SQL> drop user testuser;

User dropped.

"무단배포금지: 클라우드포털(www.linux.co.kr)의 모든 강좌는 저작권에 의해 보호되는 콘텐츠입니다. 무단으로 복제하여 배포하는 행위는 금지되어 있습니다."

관련자료

댓글 0
등록된 댓글이 없습니다.
목록

공지사항

뉴스광장

  • 현재 회원수 :  60,278 명
  • 현재 강좌수 :  36,955 개
  • 현재 접속자 :  348 명