Dimanakah Letak Archive Log?

Caranya cukup mudah..
Karena environment saya di Linux RHEL, so step yang ada disini dilakukan di linux:

1. Masuk ke sqlplus

[prodmgr@hqhcmdb1 ~]$ sqlplus / as sysdba

2. Jalankan perintah berikut
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/db/oracle/data/tech_st/10.2.0/dbs/arch
Oldest online log sequence 720
Next log sequence to archive 721
Current log sequence 721

3. Berdasarkan informasi diatas, diketahui bahwa lokasi archive log ada di:
/u01/db/oracle/data/tech_st/10.2.0/dbs/arch

Dump Partial rows in a Tables

It's simple:

• If you have enough space on your disk, just create a temporary table and move the desired data to a new temporary table. Then dump the temporary table exp command:

$ exp [user]/[passwd] FILE=[filename].dmp FROMUSER=[username] TABLES=[tamporary table]

• If it's only one table (without join), you can directly use this syntax

exp [user]/[passwd] TABLES=[table] FILE=[filename].dmp query=\" where rownum<=10000\"

Grant Select on all Tables Owned By Specific User

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='OwerName') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to UserName';
END LOOP;
END;

Evolution of BI

Many people talk about BI and data warehouse things.
Let me give you a little information about the evolution of Business Intelligence (BI)

• Executive Information System (EIS)
• Decision Support System (DSS)
• Data Warehousing (DW) and Business Intelligence (BI)

Create table with primary and foreign key constraint

Referencing one Column in other table
Template:
CREATE TABLE schema.table_name
(
col1 dataType,
col2 dataType,
col3 dataType,
col.. dataType,
CONSTRAINT Constraint_name PRIMARY KEY(column_Name),
CONSTRAINT Constraint_name FOREIGN KEY (Column_Name) REFERENCES table_name(column_name_in_the_reference_table)
);


Sample:
1. Create the master table
CREATE TABLE myschema.kategori
(
kategoriid NUMBER(5),
katoriname VARCHAR2(30),
description VARCHAR2(50),
CONSTRAINT pkkategori PRIMARY KEY (kategoriid)
);

2. Create the child table
CREATE TABLE myschema.judulvideo
(
videotittleid NUMBER(5),
videotittlename VARCHAR2(50),
kategoriid NUMBER(5),
description VARCHAR2(50),
CONSTRAINT pkjudulvideo PRIMARY KEY(videotittleid),
CONSTRAINT fkjudulvideo FOREIGN KEY (kategoriid) REFERENCES kategori(kategoriid)
);

• Referencing two or more columns in other table (if the primary key has 2 or more columns)
  

CREATE TABLE schema.table_name
(
col1 dataType,
col2 dataType,
col3 dataType,
col.. dataType,
CONSTRAINT Constraint_name PRIMARY KEY(column_Name),
CONSTRAINT Constraint_name FOREIGN KEY (Column_Name1,Column_Name2 ) REFERENCES table_name(Column_Name1,Column_Name2 )
);


CREATE TABLE myschema.sewaheader
(
sewaid NUMBER(5),
memberid NUMBER(5),
videoid NUMBER(5),
videotittleid NUMBER(5),
tanggalsewa DATE DEFAULT SYSDATE,
tanggalkembali DATE,
jlhvideo NUMBER(3),
totalharga NUMBER,
jlhbayar NUMBER,
CONSTRAINT pksewaheader PRIMARY KEY(sewaid),
CONSTRAINT fksewaheader1 FOREIGN KEY(memberid) REFERENCES members(memberid),
CONSTRAINT fksewaheader2 FOREIGN KEY(videoid,videotittleid) REFERENCES video(videoid,videotittleid)
);

Create table with primary key constraint

Template:
CREATE TABLE myschema.table_name
(
col1 dataType,
col2 dataType,
col... dataType ,
CONSTRAINT Constraint_name PRIMARY KEY(column_Name)
);


Example:
CREATE TABLE myschema.judulvideo
(
videotittleid NUMBER(5),
videotittlename VARCHAR2(50),
kategoriid NUMBER(5),
description VARCHAR2(50),
CONSTRAINT pkjudulvideo PRIMARY KEY(videotittleid)
);


Or the Other way, you can try this
First, create the table
CREATE TABLE myschema.table_name
(
col1 dataType,
col2 dataType,
col... dataType ,
);

CREATE TABLE kategori
(
kategoriid NUMBER(5),
katoriname VARCHAR2(30),
description VARCHAR2(50)
)
TABLESPACE etiket_tbl;

Then Alter the table to add the primary key constraint
ALTER TABLE myschema.table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name);

ALTER TABLE myschema.kategori
ADD CONSTRAINT pkkategori
PRIMARY KEY (kategoriid)
USING INDEX
TABLESPACE etiket_idx;

Create Table

• Create table without define the primary key at the beginning

CREATE TABLE myschema.kategori
(
kategoriid NUMBER(5),
katoriname VARCHAR2(30),
description VARCHAR2(50)
)
TABLESPACE etiket_tbl;

• Adding the primary constraint into table

ALTER TABLE myschema.kategori
ADD CONSTRAINT pkkategori
PRIMARY KEY (kategoriid)
USING INDEX
TABLESPACE etiket_idx;