Software Testing

select trunc(months_between(date2,date1)) months,        date2 - add_months(date1,trunc(months_between(date2,date1))) days   from (select to_date('25-Aug-2013','dd-Mon-yyyy') date1,to_date('23-Oct-2013','dd-Mon-yyyy') date2           from dual        ) MONTHS DAYS 1 28

Issue: Unable to connect to Oracle DB in OEL (Linux) from windows 1) Go to $cd $ORACLE_HOME/dbs 2) $mv /<ORACLE_HOME>/product/10.2.0/server/dbs/orapwtestdb /<ORACLE_HOME>/product/10.2.0/server/dbs/orapwtestdb_bkp 3) $orapwd file=orapw$ORACLE_SID password=<enter your password> force=y entries=10 4) $ls -ltr orapw* 5) $sqlplus / as sysdba SQL> select * from v$pwfile_users; USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE ... Now trying logging into database from windows...

Procedure followed to bring up the Oracle DB: 1) Login with the su - oracle user 2) sqlplus / as sysdba 3) Check the lsnrctl status if it is not up start by using lsnrctl start 4) Execute "startup" command after login to sqlplus / as sysdba 5) If you encounter with this error: ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener Check if ORACLE_HOME_LISTNER path is set in the .profile Execute the .profile Execute tnsping <ORASID> 6) Login  sqlplus / as sysdba 7) Execute "startup" Encountered with following error: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora' 8) Login with sqlplus sys/Password as sysdba 9) Check for the existence of spfile under this path "/home/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora" 10) Execute " create pfile from spfile='/home/app/oracle/product/11.2.0/db_1/d...

BEGIN   FOR c IN   (SELECT c.owner, c.table_name, c.constraint_name    FROM user_constraints c, user_tables t    WHERE c.table_name = t.table_name    AND c.status = 'ENABLED'    ORDER BY c.constraint_type DESC)   LOOP     dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);   END LOOP; END;

1)Identify tables with 0 rows 2)Issue following command on those tables ALTER TABLE > ALLOCATE EXTENT; 3) Now EXP utility will work

SELECT * FROM User_Tables s WHERE s.num_rows=0

update PROPS$ SET VALUE$='CHAR' where NAME='NLS_LENGTH_SEMANTICS';

Query to update a column of a table with random values within a range of 1 to 21 UPDATE <%table_name%> SET <%column_name%> = trunc(dbms_random.value(2,21))

declare cursor c1 is select column_name , table_name from user_tab_columns where data_type = 'VARCHAR2' ; -- and table_name = 'TABLE_NAME' ; vSQL varchar2 ( 32676 ) := null ; vParam varchar2 ( 100 ) := 'STING_YOU_WANT_TO_SEARCH' ; vcnt number ; begin for x in c1 loop begin vSQL := 'select count(*) from ' ||x.table_name|| ' where ' ||x.column_name|| ' like ' ||chr( 39 )|| '%' ||vparam|| '%' ||chr( 39 ); execute immediate vSQL into vcnt; if vcnt > 0 then dbms_output.put_line( 'Found in ' ||x.table_name|| ' ' ||x.column_name); end if ; exception when others then dbms_output.put_line( 'Problem in ' ||vSQL); end ; end loop ; end ;

1) update PROPS$ SET VALUE$='CHAR' where NAME='NLS_LENGTH_SEMANTICS'; 2) ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR; 3) ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

Scenario: I have used this command in 11g where I was not able to use "exp" utility for exporting tables with no rows. Issue: I was not able to export tables with '0' rows, objects were not created while taking .dmp file. Resolution: Execute below query on tables from where you want to export "select'alter table '||table_name || ' allocate extent;'from user_tables"

Scenario: I have used this command in 11g where I was not able to use "exp" utility for exporting tables with no rows. Issue: I was not able to export tables with '0' rows, objects were not created while taking .dmp file. Resolution: Execute below query on tables from where you want to export select'alter table '||table_name || ' allocate extent;'from user_tables

Try this on scott schema SET TERMOUT OFF SET FEEDBACK OFF SPOOL current_employees.xls SELECT ename||' '||ename AS "Employee Name", sal, hiredate, NVL(TO_CHAR(comm),'No Commission') AS "Commission", job FROM emp; OR Select * from emp; SPOOL OFF EXIT;

Materialized views are disk based and update periodically base upon the query definition. Views are virtual only and run the query definition each time they are accessed.

A View created by a single table, can be updated and the updates made on a view will be reflected on the the base table. An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table. In order to be updatable, a view cannot contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list or finally (with some exceptions) a JOIN . Views that are not updatable can be modified using an INSTEAD OF trigger.