Unix: Search for a pattern in all the files in all the directory

find . -name "*" -print xargs grep -i "string_to_find"

OR

grep -i "string_to_find" `find . -name "*"`

Finding for multiple file names in single command:

find . \( -name "*.qry" -o -name "*.sql" \) -print

How are numbers stored in Oracle?

This is really good link.

It explains how the numbers are converted from Oracle internal format to real format (decimal/hexa..).

How do i store the row counts of all tables …

The query is really good. Doing dynamic sql in SQL.



SQL> select
table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from 'table_name))
,'/ROWSET/ROW/C')) count
from user_tables;


TABLE_NAME COUNT
------------------------------ ------
DEPT 4
EMP 14
BONUS 0
SALGRADE 5



Ref: http://laurentschneider.com/wordpress/2007/04/how-do-i-store-the-counts-of-all-tables.html

Oracle: Date format element 'ww'

About Oracle: Date format element 'ww'

If you fetch past the last row in the result set, the values of the target variables are undefined.
Note: Eventually, the FETCH statement fails to return a row. When that happens, no exception is raised. To detect the failure, use the cursor attribute %FOUND or %NOTFOUND.





Cast in Oracle for type casting

COALESCE function

In Oracle/PLSQL, the coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.
The syntax for the coalesce function is:
coalesce( expr1, expr2, ... expr_n )

IF expr1 IS NOT NULL THEN

RETURN expr1;

ELSIF expr2 IS NOT NULL THEN

RETURN expr2;

....

ELSE

RETURN NULL ;

END IF;

Oracle is an object-relational database management system (ORDBMS), which means that users can define additional kinds of data--specifying both the structure of the data and the ways of operating on it--and use these types within the relational model. This approach adds value to the data stored in a database. User-defined datatypes make it easier for application developers to work with complex data such as images, audio, and video. Object types store structured business data in its natural form and allow applications to retrieve it that way. For that reason, they work efficiently with applications developed using object-oriented programming techniques.

SCN, the System Change Number

Ref: http://www.adp-gmbh.ch/ora/concepts/scn.html

SCN, the System Change Number

The scn is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.
The current system SCN can be queried using dbms_flashback.get_system_change_number.
The SCN of the last checkpoint can be found in v$database.checkpoint_change#.
The SCN is needed in a flashback table .. to scn ... statement.

Is database in a consistent state?
The SCN plays an important role to determine if the database is in a consistent state when the database is brought online. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don't match, the database is in an inconsistent state.
Converting a time to scn
smon_scn_time allows to roughly find out which SCN was current for a specific time in the last five days.

Incrementing SCN
The SCN is incremented whenever a transaction commits. However, this is not the only source of increments. In a seemingly idle database, the SCN gets incremented also through AQ, SMON, job queues...

The role of the SCN for consistent reads
The SCN plays a vital role for providing consistent reads.
Basically, it works as follows: The query reads a db block. This block has as an attribute the SCN when it was last changed. If this SCN is greater than the SCN that was in place when (our) query began, it means that the block was changed after we have started our query. So we have to find an older version of the block. If this block is found in the rollback segments, we use it for our query.

Misc
The v$ views use change# to report SCN values. Hence, it can be argued that SCN means System Change Number, not System Commit Number.
When exporting, an SCN can be specified with the exp_flashback_scn export parameter.
With Oracle 10g, it is possible to go back to a specific SCN on a table with flashback table to scn.
See also the ora_rowscn pseudo column

rowdependencies vs norowdependencies
[ column-properties ]
[ table-partitioning-clauses ]
[ cache nocache ]
[ parallel-clause ]
[ rowdependencies norowdependencies ]
[ enable-disable-clause-1 [, enable-disable-clause-2 ....] ]
[ row-movement-clause ]
[ as subquery ]
The table properties appear as part within the create table [heap] statement.
rowdependencies vs norowdependencies
The default is norowdependencies which keeps the SCN of the last modification at block level. When a table is created with rowdependencies, the SCN of each row's last modification is stored along with the

How Lenders Interpret Your Credit Report

Refer: http://www.howstuffworks.com/credit-report3.htm

Inquiries - Every time you apply for a credit card to get a free travel mug, duffel bag, or T-shirt, you are adding another hard inquiry to your credit report. When potential lenders see these inquiries, it may wrongly imply that you're either in some financial situation where you need a lot of credit, or are planning to take on a large debt. Either can flag you as a high credit risk.
Other types of inquiries, such as your own requests to view the report, employer requests to view the report and requests by marketers to get your name in order to sell you something, count as soft inquiries. These inquiries don't show up on the reports that lenders see, and therefore don't affect how they view your credit.

Debt in relation to income - If you have unsecured credit card debt that is more than 20 percent of your annual income, lenders may not want to give you the best deal on a loan -- if they'll take the chance and give you a loan in the first place. Work to reduce the debt-to-income ratio and you'll be able to get better rates on the loans you seek.

Load Oracle Package in SGA (Memory) at Startup

Load Oracle Package in SGA at Startup

Normally whenever you call package for the first time, the entire package will be loaded in the memory. Memory is a shared area called share pool. Whenever there is a call to another stored procedure/function, oracle will try to find space in shared pool. If memory is available, object will be loaded into the memory. And if memory is not available then oracle will page out old procedures in memory using LRU (Least Recently Used) algorithm. If the large package which fit into the memory initially may not fit into contiguous memory when it is reloaded after paging out. It may give error ORA-4031.

Pin Oracle Package

To prevent paging, you can mark packages as non-swappable, telling the database that after their initial load they must always remain in memory. This is called pinning, or memory fencing. Oracle provides the procedure dbms_shared_pool.keep for pinning a package. You can unpin packages by using dbms_shared_pool.unkeep. In addition to pinning packages at database startup time, you can call the dbmspool.keep procedure at runtime to pin a package of stand-alone stored procedures.

Oracle Corporation recommends that you always pin the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE, and DBMS_OUTPUT packages in the shared pool. You can use the following code:

connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');

You can write a standard procedure to pin all of the recommended Oracle packages into the shared pool. Here is the script:

execute dbms_shared_pool.keep('DBMS_ALERT');
execute dbms_shared_pool.keep('DBMS_DDL');
execute dbms_shared_pool.keep('DBMS_UTILITY');
execute dbms_shared_pool.keep('STANDARD');

You can call this script at database startup. It will load all the required packages and procedures in the memory at startup.

Reference: http://www.dba-oracle.com/art_proc.htm

Getting Cumulative Sum Using Oracle Analytical Functions

http://www.oracle.com/technology/oramag/code/tips2005/062005.html
http://www.akadia.com/services/ora_analytic_functions.html

Getting Cumulative Sum Using Oracle Analytical Functions
This tip comes from Robert Ware, DBA at TekSystems, in St. Louis, Missouri.

The following code is an alternative way to get a cumulative sum by using Oracle Analytical Function. (This is an alternative approach to the "Getting Cumulative Sum" code tip of May 23, 2005.)
SQL> select deptno,
2 ename,
3 sal,
4 sum(sal) over (partition by deptno
5 order by sal,ename) CumDeptTot,
6 sum(sal) over (partition by deptno) SalByDept,
7 sum(sal) over (order by deptno, sal) CumTot,
8 sum(sal) over () TotSal
9 from emp
10 order by deptno, sal;
DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 8750 1300 29025
10 CLARK 2450 3750 8750 3750 29025
10 KING 5000 8750 8750 8750 29025
20 SMITH 800 800 10875 9550 29025
20 ADAMS 1100 1900 10875 10650 29025
20 JONES 2975 4875 10875 13625 29025
20 FORD 3000 7875 10875 19625 29025
20 SCOTT 3000 10875 10875 19625 29025
30 JAMES 950 950 9400 20575 29025
30 MARTIN 1250 2200 9400 23075 29025
30 WARD 1250 3450 9400 23075 29025
30 TURNER 1500 4950 9400 24575 29025
30 ALLEN 1600 6550 9400 26175 29025
30 BLAKE 2850 9400 9400 29025 29025
14 rows selected.
sum(sal) over (partition by deptno order by sal, ename) CumDeptTot: Computes a running total on salaries within a department, in order of salaries from lowest salary to the highest. Adding the ORDER BY clause changed the behavior of the analytic function. Instead of working on every row in the partition, it worked only on the current and prior rows in the partition after sorting by SAL.
sum(sal) over (partition by deptno) SalByDept: Computes a department total. The SAL column was summed by department now. The individual EMP records can compare their salary against the total salary made in that department. The PARTITION keyword breaks up our result set into virtual partitions on which the analytic functions will be applied.
sum(sal) over (order by deptno, sal) CumTot: Creates a running total of the SAL column after the data was sorted by DEPTNO and then SAL. Since out entire result set would be sorted this way, it becomes a running total for our entire result set.
sum(sal) over () TotSal: Computes a grand total. This is equivalent to select sum(sal) from emp, but that query would return only one row. Here we get the sum of SAL associated with each detail row. We can easily compare each salary in the EMP table to the grand total sum of SAL.

Which Shell I am using in Unix

Try following commands:

$echo $0
OR
$finger -m
OR
$echo $SHELL
OR
$ps -p $$

Query to find current version of Oracle

There are two ways to find out the current version of Oracle using sqlplus.

First is is when you start sqlplus, it will display the version. It will look something like this:

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Apr 16 10:06:34 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name:

Second way is to run following query :

SQL> select * from v$version ;

Connected to:Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit ProductionWith the Partitioning optionJServer Release 9.2.0.8.0 - Production

Vi Editor Settings

http://www.eng.hawaii.edu/Tutor/vi.html#settings

---

Meet people who discuss and share your passions. Join them now.