purwant's blog : 2017-11-12

Microsoft Windows [Version 6.1.7600]

C:\Windows\system32>sqlplus

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

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

TULISAN INI ADALAH PRAKTIK DARI PEMILIK BLOG , BELUM SEMPAT MEMBERIKAN PENJELASAN HANYA SEKEDAR DOKUMENTASI.

MEMULAI FIREBIRD

Firebird basic

C:\Program Files\firebird\firebird_2_1\bin>ISQL

Use CONNECT or CREATE DATABASE to specify a database

SQL> CREATE DATABASE 'D:\CENTRAL\DATA.fdb' user 'sysdba' password 'masterkey'

SQL> show database;

Database: D:\CENTRAL\DATA.fdb

Owner: SYSDBA

PAGE_SIZE 4096

Number of DB pages allocated = 196

Sweep interval = 20000

Forced Writes are ON

Transaction - oldest = 5

Transaction - oldest active = 6

Transaction - oldest snapshot = 6

Transaction - Next = 15

ODS = 11.1

Default Character set: NONE

SQL> connect 'D:\CENTRAL\DATA.fdb' user 'sysdba' password 'masterkey';

Commit current transaction (y/n)?y

Committing.

Database: 'D:\CENTRAL\DATA.fdb', User: sysdba

SQL> show tables;

There are no tables in this database

SQL> CREATE TABLE DATA(ID CHAR(2), JUMLAH INT);

SQL> SHOW TABLES;

DATA

SQL> INSERT INTO DATA VALUES('01',10);

SQL> INSERT INTO DATA VALUES('01',20);

SQL> INSERT INTO DATA VALUES('01',30);

SQL> INSERT INTO DATA VALUES('01',50);

SQL> INSERT INTO DATA VALUES('02',60);

SQL> INSERT INTO DATA VALUES('03',60);

SQL> INSERT INTO DATA VALUES('03',70);

SQL> INSERT INTO DATA VALUES('02',70);

SQL> INSERT INTO DATA VALUES('06',70);

SQL> INSERT INTO DATA VALUES('05',70);

SQL> INSERT INTO DATA VALUES('05',60);

SQL> INSERT INTO DATA VALUES('05',90);

SQL> SELECT*FROM DATA

ID JUMLAH

====== ============

01 10

01 20

01 30

01 50

02 60

03 60

03 70

02 70

06 70

05 70

05 60

05 90

SQL> CREATE TABLE tab( t time, d date, ts timestamp );

SQL> INSERT INTO tab(t,d,ts) VALUES ('14:59:23', '2007-12-31', '2007-12-31 14:59');

SQL> SHOW TABLES;

DATA TAB

SQL> SELECT*FROM TAB;

T D TS

============= =========== =========================

14:59:23.0000 2007-12-31 2007-12-31 14:59:00.0000

SQL> SELECT CAST(t as varchar(13)), CAST(d as varchar(10)), CAST(ts as varchar(24))

CON> FROM TAB;

CAST CAST CAST

============= ========== ========================

14:59:23.0000 2007-12-31 2007-12-31 14:59:00.0000

SQL> SELECT D,EXTRACT(MONTH FROM D) FROM TAB;

D EXTRACT

=========== =======

2007-12-31 12

SQL> select avg(jumlah) from data;

AVG

=====================

SQL> select avg(jumlah) from data group by id;

AVG

=====================

SQL> select max(jumlah) from data;

MAX

============

SQL> select id,avg(jumlah) from data having avg(jumlah)>(select avg(jumlah) from data) group by id;

Statement failed, SQLCODE = -104

Dynamic SQL Error

-SQL error code = -104

-Token unknown - line 1, column 83

-group

SQL> select id,avg(jumlah) from data group by id having avg(jumlah)>(select avg(jumlah) from data) ;

ID AVG

====== =====================

02 65

03 65

05 73

06 70

SQL> select id,(select avg(jumlah)from data),avg(jumlah) from data group by id having avg(jumlah)>(select avg(jumlah) from data) ;

ID AVG AVG

====== ===================== =====================

02 55 65

03 55 65

05 55 73

06 55 70

SQL> select id,(select avg(jumlah)from data)as rata-rata,avg(jumlah) from data group by id having avg(jumlah)>(select avg(jumlah) from data) ;

Statement failed, SQLCODE = -104

Dynamic SQL Error

-SQL error code = -104

-Token unknown - line 1, column 47

SQL> select id,(select avg(jumlah)from data)as rata_rata,avg(jumlah)as diatas_rata from data group by id having avg

(jumlah)>(select avg(jumlah) from data) ;

ID RATA_RATA DIATAS_RATA

====== ===================== =====================

02 55 65

03 55 65

05 55 73

06 55 70

SQL> select id,(select avg(jumlah)from data)as rata_rata,avg(jumlah)as dibawah_rata from data group by id having avg(jumlah)<(select avg(jumlah) from data) ;

ID RATA_RATA DIBAWAH_RATA

====== ===================== =====================

01 55 27

SQL> select avg(jumlah) from data where id='01';

AVG

=====================

SQL>

SQL> CREATE TABLE EMPLOYEE(NAMA CHAR(20),

CON> TANGGAL_LAHIR DATE);

SQL> SHOW TABLES;

DATA EMPLOYEE

TAB

SQL> INSERT INTO EMPLOYEE VALUES('ELLEN','1993-09-12');

SQL> INSERT INTO EMPLOYEE VALUES('ELLEN AP','1993-04-12');

SQL> INSERT INTO EMPLOYEE VALUES('PURWANTO','1988-11-11');

SQL> SELECT*FROM EMPLOYEE

CON> ;

NAMA TANGGAL_LAHIR

==================== =============

ELLEN 1993-09-12

ELLEN AP 1993-04-12

PURWANTO 1988-11-11

SQL> SELECT *FROM EMPLOYEE WHERE EXTRACT(MONTH FROM TANGGAL_LAHIR)='11';

NAMA TANGGAL_LAHIR

==================== =============

PURWANTO 1988-11-11

SQL> SELECT *FROM EMPLOYEE WHERE EXTRACT(MONTH FROM TANGGAL_LAHIR)='11' OR EXTRACT(MONTH FROM TANGGAL_LAHIR)='04';

NAMA TANGGAL_LAHIR

==================== =============

ELLEN AP 1993-04-12

PURWANTO 1988-11-11

SQL> SELECT *FROM EMPLOYEE WHERE EXTRACT(DAY FROM TANGGAL_LAHIR)='11' ;

NAMA TANGGAL_LAHIR

==================== =============

PURWANTO 1988-11-11

SQL> SELECT *FROM EMPLOYEE WHERE EXTRACT(DAY FROM TANGGAL_LAHIR)='11' AND EXTRACT(MONTH FROM TANGGAL_LAHIR)='11' ;

NAMA TANGGAL_LAHIR

==================== =============

PURWANTO 1988-11-11

SQL> SELECT *FROM EMPLOYEE WHERE EXTRACT(YEAR FROM TANGGAL_LAHIR)='1993' ;

NAMA TANGGAL_LAHIR

==================== =============

ELLEN 1993-09-12

ELLEN AP 1993-04-12

SQL>

SQL> SELECT EXTRACT(YEAR FROM TANGGAL_LAHIR) AS TAHUN,EXTRACT(MONTH FROM TANGGAL_LAHIR) AS BULAN,EXTRACT(DAY FROM TANGGAL_LAHIR) AS TANGGAL FROM EMPLOYEE;

TAHUN BULAN TANGGAL

======= ======= =======

1993 9 12

1993 4 12

1988 11 11

purwant's blog

Tuesday, 14 November 2017

USER DAN TABLESPACE

MEMULAI DATABASE FIREBIRD