how to export or import specific tables from ORACLE
------------------------------------------------------------
the below commands should type in comand prompt only.
exp scott/scott@orcl file=C:\ravi\emp.dmp tables=emp
imp scott/scott@orcl file=C:\ravi\emp.dmp
or
SYNTAX FOR EXPORTING A TABLE
EXPDP USERNAME/PASSWORD@SERVICE DIRECTORY=DIRECTORY_NAME TABLES=TABLE_NAME DUMPFILE=ANY_NAME.DMP;
CREATE DIRECTORY EXPORT_EXAMPLE AS 'C:\ROHIT_BLOG\';
exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\" file=C:\ravi\emp1.dmp
how to drop an index in oracle
---------------------------------------
drop index F43121_101;
how to create a query index
------------------------------------
CREATE INDEX F43121_104 ON CRPDTA.F43121 (PRTRDJ)
how to create a bitmap index
------------------------------------
CREATE BITMAP INDEX F43121_101 ON CRPDTA.F43121 (PRRCDJ)
TABLESPACE dv_dw_idx
NOLOGGING
PARALLEL (DEGREE 8);
how to check the repository_id in odi from db
--------------------------------------------------------
select * from SNP_REM_REP
how to drop and recreate a table in stored procedure
----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(Table_nameIN VARCHAR2)
IS
CANTIDAD integer;
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = Table_name;
DBMS_OUTPUT.PUT_LINE(CANTIDAD);
IF (CANTIDAD >0) THEN
DBMS_OUTPUT.PUT_LINE(Table_name);
execute immediate 'DROP TABLE ' || Table_name;
END IF;
END;
how to drop a procedure in oracle
------------------------------------
drop procedure procedurename;
ODI-1551: The dbms_lock acquisition failed with a sql exception 'ORA-06550:
---------------------------------------------------------------------------
GRANT EXECUTE ON DBMS_LOCK TO ODI(ODI USERNAME);
how to write ddl statements(create,alter,drop,etc...) in stored procedure in ORACLE
---------------------------------------------------------------------
EXECUTE IMMEDIATE 'DDL STATEMENT';
DDL STATEMENTS SHOULD BE IN SINGLE CODES.
HOW TO EXECUTE STORED PROCEDURE IN ORACLE
------------------------------------------------
begin
STORED PROCEDURE NAME (test_wc_prf);
end;
how to view the content of stored procedure in Oracle
-------------------------------------------------------------
select text from all_source where name='SPC_CREATE_VIRTUAL_COLUMNS' order by line
How to get a dropped table IN ORACLE
-----------------------------------------
flush table table_name to before drop;
FLASHBACK TABLE WC_MFG_WORK_ORDER_F_BKP_1_4 TO BEFORE DROP;
how to know the values of biappsconfiguration parameters in ODI(DV_BIACOMPONENT)
-------------------------------------------------------------------
select CASE WHEN '<%=odiRef.getLoadPlanStepInstance( "STEP_NAME" )%>' in (select distinct group_code from C_PARAMETER_VALUE_FORMATTER_V where PARAM_CODE=
'JDE_PURCHASING_ORDER_TYPE_LIST')
THEN (select param_value
from C_PARAMETER_VALUE_FORMATTER_V
where PARAM_CODE= 'JDE_PURCHASING_ORDER_TYPE_LIST'
and group_code='<%=odiRef.getLoadPlanStepInstance( "STEP_NAME" )%>'
and datasource_num_id = 10)
ELSE
(select param_value from C_GL_PARAM_VALUE_FORMATTER_V where PARAM_CODE= 'JDE_PURCHASING_ORDER_TYPE_LIST' and datasource_num_id = 10)
END
select * from ALL_TABLES where TABLE_NAME like'%W_WRKFC_EVT_MONTH_F%';
how to know whether DB is in Unicode or not
---------------------------------------------------
select value from nls_database_parameters where parameter='NLS_CHARACTERSET'
how to chage the data type from Byte to CHAR
------------------------------------------------------
alter session set nls_length_semantics=CHAR;
how to rename table
-----------------------------
rename w_party_org_d to w_party_org_d_bkp
how to find existing sequences in oracle
-----------------------------
select * from USER_SEQUENCES
select * from ALL_SEQUENCES
how to modify a column in oracle
----------------------------------------
alter table WC_SURVEY_QSTN_DS modify SURVEY_QSTN_TYPE varchar2(2)
primary key script
----------------------------
alter table WC_SURVEY_QSTN_DS add Constraint primarykey_WC_SURVEY_QSTN_DS primary key(INTEGRATION_ID,DATASOURCE_NUM_ID)
how to drop a primary key
--------------------------------
ALTER TABLE WC_EXIT_RSN_DS DROP CONSTRAINT primarykey_WC_EXIT_RSN_DS
how to create a table with data like existing table in DB
------------------------------------------------------------
create table w_position_dh_bkp_12122014 as (select * from w_position_dh)
how to use case statement in where clause
-----------------------------------
TO_DATE((case when TO_DATE(W_UPDATE_DT,'YYYY/MM/DD HH24:MI:SS')=TO_DATE(sysdate,'YYYY/MM/DD HH24:MI:SS') THEN sysdate ELSE sysdate-1 END),'YYYY/MM/DD HH24:MI:SS')
to remove locks on table
------------------------------------
exec dbms_stats.unlock_schema_stats('DV_DW');
HOW TO DROP A COLUMN IN ORACLE
-----------------------------------------
alter table
table_name
drop column
col_name1; -- drop one column
Inline view
-----------------------------------------------------
An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex queries by removing join operations
and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.
This feature is commonly referred to in the MSSQL community as a derived table, and in the Postgres community simply refers to it as a subselect (subselects are inline views +
subqueries in Oracle nomenclature).
Examples
Example inline view:
SELECT *
FROM ( SELECT deptno, count(*) emp_count
FROM emp
GROUP BY deptno ) emp,
dept
WHERE dept.deptno = emp.deptno;
Another good example of an inline view is:
SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
( SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id ) b
WHERE a.department_id = b.department_id
AND a.salary = b.maxsal;
The above query display the employees who earn the highest salary in each department.
The WITH clause,
------------------------------------------------
or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or
resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary
table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to
retrieve the required information. They merely demonstrate the use of the WITH clause.
Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
WHERE e.deptno = dc.deptno;
Using a WITH clause this would look like the following.
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
dept_count dc
WHERE e.deptno = dc.deptno;
The difference seems rather insignificant here.
What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc1,
emp m,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
Using the WITH clause this would look like the following.
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.
If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple
references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumented MATERIALIZE hint tells the
optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.
WITH dept_count AS (
SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...
WITH dept_count AS (
SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...
Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.
WITH
dept_costs AS (
SELECT dname, SUM(sal) dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;
------------------------------------------------------------
the below commands should type in comand prompt only.
exp scott/scott@orcl file=C:\ravi\emp.dmp tables=emp
imp scott/scott@orcl file=C:\ravi\emp.dmp
or
SYNTAX FOR EXPORTING A TABLE
EXPDP USERNAME/PASSWORD@SERVICE DIRECTORY=DIRECTORY_NAME TABLES=TABLE_NAME DUMPFILE=ANY_NAME.DMP;
CREATE DIRECTORY EXPORT_EXAMPLE AS 'C:\ROHIT_BLOG\';
exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\" file=C:\ravi\emp1.dmp
how to drop an index in oracle
---------------------------------------
drop index F43121_101;
how to create a query index
------------------------------------
CREATE INDEX F43121_104 ON CRPDTA.F43121 (PRTRDJ)
how to create a bitmap index
------------------------------------
CREATE BITMAP INDEX F43121_101 ON CRPDTA.F43121 (PRRCDJ)
TABLESPACE dv_dw_idx
NOLOGGING
PARALLEL (DEGREE 8);
how to check the repository_id in odi from db
--------------------------------------------------------
select * from SNP_REM_REP
how to drop and recreate a table in stored procedure
----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(Table_nameIN VARCHAR2)
IS
CANTIDAD integer;
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = Table_name;
DBMS_OUTPUT.PUT_LINE(CANTIDAD);
IF (CANTIDAD >0) THEN
DBMS_OUTPUT.PUT_LINE(Table_name);
execute immediate 'DROP TABLE ' || Table_name;
END IF;
END;
how to drop a procedure in oracle
------------------------------------
drop procedure procedurename;
ODI-1551: The dbms_lock acquisition failed with a sql exception 'ORA-06550:
---------------------------------------------------------------------------
GRANT EXECUTE ON DBMS_LOCK TO ODI(ODI USERNAME);
how to write ddl statements(create,alter,drop,etc...) in stored procedure in ORACLE
---------------------------------------------------------------------
EXECUTE IMMEDIATE 'DDL STATEMENT';
DDL STATEMENTS SHOULD BE IN SINGLE CODES.
HOW TO EXECUTE STORED PROCEDURE IN ORACLE
------------------------------------------------
begin
STORED PROCEDURE NAME (test_wc_prf);
end;
how to view the content of stored procedure in Oracle
-------------------------------------------------------------
select text from all_source where name='SPC_CREATE_VIRTUAL_COLUMNS' order by line
How to get a dropped table IN ORACLE
-----------------------------------------
flush table table_name to before drop;
FLASHBACK TABLE WC_MFG_WORK_ORDER_F_BKP_1_4 TO BEFORE DROP;
how to know the values of biappsconfiguration parameters in ODI(DV_BIACOMPONENT)
-------------------------------------------------------------------
select CASE WHEN '<%=odiRef.getLoadPlanStepInstance( "STEP_NAME" )%>' in (select distinct group_code from C_PARAMETER_VALUE_FORMATTER_V where PARAM_CODE=
'JDE_PURCHASING_ORDER_TYPE_LIST')
THEN (select param_value
from C_PARAMETER_VALUE_FORMATTER_V
where PARAM_CODE= 'JDE_PURCHASING_ORDER_TYPE_LIST'
and group_code='<%=odiRef.getLoadPlanStepInstance( "STEP_NAME" )%>'
and datasource_num_id = 10)
ELSE
(select param_value from C_GL_PARAM_VALUE_FORMATTER_V where PARAM_CODE= 'JDE_PURCHASING_ORDER_TYPE_LIST' and datasource_num_id = 10)
END
select * from ALL_TABLES where TABLE_NAME like'%W_WRKFC_EVT_MONTH_F%';
how to know whether DB is in Unicode or not
---------------------------------------------------
select value from nls_database_parameters where parameter='NLS_CHARACTERSET'
how to chage the data type from Byte to CHAR
------------------------------------------------------
alter session set nls_length_semantics=CHAR;
how to rename table
-----------------------------
rename w_party_org_d to w_party_org_d_bkp
how to find existing sequences in oracle
-----------------------------
select * from USER_SEQUENCES
select * from ALL_SEQUENCES
how to modify a column in oracle
----------------------------------------
alter table WC_SURVEY_QSTN_DS modify SURVEY_QSTN_TYPE varchar2(2)
primary key script
----------------------------
alter table WC_SURVEY_QSTN_DS add Constraint primarykey_WC_SURVEY_QSTN_DS primary key(INTEGRATION_ID,DATASOURCE_NUM_ID)
how to drop a primary key
--------------------------------
ALTER TABLE WC_EXIT_RSN_DS DROP CONSTRAINT primarykey_WC_EXIT_RSN_DS
how to create a table with data like existing table in DB
------------------------------------------------------------
create table w_position_dh_bkp_12122014 as (select * from w_position_dh)
how to use case statement in where clause
-----------------------------------
TO_DATE((case when TO_DATE(W_UPDATE_DT,'YYYY/MM/DD HH24:MI:SS')=TO_DATE(sysdate,'YYYY/MM/DD HH24:MI:SS') THEN sysdate ELSE sysdate-1 END),'YYYY/MM/DD HH24:MI:SS')
to remove locks on table
------------------------------------
exec dbms_stats.unlock_schema_stats('DV_DW');
HOW TO DROP A COLUMN IN ORACLE
-----------------------------------------
alter table
table_name
drop column
col_name1; -- drop one column
Inline view
-----------------------------------------------------
An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex queries by removing join operations
and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.
This feature is commonly referred to in the MSSQL community as a derived table, and in the Postgres community simply refers to it as a subselect (subselects are inline views +
subqueries in Oracle nomenclature).
Examples
Example inline view:
SELECT *
FROM ( SELECT deptno, count(*) emp_count
FROM emp
GROUP BY deptno ) emp,
dept
WHERE dept.deptno = emp.deptno;
Another good example of an inline view is:
SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
( SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id ) b
WHERE a.department_id = b.department_id
AND a.salary = b.maxsal;
The above query display the employees who earn the highest salary in each department.
The WITH clause,
------------------------------------------------
or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or
resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary
table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to
retrieve the required information. They merely demonstrate the use of the WITH clause.
Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
WHERE e.deptno = dc.deptno;
Using a WITH clause this would look like the following.
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
dept_count dc
WHERE e.deptno = dc.deptno;
The difference seems rather insignificant here.
What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc1,
emp m,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
Using the WITH clause this would look like the following.
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.
If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple
references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumented MATERIALIZE hint tells the
optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.
WITH dept_count AS (
SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...
WITH dept_count AS (
SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...
Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.
WITH
dept_costs AS (
SELECT dname, SUM(sal) dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;
No comments:
Post a Comment