|
Solution Search:
in the same Oracle instance.
The database is Oracle 10g.
You have two schemas on the same Oracle instance: schemaA and schemaB. You have a procedure called proc1 in schemaA and you have a table emp on schemaB that you want to read from schemaA. Perform the following two steps: Step 1: Log onto schemaB. Grant references and select on emp to schemaA as follows: grant references, select on emp to schemaA; Step... More... Apr 2, 2007
What is the query to select empname and his managername from an emp table? This sounds like another homework question. However, it is also probably the most common example of a self-join ever devised. Everybody uses this example, and it's real easy to find on the Web, so there's no... More... Jan 17, 2006
between tables and views? A table is where you store your data. The table actually occupies space on disk. A sample table might be the EMPLOYEE table which has the columns EMPID, EMP_NAME, and SALARY.
A view is a stored query. A sample view might look like the following: CREATE VIEW emp_view AS SELECT empid,emp_name FROM employee; Notice that in the view, I have omitted the SALARY column. You can... More... Jan 11, 2007
type and sometimes the affected columns in my constraint name. This way, I will not have conflicts with two constraints trying to use the same name. For example, a PK constraint on the EMP table will be named EMP_PK. A unique constraint on the SSN column of the EMP table will be named EMP_SSN_UK. A Foreign Key constraint from EMP to DEPT will be named EMP_DEPT_FK. A NOT NULL constraint on the NAME column of...
More...
May 15, 2006
I have a table, emp, and two fields, empid and empname. The column empid has a primary key. My question is, how do I get the latest entry into the empname column with a single select statement? I'm not sure what you mean by "latest entry...
More...
Aug 1, 2006
I want to export a column in a table like (column ename in emp table) and import it later.
I have faced a situation where I had to export only four colunms in a table containing 20 columns. Oracle's export utility will export all columns of a table. You cannot use exp or 10g's new...
More...
Sep 22, 2005
How do I create a foreign key from the child table that references a UNIQUE key in the parent table? I have a table named employee with a field emp_id. I want to add an entry in the table every 10 minutes if the max value of emp_id is 10. After 10 minutes I want to add...
More...
Aug 28, 2006
To take an export of just this table, use the Oracle export utility similar to the following: exp userid=system/manager file=exp.dmp tables=scott.emp
The above example exports just the SCOTT.EMP table. You can export selected rows of data using the QUERY clause. This parameter lets you specify the equivalent of a WHERE clause on your table. An example might look like the following: exp userid=system... More... Dec 6, 2004
I took the full database export backup in an 11i apps database and tried to import a table from a production server called emp_dev_details from the schema powererp to the same setup in the test server to the same schema, but before importing there were 2,334 rows in the production table and there...
More...
Jun 6, 2006
executed and I want to optimize it.
Here is the query: SELECT E.DEPT_CODE, (SUM( SESSION_SCHEL )) AS AMOUNT
FROM HISTORY H, SESSIONS S, EMPLOYEE E, DEPARTMENT D
WHERE H.SESSION_CODE = S.SESSION_CODE AND H.EMP_ID = E.EMP_ID AND E.DEPT_CODE = D.DEPT_CODE AND S.SESSION_TYPE='D' AND
H.REQ_FLAG='Y' AND TRUNC(H.SESSION_DATE) >= TO_DATE('30-11-2004', 'DD/MM/RRRR')
AND TRUNC(H.SESSION_DATE) <= TO_DATE('24-04-2006', 'DD/MM/RRRR')
AND D.AREA_CODE = '18' GROUP BY E.DEPT_CODE
The sessions table contains 1,686,862 records. The history... More... Jun 12, 2006
using the packaged code. Any insight into why execution time is greatly increased by using the packaged code? Here's an example of the code: INSERT INTO target_table(tt_id, tt_disp, tt_date, tt_emp_1, tt_emp_2, tt_emp_3)
SELECT src_tab.src_id, src_tab.scr_disp, src_tab.scr_date, src_tab.scr_emp_1, src_tab.scr_emp_2, src_tab.scr_emp_3
FROM (SELECT row_number() over( ORDER BY SUBSTR(fn_cil_sort_format(SUBSTR(src_cil, 1, 8)), 1,
4), SUBSTR...
More...
Apr 18, 2006
I'm doing a search from one table and my goal is to show only the rows with the same value in one of the columns. For example, select emp_no, valid_from, valid_to from uddevalla.employee_degree_occupation where valid_from <= '&dag2' and valid_to >= '&dag1' and company_id = '01'This query will return a lot of rows, but I am only interested in seeing... More... Dec 2, 2005
b.job, b.sal, b.comm) VALUES (s.ename, s.job, s.sal, 150);
The above merge will update the bonus comm field to 700 if the ename in bonus matches the ename in emp. If not, a new bonus record is inserted from the emp table. Since you do not want to update and you have to use the update clause, you can change that clause to say: update set b.comm = b.comm I have to do an insert statement, and I want to do it with cursors. But if a record... More... Aug 10, 2006
example will show the differences between the two join operators. Let's assume I have the following tables: Table: DEPT DEPTNO DEPT_NAME
10 Human Resources
20 Accounting
30 Sales Table: EMP EMP_ID ENAME DEPTNO
1001 Bob Jones 10
1002 Sue Smith 10
1003 Joe White 20 With an INNER JOIN, we will only get those records that participate in the join from both tables, as can be seen in this example: SELECT d.dept...
More...
Oct 13, 2006
How to get the script (SQL) for the tables created in SQL script? You can use the DBMS_METADATA package to reverse-engineer the CREATE TABLE command for any table in the database. You can perform a query similar to the following: SELECT dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM dual; Or, you can query for all of your tables using the Data Dictionary...
More...
Mar 2, 2006
Hi, we have an emp table in Oracle. From that, I need to generate an output like this, for example: john smith sudheer deptno1 300 2400 2500 deptno2 2600 2700 2800. Can this be achived? If so, how? Please help me in this regard. Thanks in...
More...
Aug 30, 2005
In the command "analyze table emp estimate statistics sample 30%" how do I come up with the value, e.g., 30%? There's no standard formula for determining the estimate percentage when gathering table statistics, whether using ANALYZE or DBMS...
More...
Mar 6, 2006
What's the fastest way to export an Oracle table to ASCII-delimited format? Probably the fastest way is to use a simple SELECT statement in SQL*Plus and spool the output to a file. For instance, assume that my table EMP and three columns, EMPID, ENAME and...
More...
Jan 31, 2006
module that
joins many related tables to get the Project Cost transaction detail.
The view is quite slow when run wide open. One of the tables,
PA_COST_DISTRIBUTION_LINES_ALL, is set to a full table scan. Is there a
way I can put a hint in the view code to force the use of an index? Yes, you can put an index hint in your SQL. For example, let's say that you want to use index Idx_Emp_id to access your Employee table. Here...
More...
May 16, 2007
run the query 'select * from scott.emp;' it shows an error that no tables or views are present. Please help me. This has never happened before and I don't know what's the problem. First, verify that the table actually exists. Sign on to the database as SYSTEM and issue the following: SELECT owner,table_name FROM dba_tables WHERE owner='SCOTT' AND table_name='EMP'; If no rows are returned, the table was never created. Run...
More...
Jun 28, 2007
I have developed a replication system with master snapshot technology. When I try to refresh an individual table, e.g., Emp, the system tries to refresh all the
tables. What is the problem? What command are you using to refresh the snapshot? Are you sure you are not
refreshing the refresh group that the snapshot resides in? I...
More...
Mar 12, 2007
parent table, the child table will be
scanned once for each parent row deleted.
When you query from the parent to the child. Consider the EMP/DEPT example again. It is
very common to query the EMP table in the context of a DEPTNO. If you frequently query select * from dept, emp where emp.deptno = dept.deptno and dept.dname = :X; to generate a report or something, you'll find not having the index in place will slow
down...
More...
Oct 18, 2005
How can I copy a table with a LONG RAW column from one into another tablespace? It´s important to know that it is not allowed to change the LONG RAW datatype. Since you are using the LONG RAW datatype, your only option to move...
More...
Sep 14, 2005
multiple tables are being exported, the WHERE clause will be applied to each table in the export dump. For example, consider the following: exp userid=scott/tiger file=my_dump.dmp tables=dept,emp query='WHERE deptno=10'
In the above example, only those rows where DEPTNO=10 will be exported from the EMP and DEPT tables. For more information on the exp utility, have a look at the Oracle docs where these... More... Aug 15, 2007
List last name and hire date of any employee in the same department as Zlotkey
I always get puzzled up when asked some typical questions in interview like the nth highest salary in the EMP table
In SQL from two different tables select the first five highest salary
How can we find the second highest salary from three departments named D1, D2 and D3
How to select last ten max(sal) from EMP
List the...
More...
Jul 5, 2007
|
Oracle Content
Oracle Solutions
|