1. Select name, type from SYS. all_sourceWhere instr (upper (text), 'tbname')> 02. Select name, type from user_dependencies where referenced_name = upper ('tablename ');3.
Dynamically input a table name and create a new table in the Stored
Method 1: the simplest method (database creation required)
Backup:Directly copy the admin and oradata folders in the Oracle directory and the database under the ora92 directory to other storage for backup.
Recovery:1. Create a database that is
Oracle
1. Create an oracle Stored Procedure
Create or replace procedure proce_test (paramin in varchar2, paramout out varchar2, paraminout in out varchar2)
As
Varparam varchar2 (28 );
Begin
Varparam: = paramin;
Paramout: = varparam | paraminout;
End;
Create or replace view ov_applicant of ob_applicant with object OID (applicantid) as select * from applicant; -- ov_applicant is equivalent to applicant.
Insert into ov_applicant values g_ob_applicantinfo;
-- Insert data in g_ob_applicantinfo
Recently, a Project Database of the company was converted to SQL Server (2000 or 2005) as a database of Oracle. At the beginning, I found some information on the Internet, I found a tool called swissql. I tried it and failed to find it again. later,
Left:
Select
E. last_name, E. department_id, D. department_name
From
Employees E
Left
Join
Administrative ments d
On
(E. department_id
=
D. department_id );
Equivalent
Select
E. last_name, E. department_id, D.
From:
Http://www.gbunix.com/htmldata/2004_06/2/5/article_53_1.html
The data dictionary dict always belongs to the Oracle user SYS.1. User:Select username from dba_users;Change PasswordAlter user spgroup identified by spgtest;2.
In database queries, data in the tree structure is often queried. You need to use the data of a field as the parent node of the next record to continue the query, if you do not know how many nodes there are, writing SQL queries again and again will
1. Create a stored procedure
Create
Or
Replace
Procedure My_procedure
Is
--
The cursor is used here.
Cursor Cur
Is
Select
*
From Users
Where Length (name)
>
10 ;
Begin
For
User
In Cur
Loop
Insert
Into
Case: one field in an application table is the primary key. when inserting data into the table, the data is first put in the temporary table (without the primary key) and then inserted into the application table.
At this time, if duplicate data
[Application Scenario] preparations before the system goes online. Generally, the Dump File Import method is often used for data migration. Because the data is imported by one user, a permission error (which can be ignored) is reported during the
Oracle Database Synchronization The test is successful between two Oracle databases. The following is a summary:
Requirements:
There are two Oracle database servers A and B (A and B can be on the same Intranet or two independent machines on the
A snapshot of an Oracle database is a table that contains query interceptions on one or more tables or views in a local or remote database. Because snapshots are a subset of a master table, you can use snapshots to speed up database queries. When
Oracle As a data transformation in the original relevant database, the view is mainly used to view some data in the relevant table. You can set Oracle A view is a moving window through which you can see the data you are interested in.
A
1. Create a table named Test
2. Add data to the test table
Insert into test (student, course, score)Select 'zhang san', 'China', 78 from dual UnionSelect 'zhang san', 'mat', 87 from dual UnionSelect 'zhang san', 'English ', 82 from dual
You can also use the field name like '% string %' in Oracle. However, this method cannot use indexes and is not fast.
Use a string to compare the instr function (field name, 'string') to 0 to get more accurate search results.
Processing of Oracle
Create or replace package pkg_tools
Is
Type resultdata is ref cursor;
Procedure sp_page (p_pagesize int, -- number of records per page
P_pageno int, -- current page number, starting from 1
P_sqlselect varchar2, -- query statement, including sorting
After several hours, I finally realized why the self-started script could not be successfully executed. It turned out to be a space error. The steps are described as follows:
1. Modify the dbstart and dbshut scripts.
The default dbstart script
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