1) Display the name of employees along with their annual salary (sal*12) the name of the employee earning highest annual salary should appear first?
Select ename, sal, sal*12 "Annual Salary" from EMP order by "Annual Salary" desc;
2) Display name, salary, Hra, pf, da, TotalSalary for each employee. The out put should be in the order of total salary, hra 15% of salary, DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?
Select ename, sal SA, sal*0.15 HRA, sal*0.10 DA, sal*5/100 PF, sal+ (sal*0.15) + (sal*0.10) -(sal*.05) TOTALSALARY from emp ORDER BY TOTALSALARY DESC;
3) Display Department numbers and total number of employees working in each Department?
Select deptno, count (*) from emp group by deptno;
4) Display the various jobs and total number of employees working in each job group?
Select job, count (*) from emp group by job;
5) Display department numbers and Total Salary for each Department?
Select deptno, sum (sal) from emp group by deptno;
6) Display department numbers and Maximum Salary from each Department?
Select deptno, max (Sal) from emp group by deptno;
7) Display various jobs and Total Salary for each job?
Select job, sum (sal) from emp group by job;
8)Display each job along with min of salary being paid in each job group?
Select job, min (sal) from emp group by job;
9) Display the department Number with more than three employees in each department?
Select deptno, count (*) from emp group by deptno having count (*)>3;
10) Display various jobs along with total salary for each of the job where total salary is greater than 40000?
Select job, sum (sal) from emp group by job having sum (Sal)>40000;
11) Display the various jobs along with total number of employees in each job. The output should contain only those jobs with more than three employees?
Select job, count (*) from emp group by job having count (*)>3;
12) Display the name of employees who earn Highest Salary?
select ename, sal from emp where sal>=(select max(sal) from emp );
13) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?
select ename,empno from emp where sal=(select max(sal) from emp where job='CLERK') and job='CLERK' ;
14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?
select ename,sal from emp where sal>(select max(sal) from emp where job='CLERK') AND job='SALESMAN';
15) Display the names of clerks who earn a salary more than the lowest Salary of any Salesman?
select ename,sal from emp where sal>(select min(sal) from emp where job='SALESMAN') and job='CLERK';
16) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?
select ename,sal from emp where sal>all(select sal from emp where ename='JONES' OR ename='SCOTT');
17) Display the names of employees who earn Highest salary in their respective departments?
select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);
18) Display the names of employees who earn Highest salaries in their respective job Groups?
select ename,job from emp where sal in (select max(sal) from emp group by job);
19)Display employee names who are working in Accounting department?
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname = 'ACCOUNTING';
20) Display the employee names who are Working in Chicago?
select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO';
21) Display the job groups having Total Salary greater than the maximum salary for Managers?
select job ,sum(sal) from emp group by job having sum(sal) >(select max(sal) from emp where job='MANAGER');
22) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?
select ename,deptno from emp where sal>any(select min(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
23) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?
select ename,deptno from emp where sal>all(select max(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
24) Display the names of Employees in Upper Case?
select upper(ename) from emp;
25) Display the names of employees in Lower Case?
select Lower(ename) from emp;
26) Display the names of employees in Proper case?
select InitCap(ename)from emp;
27) Find the length of your name using Appropriate Function?
select lentgh('RAMA') from dual;
28) Display the length of all the employee names?
select length(ename) from emp;
29) Display the name of employee Concatinate with Employee Number?
select ename' 'empno from emp;
30) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
select substr('Oracle',3,2) from dual;
31) Find the first occurance of character a from the following string Computer Maintenance Corporation?
select lstr('Computer Maintenance Corporation','a' ) from dual;
32) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)
select translate('Alliens','A','B') from Dual;
33) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
select ename ,replace(job,'MANAGER','BOSS') from emp;
34) Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?
select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING',20, 'RESEARCH' ,30 , 'SALES','OPERATIONS')DName from emp;
35) Display your Age in Days?
select sysdate-to_date('30-jul-1977') from dual;
36) Display your Age in Months?
select months_between(sysdate,to_date('30-jul-1977')) from dual;
37) Display current date as 15th August Friday Nineteen Nienty Seven?
select To_char(sysdate,'ddth Month Day year') from dual;
39) Scott has joined the company on 13th August ninteen ninety?
select empno,ename,to_char(Hiredate,'Day ddth Month year') from emp;
40) Find the nearest Saturday after Current date?
select next_day(sysdate,'Saturday') from dual;
41) Display the current time?
select To_Char(sysdate,'HH:MI:SS') from dual;
42) Display the date three months before the Current date?
select Add_months(sysdate,-3) from dual
43) Display the common jobs from department number 10 and 20?
select job from emp where job in (select job from emp where deptno=20) and deptno=10;
44) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
select Distinct job from emp where deptno in(10,20);
45) Display the jobs which are unique to department 10?
select job from emp where deptno=10;
46) Display the details of those employees who do not have any person working under him?
select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null );
47) Display the details of those employees who are in sales department and grade is 3?
select e.ename,d.dname,grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3
48) Display thoes who are not managers?
select ename from emp where job!='MANAGER';
49) Display those employees whose name contains not less than 4 characters?
Select ename from emp where length (ename)>=4
50) Display those department whose name start with"S" while location name ends with "K"?
Select e.ename, d.loc from emp e, dept d where d.loc like ('%K') and enamelike ('S%')
51) Display those employees whose manager name is Jones?
Select e.ename Superior, e1.ename Subordinate from emp e, e1 where e.empno = e1.mgr and e.ename='JONES'
52) Display those employees whose salary is more than 3000 after giving 20% increment?
Select ename, sal, (sal+(sal*0.20)) from emp where (sal+(sal*0.20))>3000;
53) Display all employees with their department names?
Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno
54) Display ename who are working in sales department?
Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno and d.dname='SALES'
55) Display employee name, dept name, salary, and commission for those sal in between 2000 to 5000 while location is Chicago?
Select e.ename, d.dname, e.sal, e.comm from emp e, dept d where e.deptno=d.deptno and sal between 2000 and 5000
56) Display those employees whose salary is greater than his manager's salary?
Select e.ename, e.sal, e1.ename, e1.sal from emp e, e1 where e.mgr=e1.empno and e.sal>e1.sal
57) Display those employees who are working in the same dept where his manager is work?
Select e.ename, e.deptno, e1.ename, e1.deptno from emp e, e1 where e.mgr=e1.empno and e.deptno=e1.deptno
58) Display those employees who are not working under any Manager?
Select ename from emp where mgr is null;
59) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?
Select ename, grade, deptno, sal from emp, salgrade where (grade, Sal) in (select grade, Sal from salgrade, emp where sal between losal and hisal) and grade! =4 and deptno in (10,30) and hiredate<'31-Dec-82'
60) Update the salary of each employee by 10% increment that are not eligible for commission?
Update emp set sal= (sal+(sal*0.10)) where comm is null
61) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
Select e.ename, e.hiredate, d.loc from emp e, dept d where e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in('NEW YORK','CHICAGO')
62) Display employee name, job, deptname, and loc for all who are working as manager?
Select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno
and e.empno in (select mgr from emp where mgr is not null)
63) Display those employees whose manager name is Jones and also display their manager name?
Select e.ename sub, e1.ename from emp e, emp e1 where e.mgr=e1.empno and e1.ename='JONES'
64) Display name and salary of ford if his salary is equal to hisal of his grade?
Select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;
OR
Select grade, sal, hisal from emp, salgrade where ename='FORD' and sal between losal and hisal;
OR
Select ename, sal, hisal, grade from emp, salgrade where ename='FORD' and (grade, Sal) in (select grade, hisal from salgrade,emp where sal between losal and hisal);
65) Display employee name, job, deptname, his manager name, his grade and make an under department wise?
Select e.ename sub, e1.ename sup, e.job, d.dname, grade from emp e1, salgrade, dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno group by d.deptno, e.ename, e1.ename, e.job, d.dname, grade
OR
Select e.ename sub, e1.ename sup, e.job, d.dname, grade from emp e, e1, salgrade, dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno
66) List out all the employee names, job, salary, grade and deptname for every one in a company except 'CLERK'. Sort on salary display the highest salary?
Select e.ename, e.job, e.sal, d.dname, grade from emp e, salgrade, dept d where (e.deptno=d.deptno and e.sal between losal and hisal) order by e.sal desc
67) Display employee name, job and his manager. Display also employees who are with out managers?
Select e.ename, e1.ename, e.job, e.sal, d.dname from emp e, emp e1, dept d where e.mgr=e1.empno (+) and e.deptno=d.deptno
68) Display Top 5 employee of a Company?
69) Display the names of those employees who are getting the highest salary?
Select ename, sal from emp where sal in (select max (sal) from emp)
70) Display those employees whose salary is equal to average of maximum and minimum?
Select * from emp where sal=(select (max (sal)+min (sal))/2 from emp)
71) Select count of employees in each department where count >3?
Select count (*) from emp group by deptno having count (*)>3
72) Display dname where atleast three are working and display only deptname?
select d.dname from dept d, emp e where e.deptno=d.deptno group by d.dname having count(*)>3
73) Display name of those managers name whose salary is more than average salary of Company?
Select distinct e1.ename, e1.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal> (select avg (sal) from emp)
74) Display those managers name whose salary is more than average salary of his employees?
Select distinct e1.ename, e1.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal>any (select avg (sal) from emp group by deptno)
75) Display employee name, sal, comm and net pay for those employees whose net pay is greater than or equal to any other employee salary of the company?
Select ename, sal, NVL (comm, 0), sal+NVL (comm, 0) from emp where sal+NVL (comm, 0) >any (select e.sal from emp e)
76) Display those employees whose salary is less than his manager but more than salary of other managers?
Select e.ename sub, e.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.salany (select e2.sal from emp e2, e, dept d1 where e.mgr=e2.empno and d1.deptno=e.deptno)
77) Display all employees' names with total sal of company with each employee name?
78) Find the last 5(least) employees of company?
79) Find out the number of employees whose salary is greater than their managers salary?
Select e.ename, e.sal, e1.ename, e1.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal
80) Display the manager who are not working under president but they are working under any other manager?
Select e2.ename from emp e1, emp e2, emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job! ='PRESIDENT';
81) Delete those department where no employee working?
Delete from emp where empno is null;
82) Delete those records from emp table whose deptno not available in dept table?
Delete from emp e where e.deptno not in (select deptno from dept)
83) Display those enames whose salary is out of grade available in salgrade table?
Select empno, sal from emp where sal<(select min (LOSAL) from salgrade) OR sal>(select max (hisal) from salgrade)
84) Display employee name, sal, comm and whose net pay is greater than any other in the company?
Select ename, sal, comm, sal+comm from emp where sal+comm>any (select sal+comm from emp)
85) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?
Select empno, hiredate, sysdate, to_char (sysdate,'yyyy') - to_char (hiredate,'yyyy') from emp where to_char (sysdate,'yyyy') - to_char (hiredate,'yyyy')=30
86) Display those employees whose salary is odd value?
Select ename, sal from emp where mod (sal, 2)! =0
87) Display those employees whose salary contains atleast 3 digits?
Select ename, sal from emp where length (sal)=3
88) Display those employees who joined in the company in the month of Dec?
Select empno, ename from emp where trim (to_char (hiredate,'Mon'))=trim ('DEC')
89) Display those employees whose name contains A?
Select ename from emp where ename like ('%A%')
90) Display those employees whose deptno is available in salary?
Select ename, sal from emp where deptno in (select distinct sal from emp);
91) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
Select empno, hiredate, sal from emp where trim (substr (hiredate, 1,2))=trim (substr (sal, -2,2));
OR
Select hiredate, sal from emp where to_Char (hiredate,'dd')=trim (substr (sal, -2,2))
92) Display those employees whose 10% of salary is equal to the year joining?
Select ename, sal, 0.10*sal from emp where 0.10*sal=trim (to_char (hiredate,'yy'))
93) Display those employees who are working in sales or research?
Select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname in ('SALES','RESEARCH');
94) Display the grade of Jones?
Select ename, grade from emp, salgrade where (grade, Sal) =(select grade, Sal from salgrade, emp where sal between losal and hisal and ename='JONES')
95) Display those employees who joined the company before 15th of the month?
select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';
96) Display those employees who has joined before 15th of the month?
select ename ,hiredate from emp where hiredate<'15-Jul-02'
97) Delete those records where no of employees in particular department is less than 3?
delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3
98) Delete those employeewho joined the company 10 years back from today?
delete from emp where empno in (select empno from emp where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)
99) Display the deptname the number of characters of which is equal to no of employee in any other department?
100) Display the deptname where no employee is working?
select deptno from emp where empno is null;
101) Display those employees who are working as manager?
select e2.ename from emp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
102) Count th number of employees who are working as managers (Using set opetrator)?
select d.dname from dept d where length(d.dname) in (select count(*) from emp e where e.deptno!=d.deptno group by e.deptno)
103) Display the name of the dept those employees who joined the company on the same date?
select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno
104) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?
select ename,sal,grade ,substr(sal,grade,1) from emp,salgrade where grade!= substr (sal,1,1) and grade = substr(sal,grade,1) and sal between losal and hisal
105) Count the no of employees working as manager using set operation?
Select count(empno) from emp where empno in (select a.empno from emp a intersect select b.mgr from emp b)
106) Display the name of employees who joined the company on the same date?
select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;
107) Display the manager who is having maximum number of employees working under him?
select e2.ename,count(*) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename Having count(*)=(select max(count(*)) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename)
108) List out the employee name and salary increased by 15% and express as whole number of Dollars?
select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from emp
147) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?
Ans: select ename"EMPLOYEE_AND",job"JOB" FROM EMP;
148) List of employees with hiredate in the format of 'June 4 1988'?
Ans: select ename,to_char(hiredate,'Month dd yyyy') from emp;
149) print list of employees displaying 'Just salary' if more than 1500 if exactly 1500 display 'on taget' if less than 1500 display below 1500?
Ans: select ename,sal,
(
case when sal < 1500 then
'Below_Target'
when sal=1500 then
'On_Target'
when sal > 1500 then
'Above_Target'
else
'kkkkk'
end
)
from emp
150) Which query to calculate the length of time any employee has been with the company
Ans: select hiredate, to_char (hiredate,' HH:MI:SS') FROM emp
151) Given a string of the format 'nn/nn' . Verify that the first and last 2 characters are numbers. And that the middle character is '/' Print the expressions 'Yes' IF valid 'NO' of not valid. Use the following values to test your solution'12/54', 01/1a, '99/98'?
Ans:
152) Employes hire on OR Before 15th of any month are paid on the last friday of that month those hired after 15th are paid the last friday of th following month .print a list of employees .their hiredate and first pay date sort those who se salary contains first digit of their deptno?
Ans: select ename,hiredate, LAST_DAY ( next_day(hiredate,'Friday')),
(
case when to_char(hiredate,'dd') <=('15') then
LAST_DAY ( next_day(hiredate,'Friday'))
when to_char(hiredate,'dd')>('15') then
LAST_DAY( next_day(add_months(hiredate,1),'Friday'))
end
)
from emp
153) Display those managers who are getting less than his employees salary?
Ans: select a.empno, a.ename, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal
154) Print the details of employees who are subordinates to BLAKE?
Ans: select a.empno,a.ename ,b.ename from emp a, emp b where a.mgr=b.empno
and b.ename='BLAKE'
Monday, 22 March 2010
Useful Oracle Queries
Thursday, 18 March 2010
Copying Oracle Home Directories using rsync
In UNIX, it is possible to copy the Oracle Database Software (the Oracle Home directory) from one server to another using rsync.
Here is the example rsync syntax to copy the Oracle Home from one server (linux3) to another server (linux4). This process will ensure that all symbolic links and files owned by root get copied over correctly. Login to the source database server (linux3) as the root user account and run the following:
[root@linux3 ~]# rsync -auvzpogl -e ssh /u01/app/oracle/product/10.2.0/db_1/ linux4:/u01/app/oracle/product/10.2.0/db_1/
Tuesday, 9 March 2010
Physical Database Limits(11g)
Physical Database Limits(11g)
| Item | Type of Limit | Limit Value |
|---|---|---|
| Database Block Size | Minimum | 2048 bytes; must be a multiple of operating system physical block size |
| Database Block Size | Maximum | Operating system dependent; never more than 32 KB |
| Database Blocks | Minimum in initial extent of a segment | 2 blocks |
| Database Blocks | Maximum per datafile | Platform dependent; typically 222 - 1 blocks |
| Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
| Controlfiles | Size of a control file | Dependent on operating system and database creation options; maximum of 20,000 x (database block size) |
| Database files | Maximum per tablespace | Operating system dependent; usually 1022 |
| Database files | Maximum per database | 65533 May be less on some operating systems Limited also by size of database blocks and by the |
| Database extents | Maximum per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
| Database extents | Maximum per locally managed (uniform) tablespace | 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
| Database file size | Maximum | Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks |
MAXEXTENTS | Default value | Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
MAXEXTENTS | Maximum | Unlimited |
| Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit |
| Redo Log Files | Maximum number of logfiles per group | Unlimited |
| Redo Log File Size | Minimum size | 4 MB |
| Redo Log File Size | Maximum Size | Operating system limit; typically 2 GB |
| Tablespaces | Maximum number per database | 64 KNumber of tablespaces cannot exceed the number of database files because each tablespace must include at least one file |
| Bigfile Tablespaces | Number of blocks | A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks. |
| Smallfile (traditional) Tablespaces | Number of blocks | A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. |
| External Tables file | Maximum size | Dependent on the operating system. An external table can be composed of multiple files. |
Datatype Limits
Datatypes Limit Comments BFILEMaximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow. BLOBMaximum size: (4 GB – 1) *DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). CHARMaximum size: 2000 bytes None CHAR VARYINGMaximum size: 4000 bytes None CLOBMaximum size: (4 GB – 1) *DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). Literals (characters or numbers in SQL or PL/SQL – more) Maximum size: 4000 characters None LONGMaximum size: 2 GB – 1 Only one LONG column is allowed per table. NCHARMaximum size: 2000 bytes None NCHAR VARYINGMaximum size: 4000 bytes None NCLOBMaximum size: (4 GB – 1) *DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). NUMBER999…(38 9’s) x10125 maximum value -999…(38 9’s) x10125minimum value Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa) Precision 38 significant digits None RAWMaximum size: 2000 bytes None VARCHARMaximum size: 4000 bytes None VARCHAR2Maximum size: 4000 bytes None
Logical Database Limits
Item Type of Limit Limit Value GROUP BY clauseMaximum length The GROUP BY expression and all of the nondistinct aggregate functions (for example,SUM, AVG) must fit within a single database block. Indexes Maximum per table Unlimited Indexes Total size of indexed column 75% of the database block size minus some overhead Columns Per table 1000 columns maximum Columns Per index (or clustered index) 32 columns maximum Columns Per bitmapped index 30 columns maximum Constraints Maximum per column Unlimited Subqueries Maximum levels of subqueries in a SQL statement Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause Partitions Maximum length of linear partitioning key 4 KB – overheadPartitions Maximum number of columns in partition key 16 columns Partitions Maximum number of partitions allowed per table or index 1024K – 1 Rows Maximum number per table Unlimited Stored Packages Maximum size PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. Trigger Cascade Limit Maximum value Operating system-dependent, typically 32 Users and Roles Maximum 2,147,483,638Tables Maximum per clustered table 32 tables Tables Maximum per database Unlimited
Physical Database Limits
Item Type of Limit Limit Value Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block sizeDatabase Block Size Maximum Operating system dependent; never more than 32 KB Database Blocks Minimum in initial extent of a segment 2 blocks Database Blocks Maximum per datafile Platform dependent; typically 222 – 1 blocks Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommendedControlfiles Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size) Database files Maximum per tablespace Operating system dependent; usually 1022 Database files Maximum per database 65533 May be less on some operating systems Limited also by size of database blocks and by theDB_FILES initialization parameter for a particular instance Database extents (more) Maximum per dictionary managed tablespace 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)Database extents Maximum per locally managed (uniform) tablespace 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks MAXEXTENTSDefault value Derived from tablespace default storage orDB_BLOCK_SIZE initialization parameter MAXEXTENTSMaximum Unlimited Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in theCREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit Redo Log Files Maximum number of logfiles per group Unlimited Redo Log File Size Minimum size 4 MBRedo Log File Size Maximum Size Operating system limit; typically 2 GB Tablespaces Maximum number per database 64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one fileBigfile Tablespaces Number of blocks A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks. Smallfile (traditional) Tablespaces Number of blocks A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. External Tables file Maximum size Dependent on the operating system.An external table can be composed of multiple files.
Process and Runtime Limits
Item Type of Limit Limit Value Instances per database Maximum number of cluster database instances per database Operating system-dependent Locks Row-level Unlimited Locks Distributed Lock Manager Operating system dependent SGA size Maximum value Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems Advanced Queuing Processes Maximum per instance 10Job Queue Processes Maximum per instance 1000I/O Slave Processes Maximum per background process (DBWR, LGWR, etc.) 15 I/O Slave Processes Maximum per Backup session 15 Sessions Maximum per instance 32 KB; limited by the PROCESSES and SESSIONSinitialization parametersGlobal Cache Service Processes Maximum per instance 10Shared Servers Maximum per instance Unlimited within constraints set by thePROCESSES and SESSIONS initialization parameters, for instance Dispatchers Maximum per instance Unlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance Parallel Execution Slaves Maximum per instance Unlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance Backup Sessions Maximum per instance Unlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance
PL/SQL Compiler Limits
Item Limit bind variables passed to a program unit 32768 exception handlers in a program unit 65536 fields in a record 65536 levels of block nesting 255 levels of record nesting 32 levels of subquery nesting 254 levels of label nesting 98 levels of nested collections no predefined limit magnitude of a PLS_INTEGER or BINARY_INTEGERvalue -2147483648..2147483647 number of formal parameters in an explicit cursor, function, or procedure 65536 objects referenced by a program unit 65536 precision of a FLOAT value (binary digits) 126 precision of a NUMBER value (decimal digits) 38 precision of a REAL value (binary digits) 63 size of an identifier (characters) 30 size of a string literal (bytes) 32767 size of a CHAR value (bytes) 32767 size of a LONG value (bytes) 32760 size of a LONG RAW value (bytes) 32760 size of a RAW value (bytes) 32767 size of a VARCHAR2 value (bytes) 32767 size of an NCHAR value (bytes) 32767 size of an NVARCHAR2 value (bytes) 32767 size of a BFILE value (bytes) 4G * value of DB_BLOCK_SIZE parameter size of a BLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter size of a CLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter size of an NCLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
BFILEBFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.BLOBDB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)CHARCHAR VARYINGCLOBDB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)LONGLONG column is allowed per table.NCHARNCHAR VARYINGNCLOBDB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)NUMBERRAWVARCHARVARCHAR2GROUP BY clauseGROUP BY expression and all of the nondistinct aggregate functions (for example,SUM, AVG) must fit within a single database block.FROM clause of the top-level query 255 subqueries in the WHERE clause4 KB – overhead2000 to 3000 lines of code.322,147,483,6382048 bytes; must be a multiple of operating system physical block size32 KB1 minimum; 2 or more (on separate devices) strongly recommended20,000 x (database block size)1022DB_FILES initialization parameter for a particular instance4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)4 MB blocksMAXEXTENTSDB_BLOCK_SIZE initialization parameterMAXEXTENTSMAXLOGFILES parameter in theCREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit4 MB2 GB64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems10100032 KB; limited by the PROCESSES and SESSIONSinitialization parameters10PROCESSES and SESSIONS initialization parameters, for instancePROCESSESand SESSIONS initialization parameters, for instancePROCESSESand SESSIONS initialization parameters, for instancePROCESSESand SESSIONS initialization parameters, for instancePLS_INTEGER or BINARY_INTEGERvalueFLOAT value (binary digits)NUMBER value (decimal digits)REAL value (binary digits)CHAR value (bytes)LONG value (bytes)LONG RAW value (bytes)RAW value (bytes)VARCHAR2 value (bytes)NCHAR value (bytes)NVARCHAR2 value (bytes)BFILE value (bytes)BLOB value (bytes)CLOB value (bytes)NCLOB value (bytes)Physical Database Limits(10g)
Subscribe to:
Posts (Atom)