Server Administration :: Oracle Hints In SQL Statement
Sep 30, 2011What is the use of using Hints within SQL statements w.r.t query tuning? How to know which hint to use when?
View 1 Replies
ADVERTISEMENT
Ordered Hints In Oracle
Aug 5, 2007There's a fairly popular Ordered Hint example on the web as follows:
<CODE>
select /*+ ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm
from
emp e,
bonus b
where
e.ename = b.ename
;
</CODE>
I would like to know what the "parallel(e, 4)" clause does. Where does the "4" come from? What is "parallel" here? I also have another question: If I have 5 tables--T_OREGON, T_UTAH, T_VIRGINIA, T_TEXAS, and T_OKLAHOMA--lined up in a join right behind a FROM, coming in at row counts of
T_OREGON: [a lot of rows; a lot more than T_UTAH]
T_UTAH: [smaller than T_OREGON] 40550 rows
T_VIRGINIA: 14 rows
T_TEXAS: 66 rows
T_OKLAHOMA: 8 rows
from T_OREGON or, T_UTAH ut, T_VIRGINIA va, T_TEXAS tx, T_OKLAHOMA ok...
my question is, if an Ordered hint can be used here, is it that the smallest table (in this case T_OKLAHOMA) gets placed first in the join and the rest of the table ascension in the join doesn't matter? Or is it that T_OKLAHOMA gets placed first in the join, followed by T_VIRGINIA [at 14 rows], followed by T_TEXAS, T_UTAH, and finally T_OREGON?
Server Administration :: How To Run ADDM Report For Particular SQL Statement
Apr 22, 2013We are not using enterprise manager. Now, i want to generate ADDM report for the particular SQL stmt. How can i do that?
View 2 Replies View RelatedServer Administration :: Cannot Find SQL Plan For Insert Statement
Dec 27, 2011I can not find the sql plan for insert statement,why?
Select sql_text From v$sql a
Where a.SQL_ID = '0yungrk19a277';
-------------------------------
INSERT INTO OS_USERBILL_ACTV_READ_MON_DT
(MONTHNO, MAILCODE, OPERTYPE, PROVCODE, AREACODE, DAY_TOTALCOUNT, TOTALCOUNT,
CREATETIME, MODIFYTIME, SENDER_TYPE, SENDER_DOMAIN)
VALUES
(:B9 , :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , SYSDATE, SYSDATE, :B7 , :B8 )
[code]....
Server Administration :: Undo Info Of Insert Statement
Jun 24, 2012If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.
I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:
" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "
My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.
Server Administration :: Can Select Statement Generates Redo
Dec 7, 2011Whenever any transaction happen in database redo has generated for this transaction. Do select statement treat as a transaction as it doesn't modify any thing in database. And If select statement should not be a transaction, there should not be any redo generation for select statement.
So is select statement generate redo? If yes then Why ?
Server Administration :: Record Audit Info About Sql Statement Run By User
Jul 19, 2012I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.
I am giving you all the statement I issued...
SQL> create user saimon identified by abc1;
User created.
SQL> grant connect, resource to saimon;
Grant succeeded.
SQL> audit table, insert table by saimon by session;
Audit succeeded.
SQL> show parameter audit
NAME TYPE VALUE
-------------------- ----------- -------------
audit_file_dest string /u01/app/oracle/admin/orcl/adum
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DBSQL>
[oracle@DBTEST ~]$ sqlplus saimon/abc1
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> create table TB1 (id number, name varchar2(20));
Table created.
SQL> create table TB3 (id number, name varchar2(20));
Table created.
SQL> select USERNAME, TERMINAL, OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe
2 from user_audit_trail;
USERNAME TERMINAL OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME
---------- ---------- ---------- ----------------- ----------------------- ----------
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB3
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB1
conn / as sysdba
Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?
SQL> show user
USER is "SYS"
SQL> SELECT audit_option, failure, success, user_name
2 FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME
----------------------------------- ---------- ---------- ------------------------------
TABLE BY SESSION BY SESSION SAIMON
INSERT TABLE BY SESSION BY SESSION SAIMON
SQL & PL/SQL :: Usage Of Hints In The Views
Oct 11, 2011I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows
create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);
my question is how to use hints on this table if your are using view to access the data from internal table.
Normal structure is if i don't wrong:-
index( <<view name|view alia name>> <<table name|table alias name>> name of index)
Consider my case
select * from view_t130 where <index_column> --not picking up index
i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.
select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>
how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.
SQL & PL/SQL :: Fix Execution Plan Using Hints
Mar 6, 2012I am executing below query, but optimizer generating 2 different plans for the same. I don't want to use sql profiles to fix execution plan.
Query
SELECT R.VENDOR_RECORD_SEQ_NO ,
R.VENDOR_SUBJECT_SEQ_NO ,
NVL(D.RESOLVED_VALUE, D.ORIGINAL_VALUE) VAL,
D.CONTROL_COLUMN_SEQ_NO
[code]....
RAC & Failsafe :: Query Doesn't Have Any Parallel Hints
Jul 10, 2013Below query doesn't have any parallel hints. Though, it is without where clause, so full scan is must but why "PX Deq Credit: send blkd" occurred?
SELECT ROWID, REGION, STATE, CITY, DEALERNAME, DEALERCODE, DEALERID, BUSINESSUNIT, GARDEALERID, MASTERCLAIMNUMBER, CLAIMNUMBER, COMPANY, POLICYNUMBER, STARTDATE, ENDDATE, POLICYISSUEDEALERNAME, ISSUEDEALERCODE, CUSTOMERNAME, CUSTOMERADDRESS, ACCIDENTDATE, ACCIDENTTIME, INTIMATIONDATE, INTIMATIONTIME, REGISTRATIONDATE, REGISTRATIONTIME, DRIVERNAME, ACCIDENT_DESCRIPTION, INTIMATION_DELAY_REASON, INTIMATORNAME, INTIMATORMOBILENO, CLAIM_INTIMATED_BY, TOWING_ASSISTANCE, ACCIDENTPLACE, ACCIDENTLOCATION, GARAGENAME, WORKSHOPADDRESS, WORKSHOPMOBILENO, MANUFACTURINGYEAR, CHASSISNUMBER, ENGINENUMBER, REGISTRATIONNUMBER, VEHICLEMODEL, BUSINESSTYPE, TOTALSI, CLAIMEDAMOUNT, ESTIMATEDAMOUNT, "LABOUR(E)", "PARTS(E)", "PAINTING(E)", EXCESS,
[code]....
understand about "PX Deq Credit: send blkd"..
SQL Server Query - Oracle Does Not Support OUTER APPLY Statement
Feb 1, 2012DECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000)
INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000);
--select * from @MainTable;
[code]...
The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, how should the last piece be written to make it work in Oracle?
Networking And Gateways :: Running DML Statement On Oracle Transparent Gateway For SQL Server?
Dec 5, 2011how can i run dml statement on the oracle transparent gateway for sql server ,such as insert ,update,delete.
View 4 Replies View RelatedServer Utilities :: IMP-00017 / Statement Failed With ORACLE Error 1950
Apr 28, 2011While importing, I got the following error. How to resolve it?
IMP-00017: following statement failed with ORACLE error 1950: CREATE TABLE "table_name".....
Server Administration :: Monitoring Script Required For Oracle 9.2.0.7.0 On Windows 2000 Server
Apr 8, 2010My manager is asking to create a SQL scrip, which will provide following information.
Our database is 9.2.0.7.0 on Windows 2000 Server
-How many records Insert / Update or Delete daily in my Oracle database?
- Check Archive log / Redo Log switching information?
-Check database growth ?
Server Administration :: Installing Oracle Database 10.1.0.2.0 On Windows 2003 Server 64bit?
Jan 11, 2011i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
Server Administration :: Restoring The Oracle Services In Windows 2008 Server
Jun 27, 2012I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
Server Administration :: Unable To Find Server Process In Oracle Database For Specific Application / Client
Feb 13, 2013I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
Server Administration :: While Creating Index ORA-00603 / ORACLE Server Session Terminated By Fatal Error
Jul 26, 2010While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
Server Administration :: Oracle 11g R2 Installation On Windows 2008 64 Bit Server
Apr 19, 2011I want to install Oracle 11g R2 in windows 2008 64 bit server. How can I know whether my server is ready to install Oracle ie is all components are available in server or any patch is to be applied etc.
View 3 Replies View RelatedServer Administration :: How To Connect Oracle Client To Remote Server DB
Jun 29, 2013I'm trying to connect a oracle client application on the client machine to a remote oracle server on the server machine but i get a connection fail.
On the server machine I configured oracle server in the following way:
Installed oracle server. Created a database "DB_Test" with the database configuration assistant Created a LISTNER with the Oracle NET Manager with the following parameter:
Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521 Created "dbtest" service with the Oracle NET Manager with with the following parameter:
Service Name: "dbtest" Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521
All services on the server machine are running and I opened port number (1521) in the router. On the client machine I installed SQL PLUS and SQL Developer.
With SQL Plus as by the official documentation I have entered the following command:
CONNECT username/password@[//]host[:port][/service_name]. In my case is:
CONNECT SYSTEM/oracledb@//ENZOVAIO:1521/testdb.
With SQL Developer I have entered the same parameter.
But with both SQLPlus and SQL Developer the connection fails.
Server Administration :: Connect 500+ Client To Remote Oracle Server?
Aug 10, 2010we have oracle server. we have to connect 500+ client machine to oracle server.
is it i need to install client version to all 500+ or any other shortcut way?
Server Administration :: Find Or Set New Oracle Instance Name On Backup Server
Sep 22, 2010We performed image copy of production Oracle server (OS and instances) to a backup server. After a few weeks, we try to restore a latest Oracle database backup from production server to backup server. As we know, Oracle instance must be unique on the network.
Even we log on to backup server and bring up the instance, I think that still point to production instance since all init file, TNSNAMES.ora and listener file are still same. If we restore the database, we will end up bring down the production instance and restore on top of productions. How to change instance name on backup server including TNSNAMES, sqlnet, listener files in order for us to restore Oracle database from production to backup server?
Server Administration :: Oracle 10g - Database Server Startup / Shutdown
Jun 29, 2011I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.
View 5 Replies View RelatedServer Administration :: Oracle Database Server - Logon Denied
Jul 19, 2011I am connected as System. It was the only user I set-up a password when installed the database on personal computer.
SQL> alter user sys identified by mypass007
2 /
User altered.
SQL> connect sys/mypass007
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
[code]...
SQL> conn sysoper/mypass007
ERROR:
ORA-01017: invalid username/password; logon denied
Server Administration :: Oracle DB Server Migration Checklist
Apr 11, 2012We will be having a meeting with our client regarding their Database Server Migration (They are planning to buy a new server). Their current database is Oracle 10gR2, they will not upgrade to 11g, they just plan to migrate to a new more powerful machine.
I was planning to ask the following questions.
1. Specifications of the current server and the new one.
2. Operation system (I think they will use same OS, just an updated one)
3. Can the business afford full downtime on current servers?
4. Size of the DB, because it can take hours to move large files.
And is there documentation regarding Server Migration (Change of machine only, not database upgrade or anything,
Server Administration :: Oracle 10G Client Works With 11G Server?
Jul 17, 2013Does a 10g oracle client works with an Oracle 11g server?
View 1 Replies View RelatedServer Administration :: How To Install Oracle 8i And 9i Databases On Same Server
Feb 20, 2011How to install oracle 8i and 9i databases on same server for example i want that both type of databases run on same server.
View 7 Replies View RelatedServer Administration :: Oracle 10g Server Went In Hanging Condition?
May 18, 2012Sometimes our db server goes in hanging condition. It does not save any transaction & user waits for long. When I restart, it starts working fine.What should I do, I mean regarding diagnosis & performance tuning.
View 1 Replies View RelatedServer Administration :: Updating Jdk 6 On Oracle Application Server 10.1.3 On EBS 12.0.4?
Mar 2, 2011In Process of upgrading Oracle EBS 12.0.4 to 12.1.1 I updated the $IAS_ORACLE_HOME/appsutil/jdk to jdk 6u24. After replacing the original jdk with jdk 6u24 when I attempted to start the apps adoacorectl.sh failed with below error.
Quote:ld.so.1: /d4/app/oracle/apps/12.0.0/vdd700/web/10.1.3/appsutil/jdk/bin/java: fatal: libjli.so: open failed: No such file or d
irectory
I followed below steps to update the jdk
1. extracted the jdk-6u24-solaris-sparcv9.tar.Z to $IAS_ORACLE_HOME/appsutil/jdk
2. Copied lib from old jdk to $IAS_ORACLE_HOME/appsutil/jdk
3. Copied lib from oldjdk/jre to $IAS_ORACLE_HOME/appsutil/jdk/jre
4. The java was not infact in jdk/bin rather it was /bin/sparcv9 so I copied them to /jdk/bin. The same way copied the executables from /jdk/jre/bin/sparcv9 to /jdk/jre/bin .
Server Administration :: From Where To Download Oracle 9i Database Server
Aug 17, 2010I have some requirement wherein I need to install Oracle 9i database server (9.2.0.7). I am not getting it on Oracle website. where can I get the download.
View 1 Replies View Related
