aSkDba.org Forum

jellyter on "Oracle Database 11g 051 SQL Fundamentals I Exam"

Oracle Database 11g: SQL Fundamentals I Exam available online
Exam Number: 1Z0-051
Associated Certifications: Oracle 11g DBA OCA
Oracle 10g DBA OCA
Oracle9i DBA OCA
Oracle PL/SQL Developer Certified Associate
Exam Price: US$ 125
Pricing may vary slightly by country or by localized currency. Check Pearson VUE’s website for exact pricing in your country.
Exam Registration: Register
Duration: 120 minutes
Number of Questions: 70
Passing Score: 60%
Global leading Practice Exam,Low Price Preferential activity,Please lock http://www.nowexam.com/Oracle.html

Amit Bansal on "NO SPAM Entertained on this forum"

Hi All,

For past few days, I am observing spam ( some exams site) posting content on the forum. Tried deleting the content for 2-3 times so that concerned person desist from doing again but I have today finally deleted the user.

Any kind of spam will not be entertained on this forum.

Regards
Amit

jellyter on "Oracle Enterprise Manager 11g + Oracle E-Business Suite Packs"

Oracle Enterprise Manager 11g (OEM 11g) was announced in April 2010. The new systems management features in this latest release have generated a great deal of interest from existing and prospective users of our Applications Management Pack (AMP) and Applications Change Management Pack (ACMP) for Oracle E-Business Suite. We covered our roadmap for AMP and ACMP at OAUG/Collaborate earlier this year, which triggered some lively discussions about our priorities.

This is a compelling example of how you can affect the course of a product. In response to your (sometimes very vocal) feedback, we have begun projects to certify upcoming releases of AMP and ACMP with the latest Oracle Enterprise Manager 11g platform.

We appreciate your sharing your AMP/ACMP priorities with us, and we're excited about the prospect of running our EBS plug-ins with the latest Grid Control 11g release. We look forward to sharing more details with you as soon as they're available.

http://www.nowexam.com study materails & test guides for the global IT engineers!
NowExam dumps help you pass any it certifications,100% Ensure Making you Pass the IT Certification!

Amit Bansal on "What are the pre-requisites of ASM on AIX 6.1?"

Hi Bhatia,

I do not think that there are separate pre-requisites for ASM . You need to ensure that you apply all packages mentioned in OS pre-requisite for oracle installation. Also check for MOS (Metalink) to ensure that your version is certified to run on AIX 6.1

Cheers
Amit

bhatia.ish@gmail.com on "What are the pre-requisites of ASM on AIX 6.1?"

Hi,

What are the pre-requisites of ASM on AIX 6.1?

Regards,
Bhatia

jellyter on "Oracle Database 10g Administrator 1z0-007 certification"

Oracle Database 10g is Oracle’s largest introduction of new functionality and is Oracle’s most innovative release, leading the database industry into new ground in clustering, automation, high availability and more.

Oracle Database 10g Administrator Certification combines training, experience, and testing to ensure that you have a strong foundation and expertise in the industry’s most advanced database management system.

Oracle reserves the right to take action against any candidate involved in fraudulent activities, including, but not limited to, fraudulent use of vouchers, promotional codes, reselling exam discounts and vouchers, cheating on an exam, alteration of score reports, alteration of completion certificates, violation of exam retake policies or other activities deemed fraudulent by Oracle.

If Oracle determines, in its sole discretion, that fraudulent activity has taken place, it reserves the right to take action up to and including, but not limited to, decertification of a candidate's Oracle Certified Associate, Oracle Certified Professional and/or OCM credentials, temporary, indefinite or permanent ban of a candidate from Oracle certification programs, notification to a candidate's employer, and notification to law enforcement agencies. Candidates found committing fraudulent activities forfeit all fees previously paid to Oracle, or to Oracle's authorized vendors, and may be required to pay additional fees for services rendered.
View the Oracle Certification Program Candidate Agreement which requires your agreement before the start of each exam.
http://www.nowexam.com study materails & test guides for the global IT engineers!
NowExam dumps help you pass any it certifications,100% Ensure Making you Pass the IT Certification!

jellyter on "nowexam 1Z0-147 Training and Preparation"

his exam is for PL/SQL Developers on Oracle9i Database or Oracle Database 10g.
Practice Exams
Oracle authorized practice exam from Self Test Software. 1Z0-147 Program with PL/SQL http://www.nowexam.com study materails & test guides for the global IT engineers!
Creating Procedures
[ ] Define what a stored procedure is
[ ] List the development steps for creating a procedure
[ ] Create a procedure
[ ] Describe the difference between formal and actual parameters
[ ] List the types of parameter modes
[ ] List the methods for calling a procedure with parameters
[ ] Describe the DEFAULT option for parameters
[ ] Create a procedure with parameters
[ ] Invoke a procedure that has parameters
[ ] Define a subprogram in the declarative section of a procedure
[ ] Describe how exceptions are propagated
[ ] Remove a procedure

Creating Functions
[ ] Define what a stored function is
[ ] Create a function
[ ] List how a function can be invoked
[ ] List the advantages of user-defined functions in SQL statements
[ ] List where user-defined functions can be called from within an SQL statement
[ ] Describe the restrictions on calling functions from SQL statements
[ ] Remove a function
[ ] Describe the differences between procedures and functions

Managing Subprograms
[ ] Contrast system privileges with object privileges
[ ] Grant privileges
[ ] Contrast invokers rights with definers rights
[ ] Identify views in the data dictionary to manage stored objects
NowExam dumps help you pass any it certifications,100% Ensure Making you Pass the IT Certification!

Creating Packages
[ ] Use DESCRIBE command to describe packages and list their possible components
[ ] Identify a package specification and body
[ ] Create packages: Create related variables , cursors, constants, exceptions, procedures, and functions
[ ] Designate a package construct as either public or private
[ ] Invoke a package construct
[ ] Use a bodiless package
[ ] Drop Packages
[ ] Identify benefits of Packages

jellyter on "NowExam 1z0-242 Oracle Certification"

"High quality and Value for the 1Z0-242 Exam."
EditByBSEditor: NowExam 1Z0-242 Exam Features
High quality and Value for the 1Z0-242 Exam.
NowExam Practice Exams for PeopleSoft Application Developer II: App Engine & Integration 1Z0-242 are written to the highest standards of technical accuracy, using only certified subject matter experts and published authors for development.

100% Guarantee to Pass Your Other Oracle Certification exam and get your Other Oracle Certification Certification.
We guarantee your success in the first attempt. If you do not pass the 1Z0-242 Exam (PeopleSoft Application Developer II: App Engine & Integration) on your first attempt we will give you a FULL REFUND of your purchasing fee AND send you another same value product for free.

NowExam 1Z0-242 Downloadable.
Printable Exams (in PDF format) Our Exam 1Z0-242 Preparation Material provides you everything you will need to take your Other Oracle Certification exam. The Other Oracle Certification Certification Details are researched and produced by Professional Certification Experts who are constantly using industry experience to produce precise, and logical. You may get Other Oracle Certification exam questions from different web sites or books, but Logic is the key. Our Product will help you not only pass in the first Other Oracle Certification exam try, but also save your valuable time .

Comprehensive questions with complete details about 1Z0-242 exam. 1Z0-242 exam questions accompanied by exhibits. Verified Answers Researched by Industry Experts and almost 100% correct. Drag and Drop questions as experienced in the Real Other Oracle Certification exam. 1Z0-242 exam questions updated on regular basis. Like actual Other Oracle Certification Certification exams, 1Z0-242 exam preparation is in multiple-choice questions (MCQs). Tested by many real Other Oracle Certification exams before publishing. you can free download NowExam 1z0-242 Oracle Certification now.

jellyter on "Adaptive Cursor Sharing in Oracle Database 11g Release 1"

DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peaks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The following code provides and example of adaptive cursor sharing.

First we create and populate a test table.

DROP TABLE acs_test_tab;

CREATE TABLE acs_test_tab (
id NUMBER,
record_type NUMBER,
description VARCHAR2(50),
CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
);

CREATE INDEX acs_test_tab_record_type_i ON acs_test_tab(record_type);

DECLARE
TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
l_tab t_acs_test_tab := t_acs_test_tab();

BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
IF MOD(i,2)=0 THEN
l_tab(l_tab.last).record_type := 2;
ELSE
l_tab(l_tab.last).record_type := i;
END IF;

l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;

FORALL i IN l_tab.first .. l_tab.last
INSERT INTO acs_test_tab VALUES l_tab(i);

COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);The data in the RECORD_TYPE column is skewed, as shown by the presence of a histogram against the column.

SELECT column_name, histogram FROM user_tab_cols WHERE table_name = 'ACS_TEST_TAB';

COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID NONE
RECORD_TYPE HEIGHT BALANCED
DESCRIPTION NONE

3 rows selected.

SQL>Next, we query the table and limit the rows returned based on the RECORD_TYPE column with a literal value of "1".

SET LINESIZE 200

SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
1

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID cgt92vnmcytb0, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable.

VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 1;

SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
1

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan that it thinks is optimium for query by peeking at the value of the bind variable. The only problem is, this would be totally the wrong thing to do for other bind values.

VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;

SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
100000

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type';

SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N

1 row selected.

SQL>If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.

VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;

SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

MAX(ID)
----------
100000

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type

Plan hash value: 509473618

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 48031 | 422K| 138 (2)| 00:00:02 |
-----------------------------------------------------------------------------------This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as "Y".

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type';

SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
9bmm6cmwa8saf 1 Y Y

2 rows selected.

SQL>Information about the cursor sharing histograms, statistics and selectivity is displayed using the V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY views respectively.

SQL> SELECT * FROM v$sql_cs_histogram WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
-------- ---------- ------------- ------------ ---------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf 1 0 0
319A4A1C 4171522382 9bmm6cmwa8saf 1 1 1
319A4A1C 4171522382 9bmm6cmwa8saf 1 2 0
319A4A1C 4171522382 9bmm6cmwa8saf 0 0 1
319A4A1C 4171522382 9bmm6cmwa8saf 0 1 1
319A4A1C 4171522382 9bmm6cmwa8saf 0 2 0

6 rows selected.

SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf 1 2064090006 Y 1 50001 499 0
319A4A1C 4171522382 9bmm6cmwa8saf 0 2342552567 Y 1 3 3 0

2 rows selected.

SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf 1 =L_RECORD_T 0 0.432283 0.528346

1 row selected.

SQL>

Anonymous on "PKRS-1009 failed to start ASM instance"

Permissions on init+ASM2.ora is right.I am only facing this problem when i was starting ASM through srvctl command otherwise it's working fine.

Please note this point.

jumma on "DR rebuild- Data restore issue"

Download demo ost recovery software to convert .OST files to .PST file. Saved .PST file can be accessed with MS Outlook with other save options including .EML and .MSG which can be accessed with Outlook Express and MS Outlook.

Anonymous on "ORA-16055"

you have a standby database and the standby needs a log that likely you already removed where it thinks it should be /u05/arch/

jumma on "Database shutdown hangs."

If 'Shutdown Normal' hangs
Symptom:

‘Shutdown normal’ has been issued to the Oracle database, but Oracle will not shutdown.
Possible Causes and Remedies:

There are two likely causes:

1. ‘Shutdown Normal’ will only shutdown Oracle when there are no remaining connections to Oracle. It is likely therefore that an application still has an active session.

Possible Remedies:
* close all open sessions
* or issue a ‘shutdown immediate’.
2. If using Oracle Parallel Server issuing ‘Shutdown normal’ on more than one node at a time can cause the system to hang. This is a recognised Oracle problem (Oracle 8.0.5).

Possible remedies:
* Only issue ‘Shutdown normal’ on one node at a time, and only issue it on the next node when the previous node has completed its shutting down.
* Alternately, use ‘shutdown immediate’ instead.

Saurabh Sood on "Datapump dumpfile with dates"

Hi,

As you mentioned earlier, and that i missed..... You are using this on windows ..

The syntax which I have mentioned is not for windows but will work only for unix platforms only..

Saurabh Sood on "Datapump dumpfile with dates"

Hi,

Do not copy-paste the command from link as the character ` will change.
Please write the command manually and check again.

egotah on "Datapump dumpfile with dates"

Thank you for the feedback, I have that and here is the error..
E:\backup\omahalead>expdp parfile=ftp.par

Export: Release 10.2.0.1.0 - Production on Monday, 17 May, 2010 8:58:26

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39124: dump file name "æ+%d%m%y_%H%M%SÆ`.dmp" contains an invalid substitution variable
LRM-00113:multiple values not allowed for parameter 'logfile'

Here is the command.. from the parfile.

schemas=(FTP_USER,FTP_SOIL,FTP_PAINT,D_NEPA,BVCONNECT,SERAPEUM,RES_LEAD,RL_FTP,SWAMP)
DIRECTORY=datapump_dir
dumpfile=FTP_SRRL-date+%d%m%y_%H%M%S.dmp
logfile=ftp_SRRL- 'date +%d%m%y_%H%M%S`.LOG
job_name=ftp_expdp
keep_master=N

** I just copied the command from your link **
Am running this from DOS prompt on windows and this is 10.2.0.3 release.

Saurabh Sood on "Datapump dumpfile with dates"

Hi,

You are using :

date '+%d%m%Y_%H%M%S'

but you have to put the ` sign before the "date" and end with the same after S'.

You can refer to following link for the details:

http://askdba.org/weblog/2009/06/generating-datapump-export-dump-with-dumpfilename-containing-current-date-and-time/

Cheers!!!

egotah on "Datapump dumpfile with dates"

Can some one direct me how to generate Datapump Export Dump with Dumpfile name Containing Current Date and Time.

I tried this format with error..
dumpfile=myexport-date '+%d%m%Y_%H%M%S'.dmp
logfile=myexport-date '+%d%m%Y_%H%M%S'.log

Amit Bansal on "Oracle RAC OVM image"

Hi,

I think this is not possible. Since I presume you will also change the hostnames. If it is only OCR and voting disk it is possible. But you cannot change hostnames.

rbaclagan on "Oracle RAC OVM image"

Does anyone know if it is possible to take a snapshot of an existing RAC environment running on OVM and redeploy this as a new RAC cluster with minor tweaks such as ip addresses, OCR, Vote disks, etc?