The things that Oracle does

Source: Internet
Author: User

1.oracle View Operations
Creating view: Create view name as query data;
Delete view: Drop view name;

Cases:
CREATE VIEW MyView as SELECT * fromTest_person;(You can modify the view, the modified data will affect the base table)
CREATE VIEW MyView as SELECT * fromTest_person withRead only;(Non-modifiable view)
Update MyView set age=' + ' where id = ' 1 ';
Drop View MyView;
Note: The views and tables in the database can execute statements such as SELECT, INSERT, UPDATE, Delete, and so on


2.oracle Master-Slave database:
The establishment of a master-slave database is generally based on the following three considerations:
1, Disaster tolerance: Standby library in a remote location, the main library does not exist, the standby can be immediately taken over, no recovery time
2, Load Balancing: The main library to do additions and deletions, prepare the library to do the query, so many query services do not occupy the main library resources
3. Data centralization and distribution: This mode is mainly used to centralize data from branch office to head office, or distribute it from head office to branch office, if the company needs to synchronize the data
Very few, the other inter-company business system is not the same company developed synchronization function mainly through the database synchronization software implementation.

3.oracle table rollback to an action statement at a specified time

ALTER TABLE table name enable row movement;
Flashback table name to timestamp to_timestamp (' 2011-03-04 05:00:00 ',' yyyy-mm-dd hh24:mi: SS ');

4. Add database table column SQL

ALTER TABLE table_name Add (column_name type);
ALTER TABLE table_name Add (column_name type);
Ex
ALTER TABLE E_rpt_iq_teamperform Add (psnnumzaizhihege integer);
ALTER TABLE E_rpt_iq_teamperform Add (psnnumzaizhihejiyou integer);

5. Modify Column Name sql

Format: ALTER TABLE <table_name> rename column <column_old_name> to <column_new_name>;
Ex:alter table e_rpt_iq_teamperform Rename column psnnumzaizhihejiyou to psnnumzaizhijiyou;

6.Oracle querying the information of locked tables

Select object_name, Machine, S.sid, s.serial#
From GV$locked _object L, dba_objects O, GV$session S
where l.object_id = o.object_id
and l.session_id = S.sid;

see which session caused it.
Select B.username,b.sid,b.serial#,logon_time
From v$locked _object a,v$session b
where a.session_id = B.sid order by B.logon_time;

Kill the process.
alter system Kill session ' sid,serial# '; (SID and Serial#是从上面的那个sql中查询出来的)

7. Modify database table column names

ALTER TABLE (TABLE_NAME) Rename column (Column_old_name) to (Column_new_name);

8.oracle before decimal 0 does not display the problem


Select To_char (0.1,' fm9999990.9999 ') from dual;

The things that Oracle does

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.