USER DAN TABLESPACE

Microsoft Windows [Version 6.1.7600]

Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Kam Des 15 09:40:37 2016

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

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> CREATE TABLESPACE mtr datafile 'C:\oraclexe\oradata\mtr.dbf';

CREATE TABLESPACE mtr datafile 'C:\oraclexe\oradata\mtr.dbf'

*

ERROR at line 1:

ORA-01119: error in creating database file 'C:\oraclexe\oradata\mtr.dbf'

ORA-17610: file 'C:\oraclexe\oradata\mtr.dbf' does not exist and no size

specified

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

SQL> CREATE TABLESPACE mtr datafile 'C:\oraclexe\oradata\mtr.dbf' size 10m;

Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS

  2    2  FROM USER_TABLESPACES;

  2  FROM USER_TABLESPACES

  *

ERROR at line 2:

ORA-00923: FROM keyword not found where expected

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS

      FROM USER_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS

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

SYSTEM                         ONLINE    PERMANENT

UNDO                           ONLINE    UNDO

SYSAUX                         ONLINE    PERMANENT

TEMP                           ONLINE    TEMPORARY

USERS                          ONLINE    PERMANENT

MTR                            ONLINE    PERMANENT

6 rows selected.

SQL> DROP TABLESPACE tbs_02

  2     INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE tbs_02

*

ERROR at line 1:

ORA-00959: tablespace 'TBS_02' does not exist

SQL> DROP TABLESPACE mtr

     INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> SELECT TABLESPACE_NAME,STATUS,CONTENTS FROM USER_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS

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

SYSTEM                         ONLINE    PERMANENT

UNDO                           ONLINE    UNDO

SYSAUX                         ONLINE    PERMANENT

TEMP                           ONLINE    TEMPORARY

USERS                          ONLINE    PERMANENT

SQL> CREATE TABLESPACE mtr datafile 'D:\ORACLE\MTR.dbf' size 100m;

Tablespace created.

SQL> SELECT TABLESPACE_NAME,STATUS,CONTENTS FROM USER_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS

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

SYSTEM                         ONLINE    PERMANENT

UNDO                           ONLINE    UNDO

SYSAUX                         ONLINE    PERMANENT

TEMP                           ONLINE    TEMPORARY

USERS                          ONLINE    PERMANENT

MTR                            ONLINE    PERMANENT

6 rows selected.

SQL> create user ellen

    identified by roroellen

      ;

User created.

SQL>

SQL> alter user ellen

  2  default tablespace mtr;

User altered.

Tidak bisa login

Karena belum diberi hak akses login

SQL> alter user ellen

   default tablespace mtr;

User altered.

SQL> grant connect to ellen;

Grant succeeded.

SQL>

Kasih space dulu agar bisa membuat table.

SQL> alter user ellen quota 100m on mtr;

User altered.

Grant bisa unlimited

GRANT UNLIMITED TABLESPACE TO <username>

 DOWNLOAD FILE LENGKAP USER DAN TABLESPACE ORACLE