Oracle Basic Notes collation and case analysis 2____oracle

/*
===================================================================

==========================================
========================= * * * tp_orders table space, size 10M, file size can automatically grow, allow file expansion, maximum for unrestricted create A_oe user's default table space for tp_orders password bdqn Grant Connect,resource permission party A_HR user's employee/-Create tablespace and user and grant access to database create tablespace tp_orders datafile ' e:\e disk \tp_

orders01.dbf ' size 10M autoextend on;
--Creates user create username A_oe identified by bdqn default Tablespace tp_orders-grants permission grant Connect,resource to A_oe;
Grant SELECT on Test.employee to A_oe;


Grant SELECT on Test.bumen to A_oe;

SELECT * from Test.employee;
/* Use sequence to generate the value of department number/* Starting from 60, the interval is 10, the maximum is 10000 of the sequence of objects dept_seq/select * from Bumen; --Creates a sequence of create sequence dept_seq start with increment by MaxValue 10000-insert data into bumen values (dept_seq.ne
Xtval, ' academic department ');
INSERT into bumen values (dept_seq.nextval, ' academic department 1 ');

--The work before data migration drop sequence dept_seq;
Create sequence Dept_seq start with increment by MaxValue 10000; CREATE TABLE Deptbak as SelECT * from Bumen;
SELECT * from Deptbak;

--Test inserted data insert into Deptbak values (Dept_seq.nextval, ' personnel ');

/* Create a public synonym for the Dept table in A_oe mode to allow access to any user who is able to connect to the database * *-Create a Test Dept Table CREATE TABLE Dept as select * from Test.bumen;

SELECT * FROM dept;
--Create public synonym of synonyms p_sy_dept for a_oe.dept;
--Grant permission grant Select on Test.customers to A_oe;
Grant create public synonym to A_oe;


SELECT * from P_sy_dept;
/* Toggle user, operation using test user/*-View and manipulate employee table select * from Customers;
--Creating a reverse index for the customer number CREATE index index_reverse_customer_id on customers (customer_id) reverse;
--Create a bitmap index for the local column create bitmap index index_nls_territory on customers (nls_territory);

--Creates a composite index for first and last name columns CREATE index index_cus on customers (Cust_fiest_name,cust_last_name); /* Create a range from the order table/---(1) completed-(2) Create a partition created table Rangeorders (order_id number () primary key,--Order No. order_d Ate date NOT null--order Date Order_mode VARCHAR2 (8) NOT null--order mode customer_id number (6) not nul L,--customer number Order_Status number (2),-order status Order_total number (8,2), total pricing sales_rep_id number (6), --Sales Rep ID promotion_id Number (6)--promoter ID) partition by range (order_date) (Partition PA Rt1 values less than (to_date (' 2013-01-01 ', ' yyyy-mm-dd ')), partition part2 values less than (to_date (' 2014-01-01 ', ' yyy Y-mm-dd '), partition part3 values less than (to_date (' 2015-01-01 ', ' yyyy-mm-dd ')), partition part4 values less than (to_ Date (' 2016-01-01 ', ' yyyy-mm-dd ')), partition Part5 values less than (to_date (' 2017-01-01 ', ' yyyy-mm-dd ')), partition par T6 values less than (maxvalue)--insert test data into Rangeorders (order_id,order_date,order_mode,customer_id,order_

STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID) VALUES (1, ' 2017-02-09 ', ' online orders ', 2,1,323.23,1,2); Insert into Rangeorders (order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,
PROMOTION_ID) VALUES (2, ' 2016-11-09 ', ' door-to-door purchase ', 1,2,56.00,2,1); INSERT INTO rangeOrders (order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) VALUES (3,
' 2017-12-20 ', ' acquaintance recommended ', 3,1,6000,1,2; Insert into Rangeorders (order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,
PROMOTION_ID) VALUES (4, ' 2015-12-02 ', ' online orders ', 5,2,365,2,2); Insert into Rangeorders (order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,
PROMOTION_ID) VALUES (5, ' 2017-12-09 ', ' door-to-door purchase ', 3,1,3210,1,2); Insert into Rangeorders (order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,
PROMOTION_ID) VALUES (6, ' 2014-11-11 ', ' online orders ', 3,1,630,2,2); Insert into Rangeorders (order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,

PROMOTION_ID) VALUES (7, ' 2017-01-01 ', ' door-to-door purchase ', 2,1,6300,1,2);
--View the data in the table select * FROM Rangeorders--query each partition for data select * from Rangeorders partition (part1);
SELECT * from Rangeorders partition (part2);
SELECT * from Rangeorders partition (PART3); SELECT * from RangEorders partition (PART4);
SELECT * from Rangeorders partition (PART5);

SELECT * from Rangeorders partition (PART6);

--View partition status Select Table_name,partition_name from User_tab_partitions; --Change an existing table to a partition table create table Rangeorder partition by Range (order_date) (partition part1 values less than (' 2013-01 -01 ', ' yyyy-mm-dd '), partition part2 values less than (to_date (' 2014-01-01 ', ' yyyy-mm-dd ')), partition part3 values les S Than (To_date (' 2015-01-01 ', ' yyyy-mm-dd ')), partition part4 values less than (to_date (' 2016-01-01 ', ' yyyy-mm-dd ')), PA Rtition Part5 values less than (to_date (' 2017-01-01 ', ' yyyy-mm-dd ')), partition part6 values less than (maxvalue)) as SE

Lect * from orders; /* interval partition (Automation)/-Create a partition table (according to one table per year) CREATE table SALES_INTERVAL1 partition by range (order_date) interval Numtoyminterval (1

, ' year ')--according to a table (partition Part1 values less than (to_date (' 2017/01/01 ', ' yyyy/mm/dd ')) as a select * from Orders; --View partition status Select Table_name,partition_name,tablespace_name froM user_tab_partitions where Table_name=upper (' sales_interval1 '); --Insert a test data insert into SALES_INTERVAL1 (Order_id,order_date,order_mode,customer_id,order_status,order_total,sales_

REP_ID,PROMOTION_ID) VALUES (98, ' 2018/01/03 ', ' acquaintances recommended ', 3,1,9658,2,2); /* Note: The record just added is now definitely not, so 1. First select Table_name,partition_name,tablespace_name from User_tab_partitions where Table_
Name=upper (' Sales_interval1 ');
See how much of the next partition is 2. Copy the partition just queried, EG:SYS_P21 3. Execute SELECT * FROM SALES_INTERVAL1 partition (SYS_P21);

* * SELECT * from SALES_INTERVAL1 partition (SYS_P21);
--Add partition ALTER TABLE Rangeorder add partition part7 values less Tahn (to_date (' 2018-01-01 ', ' yyyy-mm-dd '));
--Deletes the partition ALTER TABLE Rangeorder drop partition part3;    --Mobile partition ALTER TABLE Rangeorder move partition part1 tablespace works01;  --WORKS01 is the table space name,/* 1. Create a separate table Space 2. Move the partition's data into this tablespace to 3. Let this tablespace be read-only-as a system login to create tablespace tb_name datafile
' e:\oracle\tbdb.dbf ' size 10M;
--Authorize ALTER user test quota unlimited on tb_name; --moving ALTER TABLE rangeorder mOve partition Part1 tablespace tb_name;
--set to read only alter tablespace Tp_name read;

--set to read-write alter tablespace Tp_name read write; * * After class brief answer/-(1) Create a table under test User stock_received creation table stock_received (stock_id number, stock_date Date, cost varchar2
(50))--insert data into stock_received values (myseq.nextval, ' 2017/03/05 ', ' Description one ');
INSERT into stock_received values (myseq.nextval, ' 2017/01/05 ', ' Description II ');
INSERT into stock_received values (myseq.nextval, ' 2017/02/05 ', ' description three ');
INSERT into stock_received values (myseq.nextval, ' 2017/04/05 ', ' Description four ');
INSERT into stock_received values (myseq.nextval, ' 2017/05/05 ', ' description five ');
INSERT into stock_received values (myseq.nextval, ' 2017/06/05 ', ' Description Six ');
INSERT into stock_received values (myseq.nextval, ' 2017/05/05 ', ' description seven ');
INSERT into stock_received values (myseq.nextval, ' 2017/04/05 ', ' description eight ');
INSERT into stock_received values (myseq.nextval, ' 2017/02/05 ', ' description nine ');
INSERT into stock_received values (myseq.nextval, ' 2017/01/05 ', ' description ten '); INSERT into stock_received values (MYSEQ.NExtval, ' 2017/08/05 ', ' Description 11 '); --Creates a sequence create sequence named Myseq myseq start with 1000 increment to MaxValue 1100 cycle-(2) Create a public synonym for

Synonym p_stock_received for stock_received-gives A_oe a permission to view the stock_received grant Select on p_stock_received to A_oe;
--Log in with A_oe and test to see if stock_received table select * from p_stock_received;
--↑ Test Success-(3) Create 3 range partitions in stock_received based on stock_date columns CREATE TABLE range_stock_received partition by Range (Stock_date) ( Partition P1 values less than (to_date (' 2017/01/01 ', ' yyyy-mm-dd ')), partition P2 values less than (' 2017/03/01 ', ' Yyyy-mm-dd '), partition P3 values less than (to_date (' 2017/05/01 ', ' yyyy-mm-dd ')), partition P4 values less than (maxvalue
) as SELECT * FROM stock_received-View the data in each partition select * from range_stock_received partition (P1);
SELECT * FROM range_stock_received partition (P2);

SELECT * FROM range_stock_received partition (p3);

 --(4) CREATE index index_stock_id on stock_received (stock_id) by creating a progressively indexed column on the ID of the table;