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