ORACLE 12C 12項強化功能之一 建TABLE 時可以直接用Sequence

oracle 12c database new features Oracle 12項 資料庫新特性 
Default value of column can use a sequence.nextval
在以往ORACLE 9i ,10g,11g  建sequence 都要建一個Trigger 將Seqence  作改變 

參考Create table with default sequence
 Creating a Table with a DEFAULT ON NULL Column Value: Example 

The following statement creates a table myemp, which can be used to store employee data. The department_id column is defined with a DEFAULT ON NULLcolumn value of 50. Therefore, if a subsequent INSERT statement attempts to assign a NULL value to department_id, then the value of 50 will be assigned instead.

CREATE TABLE myemp (employee_id number, last_name varchar2(25),
                    department_id NUMBER DEFAULT ON NULL 50 NOT NULL);

以上官網的範例 
以下我作個實際範例 

create table example (
production varchar2(100),
productioserial number default on null 1 not null);



insert into example(production) values('item2');
commit;

insert into example(production) values('item2');
commit;

select * from example;

Results ：  看樣子是會帶DEFAULT 值出 ，可是這不是我要的自動增加 
PRODUCTION  PRODUCTIOSERIAL
----------------------------------------------------------------
item1     1 
item2     1

CREATE TABLE example2 (productname varchar2(100),serial NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1));

insert into example2(productname) values ('itema');

 insert into example2(productname) values ('itemb');

PRODUCTNAME               SERIAL

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

itema                          1 

itemb                          2 

Install ORACLE 12C (安裝12C 部份畫面）

心智圖-ORACLE IOT,CLUSTER TABLE

 

REFERENCE 

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/clustrs003.htm

CREATE MV

-如有建SYNONYM 要先行DROP 掉
--drop SYNONYM FPDB5001 ;
--drop SYNONYM FPDB5003 ;
--drop SYNONYM FPDB5007 ;
--drop SYNONYM FPDB5008 ;
--drop SYNONYM FPDB5013 ;
--drop SYNONYM FPDB5015 ;
--drop SYNONYM FPDB5016 ;
--drop SYNONYM KADB1017 ;
Grant Create MATERIALIZED VIEW to XXX<==先給權限給使用者
這樣USER才能 建MV
--建MV START WITH TO_DATE 是指定何時開始作的時間,將裡面的時間先行改掉
CREATE MATERIALIZED VIEW FPDB5001 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5001@LINKACER;
CREATE MATERIALIZED VIEW FPDB5003 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5003@LINKACER;
CREATE MATERIALIZED VIEW FPDB5007 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5007@LINKACER;
CREATE MATERIALIZED VIEW FPDB5008 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5008@LINKACER;
CREATE MATERIALIZED VIEW FPDB5013 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5013@LINKACER;
CREATE MATERIALIZED VIEW FPDB5015 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5015@LINKACER;
CREATE MATERIALIZED VIEW FPDB5016 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from FPDB5016@LINKACER;
CREATE MATERIALIZED VIEW KADB1017 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from KADB1017@LINKACER;
CREATE MATERIALIZED VIEW KADB1023 TABLESPACE "IDB_III_IPMIS" REFRESH complete START WITH to_date('02-06-2011 23:50:00','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1 AS select * from KADB1023@LINKACER;
COMMIT

select * from user_mview_refresh_times;

--測試中 先DROP 掉 ,重建算時間
--drop MATERIALIZED view FPDB5001;
--drop MATERIALIZED view FPDB5003;
--drop MATERIALIZED view FPDB5007;
--drop MATERIALIZED view FPDB5008;
--drop MATERIALIZED view FPDB5013;
--drop MATERIALIZED view FPDB5016;
--drop MATERIALIZED view FPDB5015;
--drop MATERIALIZED view KADB1017;
--drop MATERIALIZED view KADB1023;

---改使用SESSION 時間顯示方式
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
select * from user_mview_refresh_times;

ORA-ERRORS

ORA-16401:	archivelog rejected by RFS
Cause:	An attempt was made to re-archive an existing archivelog. This usually happens because either a multiple primary database or standby database(s) or both are trying to archive to this standby database.
Action:	See alert log and trace file for more details. No action is necessary; this is an informational statement provided to record the event for diagnostic purposes.

調校一個DBLINK 連線時間太久

由於我方(政府機關機房)的DB 要連線去 另一個政府機關的ORACLE DB 去作DBLINK, 可是反映說連線動作時間太久,  經過思索一番認為是FW及IPS  檢查封包時間太久(是可以過)
due to a dataflow change , origin they sync data  to 2 Database from local government  to two Bureau under Ministry of Economic Affair. one of  database local on in my office . another one is main database on
other government  machines farm. now is change 3 tier layout just keep one database . our database is canceling soon. we need create a dblink  to their db. but when we test . to slow .
so I think it will be TCP/IP head check  spend many time.
原本(ORIGIN Response  time ) need : 16 MINS .
after  addition IP and port to white list on FW.  it from 16 mins to 13 Mins.then
I ask soc to add 2 ip on 2 IPS . it from 13 mins to 7mins .
still to slow .

Rman 下誤刪arch logs 導致備份不成時的作法

rman (id/passwd@instance name)
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
change archivelog all crosscheck

轉文Pivoting INSERT

http://www.oracleabc.com/forums/viewtopic.php?p=100
A data warehouse can receive data from many different sources. Some of these source systems may not be relational databases and may store data in very different formats from the data warehouse. For example, suppose that you receive a set of sales records from a nonrelational database having the form:

product_id, customer_id, weekly_start_date, sales_sun, sales_mon, sales_tue,
sales_wed, sales_thu, sales_fri, sales_sat

The input table looks like the following:

SELECT * FROM sales_input_table;

PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------
111 222 01-OCT-00 100 200 300 400 500 600 700
222 333 08-OCT-00 200 300 400 500 600 700 800
333 444 15-OCT-00 300 400 500 600 700 800 900

In your data warehouse, you would want to store the records in a more typical relational form in a fact table sales of the sh sample schema:

prod_id, cust_id, time_id, amount_sold

Note:
A number of constraints on the sales table have been disabled for purposes of this example, because the example ignores a number of table columns for the sake of brevity.

Thus, you need to build a transformation such that each record in the input stream must be converted into seven records for the data warehouse's sales table. This operation is commonly referred to as pivoting, and Oracle Database offers several ways to do this.

The result of the previous example will resemble the following:

SELECT prod_id, cust_id, time_id, amount_sold FROM sales;

PROD_ID CUST_ID TIME_ID AMOUNT_SOLD
---------- ---------- --------- -----------
111 222 01-OCT-00 100
111 222 02-OCT-00 200
111 222 03-OCT-00 300
111 222 04-OCT-00 400
111 222 05-OCT-00 500
111 222 06-OCT-00 600
111 222 07-OCT-00 700
222 333 08-OCT-00 200
222 333 09-OCT-00 300
222 333 10-OCT-00 400
222 333 11-OCT-00 500
222 333 12-OCT-00 600
222 333 13-OCT-00 700
222 333 14-OCT-00 800
333 444 15-OCT-00 300
333 444 16-OCT-00 400
333 444 17-OCT-00 500
333 444 18-OCT-00 600
333 444 19-OCT-00 700
333 444 20-OCT-00 800
333 444 21-OCT-00 900

Example 14-7 Pivoting

The following example uses the multitable insert syntax to insert into the demo table sh.sales some data from an input table with a different structure. The multitable INSERT statement looks like the following:

INSERT ALL INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;