ORACLE

ORACLE INTERVIEW QUESTIONS

2008-04-06T12:00:00.000-07:00

What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

What are the components of logical database structure of Oracle database?

There are tablespaces and database's schema objects.

What is a tablespace?

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Explain the relationship among database, tablespace and data file ?

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.What is schema?A schema is collection of database objects of a user.

What are Schema Objects?

Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

Can objects of the same schema reside in different tablespaces?

Yes.

Can a tablespace hold objects from different schemes?

Yes.

What is Oracle table?

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is an Oracle view?

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

So, who is Scott?

2008-03-16T23:25:00.000-07:00

Bruce Scott was one of the first employees at Oracle (then Software Development Laboratories). He was co-author and co-architect of Oracle V1, V2 and V3. The SCOTT schema (EMP and DEPT tables), with password TIGER, was created by him. Tiger was the name of his cat.

After leaving Oracle, Bruce co-founded Gupta Technology (now known as Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc.

Image Of SCOTT

Who is the man behind Oracle Corporation?

2008-03-16T23:07:00.000-07:00

Lawrence Joseph (Larry) Ellison (Born: 1944, Chicago) is president and CEO of Oracle corporation.

He's the Oracle worlds hero, and he should be. Oracle Corporation, the company he founded with Robert N. (Bob) Miner and Edward A. (Ed) Oates back in 1977, has emerged as the world's largest vendor of software that helps large corporations and governments better manage their information.

Check out the book: What's the difference between God and Larry Ellison".
It's really a great book, and is pretty even handed with who Larry Ellison is with lots of interesting stories from the early days of Oracle. The name of the book came from a funny e-mail message that went around the company. The answer to the question is: "God doesn't think he's Larry..."

Oracle's Head Quarters

2008-03-16T22:49:00.000-07:00

Who is Oracle and where do they come from?

Oracle at a glance

Global Workforce: 43,000
Revenues 2004: $10.15 billion
Net income 2004: $2.6 billion

Oracle Corporation was founded in 1977 in Redwood, California. They introduced the first Relational Database Management System based on the IBM System/R model and the first database management system utilizing IBM's Structured Query Language (SQL) technology.

Today, the Oracle DBMS is supported on over 80 different operating environments, ranging from IBM mainframes, DEC VAX minicomputers, UNIX-based minicomputers, Windows NT and several proprietary hardware-operating system platforms, and is clearly the world's largest RDBMS vendor.

Oracle employs more than 43,000 professionals in 93 countries around the world. Their expenditure for research and development is approximately 13% of their revenue.

Oracle's Head HQ buildings in Redwood Shores

Interview Questions

2008-02-08T04:56:00.000-08:00

>>Previous>>

51.What is an execution plan? When would you use it? How would you view the execution plan?

52.What is the STUFF function and how does it differ from the REPLACE function?

53.What does it mean to have quoted_identifier on? What are the implications of having it off?

54.What are the different types of replication? How are they used?

55.What is the difference between a local and a global variable?

56.What is the difference between a Local temporary table and a Global temporary table? How is each one used?

57.What are cursors? Name four types of cursors and when each one would be applied?

58.What is the purpose of UPDATE STATISTICS?

59.How do you use DBCC statements to monitor various aspects of a SQL server installation?

60.How do you load large data to the SQL server database?

61.How do you check the performance of a query and how do you optimize it?

62.How do SQL server 2000 and XML linked? Can XML be used to access data?

63.What is SQL server agent?

63.What is referential integrity and how is it achieved?

64.What is indexing?

65.What is normalization and what are the different forms of normalizations?

66.Difference between server.transfer and server.execute method?

67.What id de-normalization and when do you do it?

68.What is better - 2nd Normal form or 3rd normal form? Why?

69.Can we rewrite subqueries into simple select statements or with joins? Example?

70.What is a function? Give some example?

71.What is a stored procedure?

72.Difference between Function and Procedure-in general?

73.Difference between Function and Stored Procedure?

74.Can a stored procedure call another stored procedure. If yes what level and can it be controlled?

75.Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?

76.How do you find the number of rows in a table?

77.Difference between Cluster and Non-cluster index?

78.What is a table called, if it does not have neither Cluster nor Non-cluster Index?

79.Explain DBMS, RDBMS?

80.Explain basic SQL queries with SELECT from where Order By, Group By-Having?

81.Explain the basic concepts of SQL server architecture?

82.Explain couple pf features of SQL server

83.Scalability, Availability, Integration with internet, etc.)?

84.Explain fundamentals of Data ware housing & OLAP?

85.Explain the new features of SQL server 2000?

86.How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?

87.What is data integrity? Explain constraints?

88.Explain some DBCC commands?

89.Explain sp_configure commands, set commands?

90.Explain what are db_options used for?

91.What is the basic functions for master, msdb, tempdb databases?

92.What is a job?

93.What are tasks?

94.What are primary keys and foreign keys?

95.How would you Update the rows which are divisible by 10, given a set of numbers in column?

96.If a stored procedure is taking a table data type, how it looks?

97.How m-m relationships are implemented?

98.How do you know which index a table is using?

99.How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?

100.How do you find the error, how can you know the number of rows effected by last SQL statement?

101.How can you get @@error and @@rowcount at the same time?

102.What are sub-queries? Give example? In which case sub-queries are not feasible?

103.What are the type of joins? When do we use Outer and Self joins?

104.Which virtual table does a trigger use?

105.How do you measure the performance of a stored procedure?

106.Questions regarding Raiseerror?

107.Questions on identity?

108.If there is failure during updation of certain rows, what will be the state?

>>>Next>>>

Interview Questions

2008-02-08T04:47:00.000-08:00

>>Previous>>

31.What are the authentication modes in SQL Server?

- Windows mode and mixed mode (SQL & Windows).

32.Where do you think the users names and passwords will be stored in sql server?
- They get stored in master db in the sysxlogins table.

33.What is log shipping?

Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.

34.Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?

- For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER –m which will basically bring it into the maintenance mode after which we can restore the master db.

35.Let us say master db itself has no backup. Now you have to rebuild the db so what kind of action do you take?

- (I am not sure- but I think we have a command to do it).

36.What is BCP? When do we use it?

- BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.

37.What should we do to copy the tables, schema and views from one SQL Server to another?

- We have to write some DTS packages for it.

38.What are the different types of joins and what dies each do?

39.What are the four main query statements?

40.What is a sub-query? When would you use one?

41.What is a NOLOCK?

42.What are three SQL keywords used to change or set someone’s permissions?

43.What is the difference between HAVING clause and the WHERE clause?

44.What is referential integrity? What are the advantages of it?

45.What is database normalization?

46.Which command using Query Analyzer will give you the version of SQL server and operating system?

47.Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?

48.What is the purpose of using COLLATE in a query?

49.What is a trigger?

50.What is one of the first things you would do to increase performance of a query?

>>>Next>>>

Interview Questions

2008-02-08T04:33:00.000-08:00

>>Previous>>

21.What is a Join in SQL Server?

- Join actually puts data from two or more tables into a single result set.

22.Can you explain the types of Joins that we can have with Sql Server?

- There are three types of joins: Inner Join, Outer Join, Cross Join.

23.When do you use SQL Profiler?

- SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..

24.What is a Linked Server?

- Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.

25.Can you link only other SQL Servers or any database servers such as Oracle?

- We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.

26.Which stored procedure will you be running to add a linked server?

- sp_addlinkedserver, sp_addlinkedsrvlogin

27.What are the OS services that the SQL Server installation adds?

- MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

28.Can you explain the role of each service?

- SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers.

29.How do you troubleshoot SQL Server if its running very slow?

- First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes.

30.Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot?

- First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.

>>>Next>>>

Interview Questions

2008-02-08T04:28:00.000-08:00

>>Previous>>

11.Which TCP/IP port does SQL Server run on? - SQL Server runs on port 1433 but we can also change it for better security.

12.From where can you change the default port? - From the Network Utility TCP/IP properties –> Port number.both on client and the server.

13.Can you tell me the difference between DELETE & TRUNCATE commands? - Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

14.Can we use Truncate command on a table which is referenced by FOREIGN KEY? - No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.

15.What is the use of DBCC commands? - DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

16.Can you give me some DBCC command options?(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

17.What command do we use to rename a db? - sp_renamedb ‘oldname’ , ‘newname’

18.Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? - In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.

19.What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? - Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

20.What do you mean by COLLATION? - Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.

>>>Next>>>

Interview Questions

2008-02-08T04:13:00.000-08:00

>>Previous>>

1.What is normalization? - Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.

2.What is a Stored Procedure? - Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

3.Can you give an example of Stored Procedure? - sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.

4.What is a trigger? - Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.

5.What is a view? - If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

6.What is an Index? - When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

7.What are the types of indexes available with SQL Server? - There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.

8.What is the basic difference between clustered and a non-clustered index? - The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

9.What are cursors? - Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.

10. When do we use the UPDATE_STATISTICS command? - This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

>>>Next>>>

SQL Server

2008-02-08T04:03:00.000-08:00

SELECT INTO

The SELECT INTO statement enables one to create new table based on query results.

One can create both temporary and permanent tables using SELECT INTO.

Syntax:

SELECT column_list
INTO new_table_name
FROM table_list
WHERE search_condition

Example:

SELECT * into mystores
FROM Stores
Go

OR

SELECT title_id, title
INTO #tmptables
FROM titles
Go

When Creating permanent tables using select into, one must set the SELECT INTO/BULK COPY database option true.

[sp_dboption 'pubs', 'select into/bulkcopy', true]

Name of the new table must be unique within the database and must conform to the rules for SQL Server naming conventions.

When creating local temporary table (Local temporary table are those that are available only during the current user session to SQL Server and are delocated when the session is terminated) use in front of new table name.

When creating global temporary table (Available to all user sessions to SQL Server and are deallocated when the last user session accessing the table is terminated) use ## in front of new table name.

>>>Next>>>

2008-02-08T03:45:00.001-08:00

>>Previous>>

SubQueries

A Select statement within another SELECT, INSERT, UPDATE or DELETE statement. Subqueries can produce the same results as Joins. But Subqueries are more recommandable than Joins and will take less IO cycles than joins.,

Remember:

1) One can have as many nested subqueries in one as one want, there is no limit on this.

2) The subquery is always enclosed in parentheses unless used in Update statement.

3) Subquery can not contain an Order By, Compute, or SELECT INTO Clause ie in Where list!!

4) Subquery can not be used in the Order By clause.

Example:

Sql> SELECT distinct au_fname,au_lname, state

FROM authors where state IN

(SELECT state from stores);

>>>Next>>>

SQL Server

2008-02-08T03:42:00.001-08:00

>>Previous>>

Cross Join or Unrestricted Join

Returns combination of all rows of all tables in join as the result set. Each row of one table is joined with each row of another table.

Example:

SQL Server syntax

SELECT p.pub_name, p1.pr_info
FROM publishers p, pub_info p1

ANSI syntax

SELECT p.pub_name,p1.pr_info
FROM publishers p CROSS JOIN pub_info p1

Outer Join

Restricts rows from one table while allowing all rows from another table as a result set. Usually used for orphan records.

Outer joins. Outer joins can be a left, right, or full outer join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Example:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Note:If Inner Join were used in above query then we would have got only the results of authors name whose city was same as publishers and not those authors who lived in city to which no publisher belonged to. In this case we got name of all the authors.

Self Join

Correlates rows of a table with other rows in the same table.

A table can be joined to itself in a Self Join. For example, you can use a Self Join to find out the authors in Oakland, California who live in the same zip code area.

Because this query involves a Join of the authors table with itself

USE pubs

SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER join authors au2
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
AND au1.state = 'CA'
AND au1.au_id < au2.au_id

>>>Next>>>

SQL Server

2008-02-08T03:24:00.001-08:00

>>Previous>>

Implementing Joins

Joins are used to retrieve data from two or more tables. The results appear as a single table with columns from the entire table specified in the SELECT column_list and meeting the search criteria.

In nutshell Joins connect two or more tables based on a join condition and produce results as new table with the rows that satisfy the join condition

We will probe Joins using both SQL Server syntax and ANSI syntax.

Inner Join: There can be two types of Inner Joins.

One is EquiJoin and other is Natural Join

Using Pubs consider tables:

Publishers (pub_id, pub_name, city, state, country) Primary Key (pub_id)

Pub_info (pub_id, logo, pr_info) Primary Key (pub_id)

Authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contact) Primary Key (au_id)

EquiJoin: In Equi Join Column values are compared for equality and redundant columns are displayed as columns in the result set

Example:

SQL Server syntax

SELECT *
FROM publishers, pub_info
WHERE publishers.pub_id=pub_info.pub_id

ANSI syntax

SELECT *
FROM Publishers
INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id

Natural Join: In Natural Joins redundant columns are not displayed twice

Example:

SQL Server syntax

SELECT p. *, p1.logo, p1.pr_info
FROM publishers p, pub_info p1
Where p.pub_id = p1.pub_id

ANSI syntax

SELECT p.*, p1.logo, p1.pr_info
FROM publishers p
INNER JOIN pub_info p1 ON p.pub_id = p1.pub_id

>>>Next>>>

SQL Server

2008-02-08T01:55:00.000-08:00

Compute By Clause

The compute and compute by clause are used to produce new rows of summary and detail data. They use the aggregate functions. The COMPUTE clause return detail rows and a grand total summary row. The compute by clause return the new rows of summary data, much like Group By clause, but it returns the rows as sub-groups with summary values

One has to use Order By clause before using Compute By clause

Example

SELECT type, SUM (ytd_sales)
FROM titles
ORDER BY type COMPUTE SUM(ytd_sales) BY type

SQL Server

2008-02-08T01:39:00.000-08:00

>>Previous>>

Aggregate Functions

Aggregate functions can return summary values for an entire table or for group of rows in a table. Aggregate functions are usually used in conjunction with the GROUP BY clause and are used in HAVING Clause or in the column_list.

· AVG: Returns average of the values in the numeric expression

Example

SELECT AVG(ytd_sales)
FROM Titles

· COUNT(*): Returns number of selected rows

Example

SELECT Count(*) from employee [ Returns total number of rows in a table]

· MAX: Returns highest value in the expression

Example

SELECT MAX(ytd_sales) FROM titles

· MIN: Returns lowest value in the expression

Example

SELECT MIN (ytd_sales)
FROM titles

· SUM: Returns total of values in the numeric expression

Example

SELECT SUM(qty)
FROM sales

· TOP n: Returns the top n values in the result set

SELECT TOP 5 * FROM authors

SELECT TOP 5 * FROM sales where qty>20

Group By and Having Clause

The group by clause summary data that meets the WHERE clause criteria to be returned as single row. The HAVING clause set the criteria to determine which rows will be returned by the GROUP BY clause

Example

SELECT title_id, count (title_id) as Number_of_Authors
From Titleauthor
GROUP BY title_id Having count (title_id)>1

OR

SELECT title_id,ytd_sales
FROM Titles
WHERE (ytd_sales>=4000)
GROUP BY title_id,ytd_sales

1. HAVING clause has same effect on the GROUP BY clause as the WHERE clause has on the SELECT statement.

2. GROUP BY clause must contain all nonaggregate columns from the SELECT column_list.

3. HAVING clause criteria columns must return only one value.

>>>Next>>>

SQL Server

2008-02-08T01:29:00.000-08:00

>>Previous>>

Combining Search Conditions

As the previous example demonstrates, one can combine search conditions. But one need to be cautious when combining search conditions if one isn't familiar with Boolean logic: Don't confuse the AND and OR conditions.

Suppose someone say, "Get me a list of all our authors from Utah and Texas." One knows what one want.

But if one write the query as---

SELECT au_fname, au_lname, phone, au_id
FROM authors
WHERE state LIKE 'ut' AND state LIKE 'tx'

The query will not return any data because it asked for all authors who live in both Utah and Texas. The result you want is data that meets both conditions, authors who live in either Utah or Texas.

Instead, use this query:

SELECT au_fname, au_lname, phone, au_id
FROM authors
WHERE state LIKE 'ut' OR state LIKE 'tx'

Note that one needs to repeat the search condition. One can't write WHERE state LIKE 'ut' OR 'tx' because SQL Server expects a complete search condition on either side of the OR.

>>>Next>>>

SQL Server

2008-02-08T00:47:00.000-08:00

>>Previous>>

Using Distinct to Eliminate duplicate Information

One can eliminate duplicates using Distinct keyword in the SELECT clause

SELECT DISTINCT column_list FROM table_Name
Where search_condition

Example

SELECT DISTINCT city
FROM authors

OR

SELECT DISTINCT city, state
FROM authors

Using ORDER BY clause

ORDER BY clause is used in SELECT statement to sort data

SELECT column_name
From Table_List
[order by column_name[ASC|DES]]

Example

SELECT title_id, au_id
FROM titleauthor
ORDER BY title_id, au_id

Default Order is Ascending
Since sort order being used by SQL Server can make difference in the ORDER BY clause, one should check default sort order of SQL Server using sp_helpsort
ORDER BY clause can not be used on text or image columns

SQL Order of Logical Operations (each operates from left to right)

NOT
AND
OR

>>>Next>>>

2008-02-08T00:38:00.000-08:00

>>Previous>>

LIKE

Like is used with character and date data

SELECT column_name
FROM table_name
WHERE column_name [NOT] Like 'string'

Wildcards

1. %- String of zero or more characters

2. _ -Single Character

3. []- Single character within specified range

4. [^]- Single character not within specified range

Wildcard characters when used with Like keyword are enclosed in a single quotation mark

Example

SELECT title_id, title
FROM titles
WHERE title LIKE '%computers%'

OR

SELECT lname
FROM employee
WHERE lname like 'a%'

OR

SELECT lname
FROM employee
WHERE lname like '%a%'

OR

SELECT lname
FROM employee
WHERE lname like '%a'

OR

SELECT title_id, title
FROM titles
WHERE title LIKE '[cdefi]%'

Unknown Values

Null is equivalent to value "unknown"

IS NULL and IS NOT NULL operators are used

SELECT column_list
FROM table_list
WHERE column_name IS [NOT] NULL

Example

SELECT title_id, title
FROM titles
WHERE title_id IS NOT NULL

OR

SELECT title_id, title
FROM titles
WHERE title_id IS NULL

>>>Next>>>

2008-02-08T00:22:00.000-08:00

>>Previous>>

Comparison Operators

One can use comparison operators like >,<, >=, =<, =, <>, !=, !>, !<, () Example SELECT emp_id,lname,fname FROM employee WHERE pub_id='0877'

Range

One can retrieve rows based on a range of value using the BETWEEN keyword

Example

Select lname, emp_if
FROM Employee
WHERE hire_date between '10/1/92' AND '12/31/92'

OR

Select lname, emp_iD
FROM Employee
WHERE lname NOT between 'a' AND 'e'

Smaller value must come first in BETWEEN clause

Also one must enclose range in quotes if its character data types or date data types

Lists

One can use IN Keyword to match rows in a list

SELECT Column_list
FROM table_name
WHERE column_name [NOT] IN (value_list)
Example

SELECT emp_id, lname, fname
FROM employee where pub_id IN ('0877','9990')

OR

SELECT emp_id, lname, fname, pub_id
FROM employee where pub_id NOT IN ('0877','9990')

One can use the NOT operator to exclude a certain set of data and retrieve the rest. The NOT operator excludes all records that meet the criteria one specifies

>>>Next>>>

SQL Server

2008-02-07T23:46:00.000-08:00

Date Functions

Select date_function(parameter)

1. Dateadd (datepart,number,date): Adds the number of dateparts to the date Example

Select Dateadd (mm, 6, '1/1/01')-[ 2001-07-01] Select Dateadd (mm, -6, '1/1/01')-[ 2001-07-01]

2. Datediff (datepart,date1,date2): Number of dateparts between two dates Example

Select datediff(dd, '10/3/01', '10/9/01')

3. Getdate() :Returns current date and time in internal format Example

Select Getdate()

Datepart	Abbreviation
Day	DD,D
Day of year	DY
Hour	HH
Millisecond	MS
Minute	MI
Month	MM,M
Quarter	QQ
Second	SS, S
Week	WK
Weekday	DW
Year	YY, YYYY

SQL Server

2008-02-07T23:22:00.000-08:00

Using Literals

One can use Literals to make output more readable. A literal is a string surrounded by single quotation marks included in the column_list and displayed as another column in the query result

Examples:

SELECT 'Employee Name:',fname +','+lname
FROM employee

OR

SELECT fname, lname, 'Employee ID is' , emp_id
FROM employee

Important Mathematical Functions

Mathematical functions enable one to return mathematical data using the syntax:

Arithmetic operators can be used with data types: int, smallint, tinyint, numeric, decimal, float, real, money and smallmoney
With arithmetic operators two types of precedence exists:
- Datatype Precedence- Arithmetic precedence exists when arithmetic operations are performed on different data types. In case of usage of different data types, the smaller data type is converted to higher data type. Like if we multiply smallint by an int, the result will be an int, but in case of money result will always be of data type money
- Operator precedence is used when multiple operators are used. It uses same math concept of BODMAS

Select function_name(parameters)

1. Abs (numeric_expr): Provides absolute value

Example. Select Abs (-123)-[123]

2. Ceiling (numeric_expr) : Provides smallest integer greater than or equal to specified value

Example. Select ceiling(123.3), ceiling(-123.3)-[124,-123]

3. Floor (numeric_expr) : Provides largest integer less than or equal to specified value

Example. Select floor (123.3), floor(-123.3.)-[123, -124]

4. Round (numeric_expr, length) : Provides numeric expression rounded to the specified length in an integer value

Example. Select Round (1234.56,1), Round ($123.67,1)-[1234.6,123.7]

5. Square (float_expr) : Provides square value

Example. Select square (9)-(81)

6. SQRT (float_expr) :

Example. Select sqrt (9)-[3]

7. POWER (numeric_expression, y) : Provides value of numeric expression to the power of y

Example. Select Power(2,3)-[8]

Remember when using mathematical functions with monetary data type always use it with a dollar sign otherwise the value will be treated as numeric value

Important String Functions

String functions are used for manipulation of character data type

· + (expression, expression) : Concatenates two or more strings

Example

Select lname +','+ fname +'.' As Name from employee

OR

SELECT ('abc' + 'def') -[abcdef]

· ASCII (char_expr) :Provides ASCII code for left-most character

Example

Select ASCII ('A')-[65]

· Char (integer_expr) : Provides character equivalent of ASCII code value

Example: Select Char(97)-[a]

Control Character	Value
Tab	Char (9)
Linefeed	Char (10)
Carriage return	Char(13)

Example

Use Northwind
GO
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address, + CHAR(13)+ City, Region +char(10)
FROM Employees

· PATINDEX('% pattern%', expression): Returns starting position of first occurance in expression

Example

select patindex('%bv%','ronvcbvvbc')

· Lower (char_expr): Converts to lower case

Example

Select Lower ('ABCD')-[abcd]

· Upper (char_expr): Converts to upper case

Example

Select Upper ('abcd')-[ABCD]

· Left (char_expr, integer_expr) : Provides character string starting from the left and preceeding integer_expr character

Example

Select left('Seattle' ,2) - [se]

OR

USE pubs
GO
SELECT LEFT (title, 5) as Titles
FROM titles

· Right (char_expr, integer_expr) : Provides character string starting from the integer_expr character from Right

Example

Select Right ('Seattle' ,2) --[le]

· LTRIM (char_expr) : Returns data without leading blanks Example

Select Ltrim (' Computers')-[Computers]

· RTRIM (char_expr) : Returns data without trailing blanks

Example

Select Ltrim ('Computers ')-[Computers]

· REPLACE ('string_expression1', 'string_expression2', 'string_expression3') :Replaces all occurrences of srting2 in string1 with string3

Example. Select Replace ('abcde', 'bc','oo') -[aoode]

· Reverse (char_expr) : Returns reverse of character expression

Example

Select Reverse('ABCD')-[DCBA]

· SUBSTRING(expression, start, length): Returns part of character string

Example

SELECT SUBSTRING('abcdef', 2, 3) as X

X
----
bcd

SQL Server

2008-02-07T22:40:00.000-08:00

T-SQL

Now we will see how to write simple SQL queries. For understanding T-SQL and to use examples below use Query Analyzer in SQL Server

The Select Statement

One can use a SQL query to retrieve the data one wants from the database.

A query has three parts: SELECT, FROM, and WHERE

SELECT tells SQL Server which columns one want to see as the result of the query
FROM specifies which table or tables you want to use in the query.
WHERE limits which rows one want to see
The syntax isn't case sensitive, but generally people use uppercase and lowercase to distinguish the commands from the object names

A typical query looks like this:

SELECT col1, col2, col3......
FROM table1
WHERE col1 = search condition

Consider this simple query:

USE pubs
GO
SELECT * FROM authors
GO

The phrase "USE pubs" ensures that statement queries the pubs database. One could instead select the database in the DB window on the Toolbar
GO ensures that SQL Server has completed the previous command before the next one starts
This query will return all columns and rows in the authors table. Inside SQL Server * is turned into name of columns of the table

But one of the three elements-the WHERE clause-is missing from this statement. So if one uses this statement, SQL Server will return all the rows. A query without a WHERE clause can return a large amount of data, increasing network traffic

Warning: An update or delete statement without a WHERE clause to restrict the affected rows could be disastrous

Because the authors table in the pubs sample database is small, one can run this query without worry. Click on the green arrow or press CTRL-E. Image 1 shows the results. One needs to scroll the window to see all the columns and all the rows

Selecting Columns

If one wants to see only certain columns, one can restrict the output by listing the names of the columns one want. One can run a system-stored procedure to determine the column names. From the Query window, type and run:

sp_help authors

One can type this query in the same window as the previous query, and then highlight it to run the line on its own. Or one can use the cursor or mouse to highlight the word authors, and then press ALT-X. This action runs the sp_help stored procedure on the highlighted table name. Both methods will yield the same output-a list of columns and their data types and length

Another way one can identify the column names is to add a line to the query:

SELECT * FROM authors
WHERE 0 = 1

Because the WHERE condition is never met for this query (zero can't equal one), the query won't produce output, as shown in Image 2. But the query will return a page header that includes column names, with the order and spacing in which they'll appear on the final query. Now one can choose which columns you want to see by listing them in the

SELECT clause

SELECT au_id, au_lname, au_fname, phone
FROM authors

Note that commas separate the column names. For clarity, FROM is now on the line below SELECT. If one runs this query, one'll see that the output consists of only the four requested columns

Changing the Output Column Order

When one execute a query with select * from, the column order will be same as the column order specified in the CREATE TABLE statement but when one selects the columns from the column_list order does not need to be same as the table column order. One can rearrange the column order in the query output by rearranging the columns in the column list

Changing Column Header

The top row of the output lists the names of the columns one selected. Programmers devised the column names, so the names might not mean much to the person who sees the output report. One can use T-SQL or ANSI standard syntax to rename the output columns. If one uses the T-SQL syntax, supply the label first, followed by an equal sign, then the column name:

The three ways in which we can change column headings are:

Select column_heading=column_name FROM Table_name

Example
SELECT EmployeeId=emp_id, lastname=lname FROM employee
SELECT column_name as column heading From table_name

Example
SELECT emp_id as Employeeid , lname as LastName, fname as Firstname FROM employee
SELECT column_name column heading From table_name

Example
SELECT emp_id 'Employee Id' , lname LastName, fname Firstname FROM employee

One can use square brackets ([]) or single quotation marks to encapsulate column headings.,

How to Use WHERE , GROUP BY , HAVING AND ORDER BY clauses together?

2008-01-22T23:01:00.000-08:00

>>>Privious>>>

GROUP BY CLAUSE :

Using a group function one can group entire table data and provide a single result,however
you can use the GROUP BY clause to break the result set into logical groupings and get a result for each group.

Q. To display no of employees in each dept we can use.

SQL >SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;

DEPTNO COUNT(*)
---------- ----------
10 3
20 5
30 6

Q. The total salary paid for each department and also when the last recruitment was made in that department.

SQL >SELECT DEPTNO,MAX(HIREDATE),SUM(SAL) FROM EMP GROUP BY DEPTNO;

DEPTNO MAX(HIRED SUM(SAL)
---------- --------- ----------
10 23-JAN-82 8750
20 23-MAY-87 10875
30 03-DEC-81 9400

When grouping using the GROUP BY clause you cannot use a WHERE clause to restrict the groups that are returned, SQL provides another selection keyword, HAVING, to replace the WHERE clause.

Q,To diplay those departments which have more than 5 employees in them we can write.

SQL >SELECT DEPTNO, COUNT(*) FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 5 ;

DEPTNO COUNT(*)
---------- ----------
30 6

Using WHERE , GROUP BY , HAVING AND ORDER BY clauses together.

General form of the SELECT statement is

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

when all the above said clauses are used in a select query they need to appear in the order specified above, violation of which will lead to Errors.

Q. A query to display departments paid 500 or more and have 2 or less employess can be displayed as shown below

SQL >SELECT DEPTNO ,COUNT(*) , SUM(SAL) FROM EMP
2 WHERE JOB = 'CLERK'
3 GROUP BY DEPTNO
4 HAVING SUM(SAL) >=500 AND COUNT(*) <>

DEPTNO COUNT(*) SUM(SAL)
---------- ---------- ----------
30 1 950
10 1 1300

>>>Next>>>

What are AGGREGATE FUNCTIONS?

2008-01-22T22:58:00.001-08:00

>>>Previous>>>

FUNCTIONS : These functions operate over a set of values.They are also known as aggregate functions

MAX(): A function used find maximum value among the given values.

SQL > SELECT MAX(SAL) FROM EMP;

MAX(SAL)
---------
5000

MIN(): It returns minimun of the values in a column.

SQL > SELECT MIN(HIREDATE) FROM EMP;

MIN(HIRED
---------
17-DEC-80

AVG(): Gives the average of values in a column.

SQL >SELECT AVG(SAL) FROM EMP;

AVG(SAL)
----------
2073.21429

SUM(): Sum of the values in a column

SQL >SELECT SUM(SAL) FROM EMP;

SUM(SAL)
----------
29025

COUNT(*): Gives number of columns in a table.

SQL >SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
1

SQL >SELECT COUNT(*) FROM EMP;

COUNT(*)
----------
14

COUNT(): Gives no of not null ENTRIE (VALUES) in that column.

SQL >SELECT COUNT(COMM) FROM EMP;

COUNT(COMM)
-----------
4

SQL >SELECT COUNT(DISTINCT JOB) FROM EMP;

COUNT(DISTINCTJOB)
------------------
5

Explanation: Using DISTICT, duplicate values can be suppressed.

>>>Next>>>

What are CONVERSION FUNCTIONS?

2008-01-22T22:55:00.000-08:00

>>>Previous>>>

CONVERSION FUNCTIONS:

TO_CHAR: This function is used to convert a date or number to character string.
SQL> SELECT TO_CHAR(SYSDATE,'DAY DD MONTH YYYY') FROM DUAL

TO_CHAR (SYSDATE,'DAYDDMONTH
---------------------------
SATURDAY 01 JANUARY 2005

SQL> SELECT TO_CHAR (SYSDATE,' DD DY MM YY') FROM DUAL;

TO_CHAR (SYSDA
-------------
01 SAT 01 05

NVL() Function: This function is used to substitute any null value with a user-defined value.

Consider the following data from EMP table of SCOTT.

SQL> SELECT EMPNO, ENAME, SAL, COMM FROM EMP;

EMPNO ENAME SAL COMM
---------- ---------- ---------- -------------------------
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500 0
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300

In the above table except for 7499, 7521, 7654 and 7844 all others commissions are null
To display their commission as “0” (zero)
We can use NVL() function as shown below.

SQL> SELECT EMPNO, ENAME, SAL, NVL (COMM, 100) FROM EMP

EMPNO ENAME SAL NVL(COMM,100)
---------- ---------- ---------- -------------
7369 SMITH 800 100
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975 100
7654 MARTIN 1250 1400
7698 BLAKE 2850 100
7782 CLARK 2450 100
7788 SCOTT 3000 100
7839 KING 5000 100
7844 TURNER 1500 0
7876 ADAMS 1100 100
7900 JAMES 950 100
7902 FORD 3000 100
7934 MILLER 1300 100

SQL> SELECT EMPNO, ENAME, SAL, NVL (COMM, 888) FROM EMP

EMPNO ENAME SAL NVL(COMM,888)
---------- ---------- ---------- -------------
7369 SMITH 800 888
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975 888
7654 MARTIN 1250 1400
7698 BLAKE 2850 888
7782 CLARK 2450 888
7788 SCOTT 3000 888
7839 KING 5000 888
7844 TURNER 1500 0
7876 ADAMS 1100 888
7900 JAMES 950 888
7902 FORD 3000 888
7934 MILLER 1300 888

In above queries we have seen how to substitute a value when the comm is null.
If one want to display "Commission not payed" against the employees who have no
commission we can write the following query.

SQL > SELECT ENAME, SAL, NVL(TO_CHAR(COMM),'Commission Not Payed') FROM EMP

ENAME SAL NVL(TO_CHAR(COMM),'COMMISSIONNOTPAYED')
---------- ---------- ----------------------------------------
SMITH 800 Commission Not Payed
ALLEN 1600 300
WARD 1250 500
JONES 2975 Commission Not Payed
MARTIN 1250 1400
BLAKE 2850 Commission Not Payed
CLARK 2450 Commission Not Payed
SCOTT 3000 Commission Not Payed
KING 5000 Commission Not Payed
TURNER 1500 0
ADAMS 1100 Commission Not Payed
JAMES 950 Commission Not Payed
FORD 3000 Commission Not Payed
MILLER 1300 Commission Not Payed

>>>Next>>>

What are USER, SYSDATE Functions?

2008-01-22T22:54:00.000-08:00

The USER, SYSDATE Functions:

USER function displays login name of the user.

SQL> SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

SYSDATE function displays system date.(Date in your windows)

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
1-JAN-05

Practice DATE FUNCTIONS

2008-01-22T22:51:00.000-08:00

>>>Previous>>>

DATE FUNCTIONS:

ADD_MONTHS(date, n) : Adds n months to the specified date .

SQL> SELECT ADD_MONTHS('1-JAN-05',5) FROM DUAL;

ADD_MONTHS
------------------
01-JUN-05

LAST_DAY(date): Gives last date of the specified month (date).

SQL> SELECT LAST_DAY('1-JAN-05') FROM DUAL;

LAST_DAY(
---------
31-JAN-05

MONTHS_BETWEEN(date1, date2): It gives difference between the two dates date1, date2 in months.

SQL> SELECT MONTHS_BETWEEN('31-DEC-05','1-JAN-05') FROM DUAL

MONTHS_BETWEEN('31-DEC-05','1-JAN-05')
--------------------------------------
11.9677419

SQL> SELECT MONTHS_BETWEEN('31-JUL-05','1-JUL-05') FROM DUAL

MONTHS_BETWEEN('31-JUL-05','1-JUL-05')
--------------------------------------
.967741935

NEXT_DAY(date, ‘day’ ) : It gives date of the next occurrence of the specified day after the given date.

SQL> SELECT NEXT_DAY('01-JAN-05','FRI') FROM DUAL;

NEXT_DAY( (Next Friday after 1-jan-05 is on 7-jan-05)
---------
07-JAN-05

TO_DATE (string): This function converts a string into an Oracle date.

SQL> SELECT TO_DATE('01 JANUARY 2005','DD MONTH YYYY') FROM DUAL;

TO_DATE('
---------
01-JAN-05

SQL> SELECT TO_DATE('MAR 05 01','MON YY DD') FROM DUAL;

TO_DATE('
---------
01-MAR-05

SQL> SELECT TO_DATE('01/01/05', 'DD/MM/YY') FROM DUAL;

TO_DATE('
---------
01-JAN-05

>>>Next>>>

Practice OTHER FUNCTIONS

2008-01-22T22:48:00.000-08:00

>>>Previous>>>

OTHER FUNCTIONS:

Note: Arguments to the below given functions are in terms of radians

COS(x): It returns the Cosine of x.

SQL> SELECT COS(0) FROM DUAL;
COS(0)
----------
1

COSH(x) : It returns hyperbolic cosine of x.

SQL> SELECT COSH(0) FROM DUAL;

COSH(0)
----------
1

SIN(x) : It returns sine of x.

SQL> SELECT SIN(0) FROM DUAL;

SIN(0)
----------
0

SINH(x): It returns hyperbolic sine of x.

SQL> SELECT SINH(0) FROM DUAL;

SINH(0)
----------
0

TAN(x): It returns tangent of x.

SQL> SELECT TAN(0) FROM DUAL;

TAN(0)
----------
0

TANH(x): It returns hyperbolic tangent of x.
SQL> SELECT TANH(0) FROM DUAL;

TANH(0)
----------
0

>>>Next>>>

Practicce CHARACTER FUNCTIONS

2008-01-22T22:44:00.000-08:00

>>>Previous>>>

CHARACTER FUNCTIONS: These functions operate on character data.

UPPER( str) : It converts all letters in the given string “str” into Upper case.

SQL> SELECT UPPER('abcDEfg') FROM DUAL;
UPPER('
-------
ABCDEFG

LOWER(str): It converts all the letters in the given string “str” into Lower Case.

SQL> SELECT LOWER('ABCDEfg') FROM DUAL;
LOWER('
-------
abcdefg

INITCAP(str): It converts first letter of every word in the given string “str” into Upper Case and remaining letters into lower case. It is like proper function in FoxPro.

SQL> SELECT INITCAP('ABCDEF') FROM DUAL;

INITCA
------
Abcdef

LENGTH(str) : This function returns the number of characters in the given string (including spaces)

SQL> SELECT LENGTH('ABCD') FROM DUAL;

LENGTH('ABCD')
--------------
4

SQL> SELECT LENGTH('AB CD') FROM DUAL;

LENGTH('ABCD')
--------------
5

SUBSTR(str, m, n) :

Will extract “n” characters from the given string starting from
“m th” position.

SQL> SELECT SUBSTR('ABCDEFG',2,3) FROM DUAL
SUB
---
BCD

SQL> SELECT SUBSTR('ABCDEF',1,3) FROM DUAL
SUB
---
ABC
INSTR(string, str): It displays the location of “str” in the given string “string” .

SQL> SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS') FROM DUAL;

INSTR('
-----------------------------------------------------
27

INSTR(string, str, m, n):

It displays nth occurrence of “str” in the string “string” starting from “m”.

SQL> SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS',1,2) FROM DUAL

INSTR(
------------------------
37

Note : DUAL IS A TABLE WITH 1 COLUMN AND 1 ROW OF DATA IN IT.

SQL > DESC DUAL

Name Null? Type
----------------------------------------- -------- -
DUMMY VARCHAR2(1)

SQL > SELECT * FROM DUAL;

DUMMY
-
X

LPAD() :

This function is used to left pad the the given string with specified character or string.

SQL > SELECT LPAD('BCD',4,'A') FROM DUAL;

LPAD
----
ABCD

Explanation: To the given string "BCD" add "A" to the left necessary number of times to make it a string of 4 characters.

SQL > SELECT LPAD('BCD',5,'A') FROM DUAL;

LPAD(
-----
AABCD

Explanation: To the given string "BCD" add "A" to the left necessary number of times to make it a string of 5 characters.

SQL > SELECT LPAD('BCD',3,'A') FROM DUAL;

LPA
---
BCD

Explanation: To the given string "BCD" add "A" to the left necessary number of times to make it a string of 3 characters.

SQL > SELECT LPAD(' ',ROWNUM,'*') FROM EMP;

LPAD('',ROWNUM,'*')
-----------------------------------------------------------------

*
**
***
****
*****
******
*******
********
*********
**********
***********
************
*************

RPAD(): This function is used to left pad the the given string with specified character or string.

SQL > SELECT RPAD('BILL ' , 12 , 'CLINTON') FROM DUAL;

RPAD('BILL',
------------
BILL CLINTON

LTRIM(): This function removes specified string from the given string if it is there to the left of given string.

SQL > SELECT LTRIM('GEORGE BUSH', 'GEORGE') FROM DUAL;

LTRIM
-----
BUSH

RTRIM(): This function removes specified string from the given string if it is there to the right of given string.

SQL > SELECT RTRIM('TONY BLAIR', 'AIR') FROM DUAL;

RTRIM('
-------
TONY BL

ASCII(): Displays equivalent ASCII value of a character.

SQL > SELECT ASCII('A') FROM DUAL;

ASCII('A')
----------
65

SQL >SELECT TRANSLATE('JOHN','H','N') FROM DUAL;

TRAN
----
JONN

>>>Next>>>

Practice Numeric Functions

2008-01-22T22:37:00.000-08:00

>>>Previous>>>

Numeric Functions:

These functions operate on Numeric data hence is the name.

Note: Argument “num” in the following functions is any float-valued number.

ABS(num): Returns absolute value of the given number.(i.e. Always positive value)

SQL> SELECT ABS(10) FROM DUAL;

ABS(10)
----------
10

SQL> SELECT ABS(-10) FROM DUAL;

ABS(-10)
----------
10

CEIL(num):

It returns the smallest integer greater than the given number.

SQL> SELECT CEIL(123.456) FROM DUAL;

CEIL(123.456)
-------------
124

FLOOR(num):

It returns the largest integer smaller than the given value.

SQL> SELECT FLOOR(123.456) FROM DUAL;

FLOOR(123.456)
--------------
123

LN(num) :

It returns natural logarithm value of “num” .

SQL> SELECT LN(10) FROM DUAL;

LN(10)
----------
2.30258509

LOG(m, n):

It returns logarithm of “n” with base “m”.

SQL> SELECT LOG(100,10) FROM DUAL;

LOG(100,10)
-----------
.5

SQL> SELECT LOG(10,100) FROM DUAL;

LOG(10,100)
-----------
2

MOD(m, n) :

It returns remainder of “m” divided by “n”.

SQL> SELECT MOD(10,3) FROM DUAL;

MOD(10,3)
----------
1

POWER(m, n):

It returns value equal to “m” raised by “n”.

SQL> SELECT POWER(10,2) FROM DUAL;

POWER(10,2)
-----------
100

ROUND(m, n):

It rounds the given float-valued number “m” to the “n” places after the decimal

SQL> SELECT ROUND(1.23456) FROM DUAL;

ROUND(1.23456)
--------------
1

SQL> SELECT ROUND(1.23456,3) FROM DUAL;

ROUND(1.23456,3)
----------------
1.235

SQRT(m): It calculates square root value of number “m”

SQL> SELECT SQRT(9) FROM DUAL;

SQRT(9)
----------
3

TRUNC(m, n):

It truncates given float-valued number “m” to “n” places after the decimal.

SQL> SELECT TRUNC(1.23456) FROM DUAL;

TRUNC(1.23456)
--------------
1

SQL> SELECT TRUNC(1.23456,3) FROM DUAL;

TRUNC(1.23456,3)
----------------
1.234

GREATEST(expr1, expr2, …) :

It finds the greatest value among the given expressions.

SQL> SELECT GREATEST(4,7,3,5,9,2) FROM DUAL;

GREATEST(4,7,3,5,9,2)
---------------------
9

LEAST(expr1, expr2, …):

It finds the Lowest value among the given expressions.

SQL> SELECT LEAST(4,7,3,5,9,2) FROM DUAL;

LEAST(4,7,3,5,9,2)
------------------
2

>>>Next>>>

2008-01-22T22:30:00.000-08:00

>>>Previous>>>

Restricting and Sorting Data" covers the following topics.

WHERE Clause - Comparison Operators; Logical Operator; Using the Logical Operator, AND; Using the logical Operator, OR; Using the Logical Operator, NOT; Nesting Logical Operators; Other Comparison Keywords; Using the Keyword, LIKE; Using the Keywords, BETWEEN...AND; Using the Keyword, IN; Using the Keyword, IS NULL ORDER BY Clause - Using the ORDER BY Clause.

Expressions in a SELECT statement:

Arithmetic operations can be performed using SELECT statement as shown below.

SQL> SELECT 10+5 FROM DUAL;
10+5
----------
15

SQL> SELECT 10-5 FROM DUAL;
10-5
----------
5

SQL> SELECT 10*5 FROM DUAL;

10*5
----------
50

SQL> SELECT 10/5 FROM DUAL;

10/5
----------
2

>>>Next>>>

What are Relational Databases?

2008-01-22T22:27:00.000-08:00

>>Previous>>

Relational Databases

The relational data model comprises:

relational data structure
relational integrity constraints
relational algebra or equivalent (SQL)

• SQL is an ISO language based on relational algebra

• relational algebra is a mathematical formulation

>>>Next>>>

What are DATABASE Models?

2008-01-22T22:25:00.000-08:00

>>previous>>

Database Models

A data model comprises

a data structure
a set of integrity constraints
operations associated with the data structure

Examples of data models include:

hierarchic
network
relational

Models other than the relational database module used to be quite popular. Each model type is appropriate to particular types of problem. The Relational model type is the most popular in use today, and the other types are not discussed further.

>>>Next>>>

What is SQL?

2008-01-22T22:21:00.000-08:00

>>Previous>>

SQL is an international standard language for manipulating relational databases. It is based on an IBM product. SQL is short for Structured Query Language.

SQL can create schemas, delete them, and change them. It can also put data into schemas and remove data. It is a data handling language, but it is not a programming language.

SQL is a DSL (Data Sub Language), which is really a combination of two languages.

These are the Data Definition Language (DDL) and the Data Manipulation Language (DML).

Schema changes are part of the DDL, while data changes are part of the DML.

We will consider both parts of the DSL in this discussion of SQL.

>>>Next>>>

Reducing Database Fragmentation

2008-01-06T12:21:00.000-08:00

Oracle9i database admin guide says, a fragmented database should be reorganized to make space. The solution is to make a full db export, delete the db, recreate the db, and a full db import. Is there another easier option?

os: win2000 server
oracle 9.2.0.1.0

----->

Yes, there is an easier option. Use locally managed tablespaces. Do you have a fragmentation problem? Are you currently using locally managed or dictionary managed tablespaces?

----->

Yes, we are using locally managed tablespaces, does it mean I don't have to worry about fragmentation?

----->

It means you don't have to worry about it as much. Are you using uniform extent sizes or system managed extent sizes? If you are using uniform extent sizes, then you won't have any fragmentation issues. All extent sizes are the same. If you are using system managed extent sizes then fragmentation is greatly reduced, to the point that you shouln't have to be concerned with it. Have you identified that you have a fragmentation issue?

----->

No we havent, that's another problem, I don't know how to identify if we have... I will look it up.,

Best way to compare two Databases

2008-01-06T12:13:00.000-08:00

What is the best way to compare two databases. Especially tables+columns, indexes+columns, constraints, views, stored procedures, triggers.
It is not about data.

It is not possible to make a network connection to the two databases, so a dblink is not a possible solution.

-------------------------

select table_name from s1.user_tables

minus

select table_name from s2.user_tables

vice versa

for all the other objects in question write sql the like

or use dbms_metadata for both schemas and then use your favourite editor to compare the files will all that ddl included

or take an exp from both schemas
imp /

@sid file=your.dmp fromuser=s1 show=yes log=s1.log
imp /

@sid file=your.dmp fromuser=s2 show=yes log=s2.log

then compare the logs and retrieve the differences

-------------------------

Use OEM Change Management to take the diff between two databases. Of course you would need OMS if want to take snapshots, but for simple comparison - it's not neccessary.,,

Database not Opening

2008-01-06T12:08:00.000-08:00

We have an amazing situation that we don't have the lastdays backup (as backup was not properly taken up)

The database gets mounted but don't get opened as one of the data file is missing.
Is there any way to recover the datafile and make the oracle server live once again.

Good question! There are ways to bring the DB up.

*********

SVRMGR> alter database datafile '/u01/oradata/ORCL/data/users01.dbf' offline drop;

SVRMGR> alter database open;

SVRMGR> drop tablespace users including contents;

*********

In the first stmt you can either use DROP or just use OFFLINE.It totally depends on what kind of data is out in that datafile.If un-necessary data then just drop it...if u happen to have valuable data then you can bring it offline and try to recover the DB.

Also you haven't mentioned if you are running a COLD or HOT bkp ?

Do you at least have an EXPORT.dmp of the tablespace?

If you have then just export the Tablespace.

I am not sure this might workout for your scenario but it may bring the DB up for now.

Thanks a lot , by taking datafiles offline , server could be opened and is now live.
but all the files taken offline are showing files 0 kb size.

Was it due to virus or anyother reason?

and these files now require auto recovery.

can these files be repaired now

However thanks for the advice

I ran the following command and the output came like this
Can you explain me the reason for the following output and also how the files can be recovered.

SQL> set autorecovery on;

SQL> recover automatic datafile 11;

ORA-00283: recovery session canceled due to errors

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file

ORA-01110: data file 11: 'E:\ORACLE\ORADATA\HHML\D_HH_ATEND.ORA'

Seems like the only option in your scenario would be dropping the tablespace and
re-creating a new one.

If you happen to have an export dump for the tablespace or objects in that tspace just import them after creating a new ts.

Datafiles Efficiency: More Files vs Increase the Size

2008-01-06T11:20:00.000-08:00

Which is more efficient; adding more data files or increasiong the size of the data files of a tablespace?

Running on oracle 9.2 on win 2000 server.

It's mostly a personal preference. One thing to consider is recovery. If you have fewer, larger files then restoring and recovering a file with a bad block will take longer than if you had more, smaller files.

If you are not using raid then it is better to spread the data on multiple files on multiple disks.

It depends what kind is your storage: local disks or RAID

For local disks - you want to have many datafiles on as much local disks as possible

For RAID - it really doesn't matter. The datafiles are stripped anyway. it's a more for convenience not to have huge datafiles, since if you have to move them latter it's harder to find 20 GB disk space than 5 chunks of 4GB.

If you are going to be using RAID-5 (which is common), I cannot see how chopping up your database will help a lot. Unless you have multiple ranks of RAID-5 storage, all your data is stored in one big blob over which you have little physical control, if any.

I usually separate my table-tablespaces and index-tablespaces into different tablespaces and subtending datafiles, more of a matter of management than of performance, since I am not a big fan of self-extending Oracle storage.

RAID-5 automatically scatters your data when it is stored; it is part of its reliability functions and also what can help your performance in queries. If I were you, I would separate the database logically, and not arbitrarily separate the storage on physical boundaries, given they are meaningless in the RAID-5 environment unless you have multiple controllers and RAID ranks.

Oracle Service Consuming Lot of CPU Resource

2008-01-06T10:46:00.000-08:00

System Config :
OS : Win 2000 A.S
Oracle 11.5.8
Rdbms : 8.1.7

Usaully my Oracle use to work very good but some times once in a blue moon , when I restarted my Database after performing a nightly backup , it startup quickly but when I look into the Task Manager , it shows Oracle.exe service is consuming a lot of CPU ( 70 – 80 % ) even wen no user is connected , usually it won't.

Because of this behavior my Oracle Applicatrion services takes up a long time to get UP. And after sometime Users starts complaining that Application is very slow. I didn't understand what background process is running that is consuming a lot of CPU .

I was told to create a statspack but since I am new to DBA, can you pls. guide me how to create a statspack report.

Not getting it up quick enough is a real problem.

It could be a couiple of things. First, it takes time for your cache to "warm up". Initial database queries will take longer to run because of this. Second, maybe your database is not being brought down cleanly and it takes some time for SMON to clean things up.

Either way, I'd look at not using a cold backup.

Creating STATSPACK:

1. Create a tablespace for this purpose called perfstat or any good name

2. Run catdbsyn.sql dbmsspool.sql

Now you must understand the data collection level. Each collection is in one of this level:

a. Level 0

b. Level 5

c. Level 6

d. Level 7

e. Level 10

- Study the STATSPACK architecture -. Very important

3. Do a snapshot to capture the RAM structure

SQL> exec statspack.snap;

Statspack report is easy to see the current dabase situation

1) create a tablespace of 100mb size

2) run the script: oracle_home\rdbms\admin\spcreate.sql

It will prompt for your default tablespace then give the above tablespace name. After that just give the temporary tablespace name other than system..

3) take some snapshots under perfstat user.

the command will be:
exec perfstat.statspack.snap

take as many snap shots u want within a time interval.

4) now run the script

oracle_home\rdbms\admin\spreport.sql
it will ask for your destination and name of the output report file..

provide those...with to snapshot nos between which you want to see the reports...

now open the report file and enjoyyyyyyyyyyyy.,,

How to Start the Service Manager

2008-01-06T09:50:00.000-08:00

I am new to oracle. I want to know how to start the service manager and where should I give the command. What is the password for newlly installed oracle. Please help me out.

If you want to run SVRMGRL on UNIX then just log on to the UNIX with your user name and password.

1. Type SVRMGRL

2. Type connect internal

The message will say connected. Now you are connected to Oracle. If you are new to Oracle then make sure of you your commands, SVMGRL has a lot of power.

Now if you are connecting to an NT machine, then you need to start the command prompt.

1. Type SVRMGRL

2. It will ask you for a password, this is the same password that you created when you did the fresh install of oracle. Once you issue a password you should be in.

Thanks for the reply but I am working on Windows NT not Unix so let me know how to start svrmgr in it. Your help will be appreciated.

For NT do the following....assuming u have Oracle installed on c:drive if u have oracle on d: e: or anyother drive just replace the c:> with ur drive.

c:> set ORACLE_SID=ORCL ( whatever db name u have)

c:> svrmgrl

svrmgrl > connect internal

svrmgrl > select * from v$database; -- if Oracle not available ...do this

svrmgrl > startup

svrmgrl > select * from v$database;

You need to start the Listener too...for sqlplus conectivity..

c:> lsnrctl

lsnrctl > status -- if shows errors then you need to start the listener

lsnrctl > start

If you happen to have a named listener then u need to specify the name....

lsnrctl > start ORCL (whatever listener name you have ..check listener.ora file for that)

You should be all set

But remember man, server manager "svrmgrl" is unsupported 9i onwards. You have to use only sqlplus to get connected.

The person who posted this query didn't mention the version

I am new to oracle. I try to connect the oracle sever 8i (8.1.7) from windows 2000, I got error message no listner, then I try to start listner: start orcl and got another error message:
TNS-01151: Missing listerner name, oracle, in LISTENER.ORA

This is my listerner.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 2481))
)

)

What I need to do ? Please help.

According to your listener.ora file you don't have a named listener so while starting the listener you must do the following

lsnrctl start -- Correct

lsnrctl start orcl -- Incorrect

NOTE : Only for named listener you need to start like ... lsnrctl start orcl.

How to init an object type

2008-01-06T09:35:00.000-08:00

I have the following code :

CREATE OR REPLACE TYPE ObjB IS OBJECT (
B1 varchar2(10)
,B2 varchar2(10)
);

CREATE OR REPLACE TYPE ObjC IS OBJECT (
C1 varchar2(10)
,C2 varchar2(10)
);

CREATE OR REPLACE TYPE ObjCArr IS VARRAY(100) OF ObjC;

CREATE OR REPLACE TYPE ObjA IS OBJECT (
A1 varchar2(10)
,A2 varchar2(10)
,Ab ObjB
,Ac ObjCArr
);

a ObjA;

I've tried to initialize a, but each time I have an ORA-06530 error.

Any idea how to initialize it using Oracle 8.1.7 ?

If you want to initialize, use:

DECLARE a ObjA := ObjA(NULL,NULL,ObjB(NULL,NULL),ObjCArr());

Note: Since element a.Ac is a VARRAY of objects ObjC, you would have to use

a.Ac.EXTEND;
a.Ac(1) := ObjC('X','Y');

to add elements to VARRAY. You can not add it using:

a.Ac.EXTEND;
a.Ac(1).C1 := 'X';
a.Ac(2).C2 := 'Y';

You can use already existing a.Ac(1).C1 or a.Ac(1).C2 for existing elements only.

Shell Script To Dynamically Generate Init.ora File

2008-01-06T09:19:00.000-08:00

I am working on a need to clone databases..and in that I need to write a shell script which will 'dynamically generate' the init.ora file for target database using that of source database and a temp file which will hold changed value of parameters.

For example in temp file there is a param like:
db_name =targetdb

and in source init.ora file db_name=db1 then the shell script should read the temp file and be able to create a new init.ora file with changed value
ie db_name=targetdb (so all other parameters that are not specified in temp file will remain same, but all specified parameters will be changed.)

Can you suggest some similar scripts for such work?

--------------------------------------------/

Try something this:

CODE

set pages 0 term off feed off trims on lin 132 ver off

def fdb='FromDB'

def tdb='ToDB'

spo /tmp/init&&tdb.ora

Select NAME||'='||replace(VALUE,'&&fdb','&&tdb')

From DBA_PARAMETERS;

spo off

How much memory is been utilized by the SGA

2008-01-06T07:36:00.000-08:00

We are running SAP 4.6c with ORACLE 9.2.0.5 on AIX. Database is configured with Dynamic SGA, and we have set SGA_MAX_SIZE also.

When we look at the memory management at OS level it shows high utilization of memory.

We don’t know whether the memory allocated to SGA is completely used or not.
How to find out how much memory is been utilized by the SGA, whether it needs to be allocated more memory or can be reduced from the SGA.

v$sga will give you the current sga stats

Run statspack to analyze whether you reqiure more sga or not

You can also look at V$SGASTAT view. Try running the following script (note the part about free memory):

CODE

set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off


col num              format 999      heading "Nbr"
col name             format a20      heading "Name"
col type             format 999      heading "Type"
col value            format a10      heading "Value"
col meg              format 99.99    heading "Size|Meg"
col isdefault        format a10      heading "IsDefault"
TTITLE "SHARED POOL PARAMETERS (DICTIONARY AND LIBRARY CACHE)"
select num, name, type, value, (to_number(value) / 1024) / 1024 meg,
isdefault
from v$parameter
where name = 'shared_pool_size';


col AA format 99.99    heading "Size MB"
col BB format 99.99    heading "Free MB"
col CC format 99.99    heading "% Free"
col DD format 99999999 heading "Executions"
col EE format 999999   heading "Reloads"
col FF format 999.99   heading "% Reload"
col GG format a20        heading "Parameter"
col HH format 99,999,999 heading "Count|(entries)"
col II format 99,999,999 heading "Usage|(valid entries)"
col JJ format 99,999,999 heading "Gets|(memory)"
col KK format 9,999,999  heading "Misses|(disk)"
col LL format 99.99      heading "% Misses"


TTITLE CENTER 'V$PARAMETER (SHARED POOL SIZE) AND V$SGASTAT (FREE MEMORY)
REPORT '
select (to_number(VP.value) / 1024) / 1024 AA,
      VS.bytes / 1048576 BB,
      (VS.bytes / to_number(VP.value)) * 100 CC
from  v$parameter VP, v$sgastat VS
where VS.name = 'free memory'
and   VP.name = 'shared_pool_size';


PROMPT 

PROMPT More than 5% Free = lower shared_pool_buffer parameter


TTITLE CENTER 'V$LIBRARYCACHE (SHARED SQL AREA) SUMMARY REPORT' STITLE
CENTER 'V$LIBRARYCACHE (SHARED SQL AREA) SUMMARY REPORT'
select sum(VL.pins) DD,
      sum(VL.reloads) EE,
      (sum(VL.reloads) / sum(VL.pins)) * 100 FF
from v$librarycache VL;


PROMPT
PROMPT More than 1% Reloads = raise shared_pool_size parameter


TTITLE CENTER 'V$ROWCACHE ENTRIES DETAIL REPORT (DICTIONARY)'
select parameter GG,
      /* count HH, */
      /* usage II, */
      gets JJ,
      getmisses KK,
      (getmisses / (gets + getmisses + 1)) * 100 LL
from v$rowcache
order by parameter;


PROMPT
PROMPT Not tunable at this level of detail, provided for information
only.


TTITLE CENTER 'V$ROWCACHE ENTRIES SUMMARY REPORT (DICTIONARY)'
select sum(gets) JJ,
      sum(getmisses) KK,
      (sum(getmisses) / (sum(gets) + sum(getmisses) + 1)) * 100 LL
from v$rowcache;


PROMPT
PROMPT Not tunable at this level of detail, provided for information
only.


TTITLE CENTER 'V$ROWCACHE ENTRIES SUMMARY REPORT (DICTIONARY)'
select sum(gets) JJ,
      sum(getmisses) KK,
      (sum(getmisses) / (sum(gets) + sum(getmisses) + 1)) * 100 LL
from v$rowcache;


PROMPT
PROMPT More than 5% Misses (summary) = raise shared_pool_buffer parameter





exit;

Unable to Create a New Instance

2008-01-06T07:29:00.000-08:00

I am attempting to create a new instance in my sun solaris 8 server. I have set the environment and prepared the scripts. I do not have gui to perform the db creation therefore i need to use the scripts and run from sqlplus.

it's a 9.2.0.1 database. I have added in oratab. However when I run this script in sqlplus -

I get the following error:

SQL> @/oracle/ora01/app/oracle/admin/xmbdb/scripts/CreateDB.sql
Connected to an idle instance.

SQL> spool /oracle/ora01/app/oracle/product/9.2.0/assistants/dbca/logs/CreateDB.
log

SQL> startup nomount pfile="/oracle/ora01/app/oracle/admin/xmbdb/pfile/init.ora"
;
LRM-00109: could not open parameter file '/oracle/ora01/app/oracle/admin/xmbdb/p
file/init.ora'
ORA-01078: failure in processing system parameters

SQL> CREATE DATABASE xmbdb
2 MAXINSTANCES 1
3 MAXLOGHISTORY 1
4 MAXLOGFILES 5
5 MAXLOGMEMBERS 5
6 MAXDATAFILES 100
7 DATAFILE '/oracle/ora02/oradata/xmbdb/system01.dbf' SIZE 325M REUSE AUTOEXT
END ON NEXT 10240K MAXSIZE UNLIMITED
8 UNDO TABLESPACE "UNDOTBS" DATAFILE '/oracle/ora02/oradata/xmbdb/undotbs01.d
bf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
9 CHARACTER SET US7ASCII
10 NATIONAL CHARACTER SET AL16UTF16
11 LOGFILE GROUP 1 '/oracle/ora02/oradata/xmbdb/redo01.log') SIZE 100M,
12 GROUP 2 ('/oracle/ora02/oradata/xmbdb/redo02.log') SIZE 100M,
13 GROUP 3 ('/oracle/ora02/oradata/xmbdb/redo03.log') SIZE 100M;
CREATE DATABASE xmbdb
*
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> spool off

SQL> exit;
Disconnected

How can I rectify this problem? Please Advice.

The problem you are encountering here is that you have a Permission issue. Try to check all the files you created are under the user Oracle and dba Group. It seems like you have some folders created under diff user other than Oracle.When you try to aceess a folder which Oracle may not have permissions will fail. Just make sure all the folder and file permissions are consistent with Oracle:dba

I have checked the permissions for the folders - they all belong to oracle user. DoIi need to create udump, bdump, cdump and pfile folders and put in the init.ora file into pfile folder before running the scripts?

There's no need to create all the dump folders. They are included in the init.ora file by default...unless you are trying to create your own init.ora file. Its always better to use an existing init.ora file of other db and modify accordingly. Try that method and run the script again.

Unable to locate statement causing error

2008-01-06T07:27:00.000-08:00

When I ran the procedure below, I am running into the following error:
The error is ORA-00936: missing expression
I tried to put DBMS_OUTPUT.PUT_LINE statements to see where it is failing. I received the following output

SQL> exec proc_load_user_privileges
The error is 6
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 4
The error is 6
The error is ORA-00936: missing expression

It looks like right after the loop is done, the error is happening. How can I zero in on the statement that is causing the problems? I tried to run all the sql statements by themselves in sql*plus and they are working fine. I am not sure where the syntax error is. Any help would be greatly appreciated.

Here is the procedure text

CREATE OR REPLACE PROCEDURE proc_load_user_privileges
AS

CURSOR cur_list_of_cols
IS
SELECT column_name FROM user_tab_columns WHERE table_name = 'TEMP_NSDA_USER_PRIVILEGE' AND column_name NOT IN ('SUN_ID', 'NODE_ID', 'OPERATION', 'FIRST_NM', 'LAST_NM', 'FUNCTIONAL_AREA_NM');

CURSOR cur_temp_nsda_user_privilege
IS
SELECT SUN_ID,NODE_ID,OPERATION,FIRST_NM,LAST_NM,FUNCTIONAL_AREA_NM FROM TEMP_NSDA_USER_PRIVILEGE;

v_current_sun_id VARCHAR2(30);
v_current_node_id NUMBER(20);
v_current_privilege_nm VARCHAR2(30);
v_current_user_privilege_val VARCHAR2(30);
v_current_column_name_in VARCHAR2(30);
v_block_str VARCHAR2(500);
v_dynamic_query_handle INTEGER;
feedback INTEGER;
v_curr VARCHAR2(30);
v_operation VARCHAR2(30);
v_first_nm VARCHAR2(30);
v_last_nm VARCHAR2(30);
v_functional_area VARCHAR2(30);

BEGIN
FOR rec_temp_nsda_user_priv IN cur_temp_nsda_user_privilege
LOOP
v_current_sun_id := rec_temp_nsda_user_priv.sun_id;
v_current_node_id := rec_temp_nsda_user_priv.node_id;
v_operation := rec_temp_nsda_user_priv.operation;
v_first_nm := rec_temp_nsda_user_priv.first_nm;
v_last_nm := rec_temp_nsda_user_priv.last_nm;
v_functional_area := rec_temp_nsda_user_priv.functional_area_nm;

IF v_operation = 'UPDATE' THEN
DELETE FROM NSDA_USER_PRIVILEGE_T
WHERE sun_id = v_current_sun_id;
END IF;
DBMS_OUTPUT.PUT_LINE('The error is 6');
FOR current_col IN cur_list_of_cols
LOOP
v_current_privilege_nm:= current_col.column_name;

v_block_str:=
'SELECT ' || v_current_privilege_nm || ' FROM TEMP_NSDA_USER_PRIVILEGE WHERE sun_id = ' || v_current_sun_id || ' AND node_id = ' || v_current_node_id ;

EXECUTE IMMEDIATE v_block_str INTO v_current_user_privilege_val;

BEGIN
SELECT
REPLACE(v_current_privilege_nm, '_', ' ')
INTO v_current_privilege_nm
FROM DUAL;

END;
DBMS_OUTPUT.PUT_LINE('The error is 1');

IF (v_current_user_privilege_val = 'Y')
THEN

INSERT INTO NSDA_USER_PRIVILEGE_T (sun_id, node_id, privilege_nm) VALUES (v_current_sun_id, v_current_node_id, v_current_privilege_nm);

ELSIF (v_current_user_privilege_val IS NOT NULL) AND (v_current_user_privilege_val != 'N')
THEN
v_current_user_privilege_val:= TO_NUMBER(v_current_user_privilege_val);
DBMS_OUTPUT.PUT_LINE('The error is 2');

INSERT INTO NSDA_USER_PRIVILEGE_T
(sun_id, node_id, privilege_nm, user_privilege_value)
VALUES
(v_current_sun_id,v_current_node_id,v_current_privilege_nm,v_current_user_privilege_val);

END IF;
DBMS_OUTPUT.PUT_LINE('The error is 3');

END LOOP;
DBMS_OUTPUT.PUT_LINE('The error is 4');

END LOOP;
DBMS_OUTPUT.PUT_LINE('The error is 5');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is ' || SQLERRM);
END proc_load_user_privileges;

Deleting all objects from a tablespace

2008-01-06T07:22:00.000-08:00

Just want to know what's the best way to delete all the objects (tables, indexes, squences, etc..) from a tablespace?
Delete the tablespace and recreate it?

Just drop the tables from the User(s) whatever exists in that tablespace.This way u can be sure everything is removed.You don't need to drop the tablespace. If you are using Oracle Enterprise Manager(OEM) its very simple...just click on the database-schema-tables-select all the tables in that user and remove them from the schema....do the same for the views/synonyms/seq...

(OR)

If you are not having OEM then u can write a sql stm to drop the tables/views/synonyms/....from each user individually.

(OR)

If you are not worried about the users and the data in it....simply drop the user which will remove everything.

First list out all the users in that tablespace and proceed.

I do have OEM installed. When I try to run it, it will not let me connect. I'm leaving the management server box blank (the database is local) and using all the different username/password combos I can think of that oracle came with. Am I missing something here?

For logon to OEM u might be using a right password but may be the TNSNAMES.ora file have some issues.Check your sqlnet.ora file and all the tns aliases in the tnsnames.ora file and make sure ur using .world in your host or just the string.

Ex: system/manager@orcl.world --if sqlnet.ora has names.default_domain=world
Ex: system/manager@orcl --if sqlnet.ora dont have any entry or commented for names.default_domain=world

The thing is OEM configures itself with the TNSNAMES.ora once installed so while conecting it only expects you to connect using uname/pwd as the host name is already picked by it.In the worst case create a new database tree and try.

Deleting duplicate rows in the table

2008-01-06T07:15:00.001-08:00

I am searching for a way to delete the duplicate records in a table..
Is anybody having efficient way to do it..?

The command is:

DELETE dv
FROM a WHERE rowid <
(SELECT MIN(rowid)
FROM b
WHERE a.dv = b.dv);

i.e. if the table name is emp and the dupicate column value is ename:

DELETE ename
FROM emp a
WHERE rowid <
(SELECT MIN(rowid)
FROM emp b
WHERE a.ename = b.ename)

Another solution is:

delete from where rowid not in (
select max(rowid) from group by );

How to Drop undo Tablespace

2008-01-06T07:10:00.000-08:00

I create a new undo tablespace, then I want to drop the org undo ts and recreate is with a smaller size.

However, after switching Ii am not always able to offline the org tablespace.

I get an Ora-30042:Unable to offline the undo TS.

This error is always returned when using the procedure. This is in a test db, where I'm the only one logged in (sqlplus /nolog).

Lookin in de alert.log it states:
Undo tablespace 1 moved to Pending Switch-Out State.

What does this mean? How can I make sure the undo TS is not being used anymore, so I can offline it?

Using this command shift user UNDO and the try to drop old one.

ALTER SYSTEM SET UNDO_TABLESPACE = ....

Note of cautious:

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPACE drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo tablespace if undo information is needed by some existing queries.,,

Select Entries from Whole Week

2008-01-06T05:33:00.001-08:00

Get all entries from "the whole week" (begin: Monday end: Sunday) from e.g the day sysdate - 222 (today: 20.10.2004) . This means all entries with a date between 08.03.2004 and 14.03.2004 (including the borders !)

create or replace
procedure whole_week (week date) is

total number(6);
day varchar2(20);

begin
total:=0;

select to_char(sysdate,'day') into day from dual;

if day='montag' then
select sum(total) into total from s_ord where date_ordered between week-1 and week+5;

else if day='dienstag' then
select sum(total) into total from s_ord where date_ordered between week-2 and week+4;

else if day='mittwoch' then
select sum(total) into total from s_ord where date_ordered between week-3 and week+3;

else if day='donnerstag' then
select sum(total) into total from s_ord where date_ordered between week-4 and week+2;

else if day='freitag' then
select sum(total) into total from s_ord where date_ordered between week-5 and week+1;

else if day='samstag' then
select sum(total) into total from s_ord where date_ordered between week-6 and week;

else if day='sonntag' then
select sum(total) into total from s_ord where date_ordered between week and week+6;

else
dbms_output.put_line('kein tag');

end if;
end if;
end if;
end if;
end if;
end if;
end if;
dbms_output.put_line(total);

end;
/

begin
whole_week(sysdate+1);
end;
/

instead of montag you have to write monday and so on...i've hat a german version so i've got this names for the days.

montag is monday

dienstag is tuesday

mittwoch is wednesday

donnerstag is thursday

freitag is friday

samstag is saturday

sonntag is sunday

Modifying A Column Of A Table

2008-01-06T05:14:00.000-08:00

I have a table lets say demo, and one of the columns of this table is time_in_sec is of type number(38).

This table has around 20,000 rows of data. Now I want to modify the time_in_sec column of demo table to number (7,3) so that I can store values like 234.987

All the existing datas in this column are numbers up to maximum of 3 digit whenever I am trying to modify the column I get errors like ORA-01440: column to be modified must be empty to decrease precision or scale one solution to this was drop this column and add new column but I do not want to loose existing data.

How to achieve the changes I want ie changing the column type from number (38) to number (7,3)

CODE :

Create Table Demo_Time_In_Sec As Select Rowid Row_Id, Time_In_Sec From Demo;

Update Demo Set Time_In_Sec = Null;

Commit;

Alter Table Demo Modify (Time_In_Sec Number(7,3));

Update Demo U Set Time_In_Sec = (

Select Time_In_Sec From Demo_Time_In_Sec T
Where U.Rowid = T.Row_Id);

Commit;

Drop Table Demo_Time_In_Sec;

Modify the Primary Key Order

2008-01-06T05:03:00.000-08:00

I have created a composite primary key (a,b,c) in the table. It has references in other table also.

Now I want to modify the primary key order - (b,c,a). How to go about it?

You can try this sequence:

1) disable primary key related foreign constraint on child table;

2) disable primary key constaint on present table

3) drop index

4) recreate index

5) enable all disabled constraint

The enclosed scrip will do it:

CODE

FILE: recreate_pk_ddl.sql

set pagesize 999 heading off verify off termout off

set feedback off sqlcase upper newpage 3 rowsize 400

SELECT constraint_name column1,5 column2, 0 column3,
'alter table '||table_name||' add constraint '||table_name||'_pk primary key ('
FROM user_constraints where
constraint_type = 'P'

UNION

SELECT constraint_name,10,position,
decode(position,1,' ',',')||column_name
from user_cons_columns
where constraint_name in (select constraint_name from user_constraints
where constraint_type = 'P')

UNION

SELECT constraint_name, 20,99,' )' from user_constraints

union

select constraint_name, 30,99,' using index' from user_constraints

union

select constraint_name, 40,99,' pctfree 10' from user_constraints

union

select constraint_name, 50,99,' tablespace indexes ' from user_constraints

union

select constraint_name, 60,99,' storage( ' from user_constraints

union

select constraint_name, 70,99,' initial ' from user_constraints

union

select constraint_name, 80,99,' next ' from user_constraints

union

select constraint_name, 90,99,' pctincrease 0 );' from user_constraints

union

select constraint_name, 100,99, ' ' from user_constraints

order by 1,2,3;

Renaming A Datafile

2008-01-06T04:15:00.000-08:00

Renaming A Datafile

I'm using Windows XP and Oracle 9i R2. The issue are as follows:

1. I found 2 strange names in the V$datafiles. They are .ORA and logdata03 . What I wanted is all datafiles have .DBF extension.

2. In order to rename the file I shutdown the database and rename the physical file (.ORA first)

3. I think I've done something wrong here because I do not really need to shuttdown the database, I just need to take that datafile offline and change the name physically and in the database.

4. Since I'm using a Laptop which is impossible to always generate ARC files, I set my database to NO ARCHIVE LOG.

The questions are:

When I wanted to take the datafile offline, I use this command

Alter tablespace finance datafile 44 offline;

But an error appears that I supposed to have the database in ARCHIVE LOG mode.

then, I just try my luck with this syntax

ALTER TABLESPACE FINANCE DATAFILE 44 OFFLINE DROP;

It can, then I can start renaming the datafile again.

1. What is the meaning of OFFLINE DROP? because at first I thought it will take the datafile offline and Drop the datafile as if you are deleting it.

The DROP keyword must be specified if the database is in NOARCHIVELOG mode. The datafile is not dropped, it is flagged as either OFFLINE or RECOVER.

2. What actually happened to the redolog file when I did that command? I think that when we are going to make one of the datafile offline, Oracle will reset all the log in the logfile after applying the changes to that offline datafile.

When a datafile is placed offline while the database is open, media recovery must be performed to bring the datafile back online.

The online redo logs will be used to perform media recovery before you can bring the datafile online but the redo logs are not reset.

3. I did this in my laptop which is me the only person who did the transactions. what might happened if I did this in the live system with busy transaction 24 hours?

Data in the datafile that was taken offline would not be accessible until it is online once again. Consider the following:

CODE :

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> create tablespace test datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' size 8M
2 extent management local segment space management auto;

Tablespace created.

SQL> create table test_table tablespace test as select * from all_objects where rownum <= 10000;

Table created.

SQL> select count(*) from test_table;

COUNT(*)
----------
10000

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' offline;
alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' offline drop;

Database altered.

SQL> select count(*) from test_table;
select count(*) from test_table
*
ERROR at line 1:
ORA-00376: file 17 cannot be read at this time
ORA-01110: data file 17: 'G:\ORACLE\ORADATAIDB\TEST.DBF'

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' online;
alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' online
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: 'G:\ORACLE\ORADATAIDB\TEST.DBF'

SQL> alter database recover datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF';

Database altered.

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' online;

Database altered.

SQL> select count(*) from test_table;

COUNT(*)
----------
10000

happy new year

2007-12-30T22:38:00.000-08:00

2007-12-07T14:55:00.001-08:00

Tablespace Change

I created a user as below
create user paymaster identified by paymaster
default tablespace users
temporary tablespace temp

this created user paymaster.
however when i queried
select owner,tablespace_name from dba_tables
the output was as below:-

OWNER TABLESPACE_NAME
PAYMASTER SYSTEM

the paymaster should be in tablespace 'PAYDATA'
this tablespace is already created.
How do i create user PAYMASTER to use PAYDATA?

I had earlier dropped user paymtr from the database as the client wants paymtr to be changed to paymaster.
I created as above and granted the roles.
However when I tried to import from *.dmp file that was exported from paymaster in another machine(which was in system tablespace) and I queried from dba_tables to find all tables are in system tablespace.
How can I retify this?

Change the tablespace from SYSTEM to PAYDATA without dropping the tables.
sql > Alter user PAYMASTER default tablespace PAYDATA
temporary tablespace TEMP;

and for each table - move them to PAYDATA tablespace...try to create a script for the below for all the tables ..

sql > Alter table tablename default tablespace PAYDATA;

Also check the indexes ...if they happen to be in SYSTEM tspace do the above and move them into PAYDATA tablespace or anyother where you wish.

I have managed to move the tables from system to paydata. However there is one table with datatype long which I am unable to move.
Any suggestions on how to solve this.

In order to move the table with a LONG col datatype you can generate a table script manually and import the data into that table.

If you have problem generating table script use TOAD to generate the table script and also the INSERT statement script and run the script from sqlplus.You should be good.

2007-12-07T14:53:00.001-08:00

Dynamic Table Names and Moving Table

I'm fairly new to PL/SQL and I could use some help. I'm trying to create a cursor and build the tablename based on an input parameter. Currently I'm using DBMS.SQL statements, which I find cumbersome to use. I'd like to find an easier way if possible. Here is an example of what I'd like to do;

Cursor DYNAMIC is

SELECT column1 from I_PARAM||restoftablename
in the above example I_PARAM is an input paramter.

You can try using "EXECUTE IMMEDIATE" statement create a table dynamically.....also do get a second opinion.

*****************
declare
my_table varchar2(400);
begin my_table := 'create table dynamic_table (my_name varchar2(20))';
EXECUTE IMMEDIATE my_table;
end;
*****************

In Oracle DBS 7.3, how can I move a table from one tablespace to another without losing permissions, index's...etc for this table?

Hmmm good question ....i used diff methods but nothing like this in 7x...but still i'll try....

* First i would take an export of that table (say mytable.dmp)

* Open mytable.dmp in note pad.

* Edit the tablespace to the new tablespace name(whichever tablespace u wanna move).

* Save the file

* Try to import the dump file(mytable.dmp) into the new user schema u want to.

I can't gaurentee that this procedure would work but its worth a try on test table first.

2007-12-07T14:51:00.001-08:00

Securing Database 9i, User rights

I have mid-size database that have multiple schemas. The schema usually accessed by applications. However, there is 5 users who access the database locally to check some infomations. In the past they used to logon as system which cuase some problems! I want to create accounts for them and give them read-only for all user create schema (no system access). and I would like to track which user did what.

grant select any table ....... privilage to the users this solves the first part.
As you're on 9i , you can use the FGA (Fine Grained Auditing) feature to track the select queries given by the users.

Grant select, resource will gives them read only? will that enable them to view the data via EM and change or not? I think once they logged in the EM they will be able to change data?

Select is a read only privilage on a table. Why don't you try it out so that your doubts are cleared.

I did test it. the user who have select any table, will not be able to login to EM, becuase EM have have many data dictionary parameters and instance, storage and so on.

but after

grant create session
grant select any dictionry
grant select any table

Then it works fine. So I will create new rule "test" and give it the above rights then assign it to the users.

2007-12-07T14:45:00.000-08:00

Can we Change a Column Name

Suppose I have a table names student having a column named as "> roll_no". now I have to change its name by " registration_no ". Can we do this?

Renaming a column of an Oracle is possible.
ALTER TABLE ..table_name .. RENAME COLUMN ..old_column_name.. to ..new_column_name..

Column rename feature is supported by oracle 9.x onwards

Thus in caes you are using oracle 9.x or later versions use following query
ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;
In case you are usinf oracle prior to 9 use following options.

Other workarounds:

1. -- Use a view with correct column names...
rename t1 to t1_base;
create view t1 ..column list with new name.. as select * from t1_base;

2. -- Recreate the table with correct column names...
create table t2 ..column list with new name.. as select * from t1;
drop table t1;
rename t2 to t1;

3. -- Add a column with a new name and drop an old column...
alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;

You can rename a column in oracle.
try this,

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

2007-12-07T14:42:00.000-08:00

Shutting Down Oracle On Windows

What is the difference between shutting down an Oracle database (8i and up) on Windows by logging into the instance and issuing the shutdown command vs stoping the Oracle service (OracleServicedbname)?

I think there's no difference - but you can see many more informations by logging in with sqlplus/svrmgrl and shutting down the DB with the shutdown command.
Using MMC and shutting down the service shows only a status bar.

Depends on the version of Oracle and how the instance is configured in the Windows registry. For 9i (not sure about 8i, don't have an 8i instance anymore), each instance has a key in the registry called ORA__SHUTDOWN_TYPE that determines the type of shutdown to initiate when the service is stopped (i = immediate for example). The key ORA__SHUTDOWN_TIMEOUT specifies the amount of time to wait (seconds I think but I never got a clear answer on this) before the instance is terminated.

That being said, in my opinion it is always best to shutdown the database manually thru SQL*Plus. That way you know what is going on.

There is a difference.

The service must be running in order to have the database running.
The service can be running without the database running.

It's simple to see: start the service, start the database (if not set to automatic start), then connect as sysdba and do a shutdown. Then go look at the services - the OracleServiceSID still shows started.

Stop the service, and then start a SQL*Plus session. Try to connect or start, and you get Oracle not available error.

You can shut down the database either through the sql prompt or by shutting down the associated oracle service , in both the cases the database will shutdown its same if your purpose is to shutdown the database a service is continuously running program which helps other program to run or execute.

But when you stop the oracle service you close the door for everything associated with the service so if you stop the service you cannot start the database unless and until you start the service again.

If you look at the oracle database from programmers perspective you can feel oracle database is not one program, its number of programs running togerther and the oracle service you can think of as the link between all these programs.

On Windows you have "services" which could be started or stopped. You can have Oracle DB up/down only if the service is started.

You can also enable Oracle DB instance to shutdown and startup with the service shutdown/startup. On *NIX you don't have services and so such restrictions.

2007-12-07T14:40:00.000-08:00

Tuning a Wait Session

When I was about to bring down the server in an immediate mode for the start of the weekend maintenace window, I saw a user session is still active where he was trying to run a query. This query, in the past, took a minute or so and now its more than half hour. All I could do is kill it.

The thing I am wondering about is, if time permits, the approach towards tuning it. How do I find out which object is he accessing, is there a wait for any latches or is it undergoing any full table scans or is it waiting for any resource to be freed and etc..

----->

The first thing you should do is select * from v$session_wait where sid = [his session ID]. We can go from there. Run that query multiple times a few seconds apart.

----->

****************

To find out what sql the problem session(s) are executing, run the following query:

select s.sid, event, wait_time, w.seq#, q.sql_text
from v$session_wait w, v$session s, v$process p, v$sqlarea q
where s.paddr=p.addr and
s.sid=&p and
s.sql_address=q.address;

****************

I suggest you drop v$process from that query because you're not using anything in it. And suggest you change v$sqlarea to v$sql because the latter is much less expensive.

To be more accurate, v$sql (or v$sqlarea) contains the SQL the session either is running or last ran, depending on the info in v$session_wait.

2007-12-07T14:38:00.000-08:00

Update B with the Help of A

I am in great problem since last 3 days by the following problem.

I have 2 tables as A and B both having 2 fields as name and roll. I have to update the B table with respect to table A.

Can we do this with the help of query.
I have written a code for this and giving error and system goes to infinite loop.

The code is following:

Declare
cursor c1 is select * from A where roll IN ( select roll from B);
rec_set emp1%rowtype;
begin
open c1;
fetch c1 into rec_set;
loop
update B set B.name=rec.set.name where B.roll=rec_set.roll;
end loop;
close c1;
end;

When I complied this block, it then goes to infinite and then I need to stop its services to again login the system.

Looks like in the line

update B set B.name=rec.set.name where B.roll=rec_set.roll;

the underlined part should be rec_set

I think this could be done using a single query also

update B

set B.name = (select name from A T1where T1.roll = B.roll)
be careful the above query will be valid only if the embedded query returns just one row.

2007-12-07T14:34:00.000-08:00

Changing Prompt in SQL Plus

I have more then one Oracle 8 database servers. I need to have SQL promp to be my connect string. Username whenever I login in SQL Plus. Connect String is the same as Server Name.

Example:

Server 1 = aaaaa
Server 2 = bbbbb
Username = Naaa

Prompt should be

aaaaa.Naaa or bbbbb.Naaa

I use the following sql script to change my prompt. Modify it according to your requirements.
Copy and paste the below script (between .............) into a file named login.sql.
Copy this login.sql file in the working directory of your sqlplus (like c:\oracle\ora81\bin).
Oracle looks for a file named login.sql in its working directory. If it finds one then it executes it.

..................

set lines 1000
set pages 50
set serverout on size 500000
set head off
set pages 0
set termout off
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
/
spool z1.sql
select 'set sqlprompt '''||lower(global_name)||':'||user||'''' from sys.global_name
/
spool off
@z1
set head on
set pages 60
set termout on

...................

I take a slightly different approach. I use the "new_value" clause of the column statement.

Here is the script:

-- setPrompt.sql
-- -------------
set term off

column prompt_col new_value prompt_var

define prompt_var = "not connected> "

set prompt off
select lower(user) || '@' || ' (' || global_name || ')' || chr(10) || ' > ' prompt_col
from global_name
/

set sqlprompt "&prompt_var"

set term on

2007-12-07T13:16:00.000-08:00

Droping Logfile Group Failed

How to drop a log file group that is being the current logfile group?
I have 4 groups. In that I want to drop one group and that group is the active one.
If I drop that group it is telling that it is the current group.
If I try to switch logfile it is telling that database is not open.

----->
Have you use the SYS user to start the database ?

SQL> conn sys/ as sysdba
connected to an idle instance
SQL> startup

----->
In mount stage I want to drop it.

----->
In mount stage you can drop non-current log group but cannot drop or switch current log group.

----->

1. You must have at- least two online groups.

2. You can not drop a active online active redo log group.

3. If it active switch it by alter system switch logfile before dropping.

3. make sure that online redo log group is archived ( if archiving is enabled).

Syntax :- Alter database drop logfile group .

----->

Open the database
switch logfile.
drop the logfile

Why do you want to drop the logfile only on mount stage??

----->

There was some problem with the logfile while opening the database. So to rectify that I need to drop that group.

Database is not starting.. If it get started then I would have used switch log file command then I would have droped it.
Since it is not starting I am forced to drop it in mount stage.

Since the log file is the current log then in mount stage switch log file is not working.
So what I did I recreated control file. Now it is working

----->

Well if the problem has been solved, No issues.
But the appropriate solution would have been:

Alter database clear unarchived logfile group <>;

----->
My database is in no archivelog mode.. will it work....

Moreover suppose if it is in archivelog mode then

If we clare the logfile group will it clear the data or will it move the data to the logfiles... and what effect will be there if the database is in archivelog mode? Like dataloss?

----->
This statement is to be used only when the current logfile has became corrupt and cannot be dropped.It will simply clear the contents and will not copy them any other log group

ALTER DATABASE CLEAR LOGFILE GROUP ;
Use this for noarchivelog mode

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP ;

Use this verison of clearing a log file if the corrupt log file has not been archived

----->
I have a question how does creating a new control file , solves the problem as the new control file will be pointing to the current logfile which has the problem

----->
In the new control which I created in the trace file I edited and droped the logfile which is giving problem and then assigend the new control file to the database.

2007-12-07T11:47:00.000-08:00

Advantage Between Range Scan & Skip Scan In Oracle

Can someone explain me how the range scan is better than Skip Scan in Oracle 9i.
Is it advantage of tuning queries to take RANGE SCAN access path than SKIP SCAN path?

In index range scans,
</quotedoc>

Multiple rows with identical values are sorted in ascending order by rowid.
< /quotedoc>

in Skip scan

quotedoc>

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

< /quotedoc>

My understanding is, Skip scanning is good only if the cardinality of the leading columns are low ( very few distinct records).
if the cardinality of the rest columns are too low, eventually the CBO will ignore the indexes.

2007-12-07T11:15:00.000-08:00

How Can I Copy Data in Another System

I am using oracle 9i.

Now I want to copy its whole data like tables, cursors, function, procedure and trigger to an another system which has no link with my first system.

Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. export / import are logical backup system in oracle.

Just type exp(in case of export) or imp (in case of import) at prompt, you will get online help from oracle and supply parameters, it will serve your purpose.

Otherwise, the command is

exp file=..filename.dmp.. full=y statistics = none (will export all schema backups)

for example,

exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
(table level export)
exp scott/tiger file=emp.dmp tables=(emp,dept) (exporting 2 tables)

Similarly import command will be like....

imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott
tables=dept

2007-12-07T11:01:00.000-08:00

Is IMPORT & EXPORT is platform dependent

Please help me to find the soultion for this,,,,

I'll explain u my problem....

1. I have a Unix server Running Oracle 7.2
2. I have a user called 'Namankan' on Oracle DB
3. I have to shift the entire tables of user 'Namankan' from this System to a new server running Windows XP (or NT) with Oracle 8 (Not 8i)

If you are going to suggest me that I spool the entire data into text files and import them by running SQL*Loader in the new system, Pls dont suggest.......coz
i) Data is too large, ii) Table level validations in database will not allow the data to go in.
I have tried making a .dmp file using exp command on Unix Machine with default parameters, and have exported the entire data into a file called Intecons.dmp which is 175 MB, zipped into 28MB

Then I have taken this file onto Windows system with Oracle 8 and tried to import it through Imp80 command

It says
* Invalid Header Information, OS different (some such 2-3 lines)

As suggested by someone in a yahoo chat room, I Have tried to change the header information in .dmp file using a hex editor, I matched it to same as it was in a sample .dmp I made using exp80 command on my windows machine.......... Sometimes, it asks 2-3 questions and after replies starts dumping junk......... sometimes it hangs the system

I also came across to a data extraction program NXTract, however, its similar to spooling of files, even if it extracts data from .dmp file, so it is useless for me

IMP/EXP IS NOT PLATFORM DEPENDANT!!!!!!!!

Gawd Hex editor hacking on dmp binary files.... thatz weird!!!
The guy who gave you that advice is either brilliant
or
....let me leave this here

Firstly:

With the Oracle EXPORT and IMPORT utilities you can transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
ie: Dump files also aren't OS-specific; as a result, you can use Export/Import to transfer data between databases on different platforms.

Secondly:

Exporting data from a lower version of oracle into a higher version===no problem
The vice versa ==== got to do it tactically!!

IN your case Puneeth:

From 7.3.3 to 8 => Use the EXPORT 7.3.3 to export the data from the 7.3.3 database and IMPORT 8 to import the data into the 8 database. This should work just fine without any dependancy on the OS.

The PROBLEM of yours could be simple as this:
Did you use ftp to transfer the dmp file from the unix system in to ur target machine?
IF SO: did you type

ftp..bin

before you did the transfer?

When transferring the export dump file over a network, the file has to be transmitted in binary mode.

Transmitting export files in character mode (ASCII) causes errors when the file is imported.

If the problem is still not solved post the errors that are being logged so that we could actually understand the problem come up with some clear cut answer!!!

I see that you have actually posted a similar posting somewhere where you have suggested to pay the person who helps you get through this.... so is that offer still valid here :+)

As per oracle corp:

When creating an export dump file for an import into a higher release database (e.g.: from Oracle8i to Oracle9i), use a version of the EXPORT utility that is equal to the version of the source database (= lowest version = Oracle8i in this case).
The export FAILS if you use a HIGHER release export version.

For example, if you use the export 9.0.1 utility the export data from an 8.1.7 database, you will get the errors:

EXP-56 Oracle error 942 encountered
ORA-942 table or view does not exist
EXP-0 Export terminated unsuccessfully

Solution: use the lowest release export utility (8.1.7 in this case).

Hence I dnt think you can use oracle8 exp on the oracle7.3 database and get things right....!!

And i totally agree HEX editor on dmp files==bad idea.

2007-12-07T10:47:00.000-08:00

Error in PL/SQL block of trigger

I have written a trigger whose PL/SQL block contains a simple select statment among many other statements.
Now I find that, if the select statement returns no rows the trigger does not continue its operation further and aborts there itself. And if the select statement returns some rows, then it works fine.

I tried to execute a simplified PL/SQL block of the trigger in SQL*Plus and following were the results:

************************

declare
tempdate date;
begin
select trdt into tempdate from inv_trans;
if sql%notfound then
null;
end if;
end;
/

************************

When no data is present in inv_trans table, the result was:
************************

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

************************

And when the table inv_trans had data, the result was:

************************
PL/SQL procedure successfully completed.
************************

Why is the piece of code flashing an error when I have already given a treatment if no data is found.
Why is it taking "No Data in table" as an abnormal condition and not normal?

-------------------------

Your problem is in the fact that you haven't given a treatment if no data was found, as you said you've been doing'.

sql%notfound was never reached in case where no record was returned.
The exception was raised in the select into statement.
That's the default Oracle behaviour.

select into statement always raise ORA-01403 error
if no record was returned and no value set into variable.

You'll have to handle the exception.

Here is the code that will work the same logic, without any problem:

[color=red]declare
tempdate date;
begin
select trdt into tempdate from inv_trans;
exception
when no_data_found then
null;
end;
/ [/color]

2007-12-07T10:22:00.001-08:00

Using Trigger And Procedure Together

Table Name: INSTRUCTOR

INSTRUCTOR_ID INSTRUCTOR_NAME SALARY COMMISSION MENTOR_ID DATE_HIRE

------------- --------------- --------- ---------- --------- ---------

700 WAYNE 4500 300 16-MAY-81
628 MONROE 3000 700 16-JUN-84
790 NEWMAN 3100 300 700 16-DEC-82
795 BOGART 3200 700 16-DEC-83
515 SHELLEY 3500 200 700 20-JAN-91
222 CAINE 5500 350 02-NOV-76
978 STEEL 5000 250 222 16-JAN-80
560 LAUREL 5200 200 978 16-DEC-83
835 SPARKS 4000 200 978 16-DEC-84
243 TUCKER 2000 835 18-DEC-90
263 JOHNSON 4000 835 18-JUL-92
453 LODGE 2500 100 835 14-SEP-88

Table Name: AUTH
INSTRUCTOR_ID COURSE_ID AUTH_DATE

------------- --------- ---------

222 315 14-MAR-90
222 415 13-JAN-92
978 815 14-MAR-89
700 315 14-FEB-91
835 515 12-MAR-92
515 915 22-APR-91
263 215 14-JUN-92
263 315 14-FEB-92
453 715 14-AUG-93
790 315 14-AUG-91
560 915 14-AUG-91
453 615 14-JUL-93
628 315 13-JUL-91

My Question requirement is:

1) From the INSTRUCTOR table, if the salary of that instructor >= 3750, then he/she can insert new record on the AUTH table, BUT if salary <>

SO, I SOLVE this question using the method below, and can some one help me to check any logic error on it.

cause even i insert a new record where the instructor salary already <>

***************************************************************

INSERT INTO INSTRUCTOR(INSTRUCTOR_ID,
INSTRUCTOR_NAME,
SALARY,
COMMISSION,
MENTOR_ID,
DATE_HIRED)
VALUES (100, 'JOSH', 2000, 500, 700, '11-AUG-05');

INSERT INTO AUTH(INSTRUCTOR_ID,
COURSE_ID,
AUTH_DATE)
VALUES(100, 515, '11-AUG-05');

******************************************************
=============================================================================

CREATE OR REPLACE TRIGGER CHECK_SALARY_TR
BEFORE INSERT
ON AUTH
DECLARE
SALARY INSTRUCTOR.SALARY%TYPE;
BEGIN
IF (SALARY lessthan= 3750)THEN
RAISE_APPLICATION_ERROR(-20101,'THANKS, YOU HAVE AN AUTHORIZATION TO OFFER COURSES');
ELSIF (SALARY lessthan 3750) THEN
RAISE_APPLICATION_ERROR(-20102,'SORRY, YOU DO NOT HAVE AUTHORIZATION TO OFFER COURSES');
END IF;
END;

=============================================================================

CREATE OR REPLACE PROCEDURE INSERT_AUTH_PROC(P_INSTRUCTOR_ID AUTH.INSTRUCTOR_ID%TYPE,
P_COURSE_ID AUTH.COURSE_ID%TYPE,
P_AUTH_DATE AUTH.AUTH_DATE%TYPE ) IS
BEGIN
INSERT INTO AUTH(INSTRUCTOR_ID, COURSE_ID, AUTH_DATE)
VALUES (P_INSTRUCTOR_ID, P_COURSE_ID, P_AUTH_DATE);
END INSERT_AUTH_PROC;

=============================================================================

--------------------------------------->

In your trigger you have defined a variable SALARY and used it in the IF..ELSIF. Since the value of SALARY is NULL (you haven't initialized it) both the IF and ELSIF will evaluate to FALSE. You need to populate the variable, probably by querying the INSTRUCTOR table with the ID of the instructor from the insert. Use the NEW record supplied by the trigger (:new.instructor_id).

Also, I don't think you want to use the RAISE_APPLICATION_ERROR if the insert is allowed. Doing so will cause the insert to fail. Perhaps you are just doing that for testing.

--------------------------------------->

=============================================================================

CREATE OR REPLACE TRIGGER CHECK_SALARY_TR
BEFORE INSERT
ON AUTH
DECLARE
SALARY INSTRUCTOR.SALARY%TYPE;
BEGIN
IF (SALARY < 3750) THEN
RAISE_APPLICATION_ERROR(-20101,'SORRY, YOU DO NOT HAVE AUTHORIZATION TO OFFER COURSES');
END IF;
END;

=============================================================================

How to modify the trigger coding so that the SALARY can refer to the most currently inserted new row?

Example: I insert new record into the INSTRUCTOR table where the new instructor have the SALARY (lessthan) 3750, then when he want to insert himself into the AUTH table, he can't cause the SALARY (lessthan) 3750 unless his salary change to more that 3750?

It can run smoothly if i change the ......

SALARY INSTRUCTOR.SALARY%TYPE;

TO BECOME

SALARY INSTRUCTOR.SALARY%TYPE:=2000; (this will not allow me to insert into AUTH table)
SALARY INSTRUCTOR.SALARY%TYPE:=5000; (this will allow me to insert into AUTH table)

BUT I don't want it to be fix value! I want the trigger to check the SALARY on the new instructor that i just inserted.

PROBLEM: I don't really know how to use the :new and :old like what you told me, if not mistaken, the :new should be only available on "BEFORE INSERT or update" right.

--------------------------------------->

You're correct, the NEW and OLD are only available to row triggers. Sorry about that, didn't catch that your trigger was not a row trigger. Could you make this a row trigger instead? Or, add the validation code to the procedure, prior to the insert.

--------------------------------------->

Thanks again for your help one more time. I try to implement the technique that you suggest and now I done perfectly. Thanks.

Below is the code that run successfully. with make sure of :NEW command

=============================================================================

CREATE OR REPLACE TRIGGER CHECK_SALARY_TR
BEFORE INSERT
ON AUTH
FOR EACH ROW
DECLARE
V_SALARY INSTRUCTOR.SALARY%TYPE;
BEGIN
SELECT SALARY
INTO V_SALARY
FROM INSTRUCTOR
WHERE INSTRUCTOR_ID = :NEW.INSTRUCTOR_ID;
IF (V_SALARY lessthanusethatsysmbol 3750) THEN
RAISE_APPLICATION_ERROR(-20101,'SORRY, YOU DO NOT HAVE AUTHORIZATION TO OFFER COURSES');
END IF;
END;

=============================================================================

2007-12-07T10:15:00.000-08:00

What is PL/SQL tables?

What is PL/SQL tables. When and why they are used.

PL/Sql table is a virtual array(single array) table defined during runtime.These tables help u in storing,manipulating in a blocks thus removing the need to connect to the database for information except for the first time and later can be updated into the database.

Assume I have a table emp

emp
------
empno
empname
empsal
saldate
empcomm

Now I would like to give the employees an increment/commission and will create a procedure with some logic.

Create procedure empinc
as
type v_empsal is table of emp%rowtype;
cursor as my_empsal is select * from emp
where empsal.empno=emp.empno;
begin
open my_emp
fetch my_emp into v_empsal;
exit when my_emp%notfound;

for i in v_empsal.first..v_empsal.last
loop
if v_empsal is not null then
v_empsal(i).empcomm:=v_empsal(i).empsal/100*10;
v_empsal(i).empsal= v_empsal(i).empsal
+v_emp(i).empcomm;
end if;
end loop;

begin
forall k in v_empsal.first..v_empsal.last
insert into empsal vlaues v_empsal(k)
end;

end;

In the above example as you see I didn't need to use any extra valrables nor define any data types. Both of these were as it was defined in the database and the array takes care of storing the values and insertion of the details in to the table was done by the decalring the table type and the array with in it

Anyway you are using the cursor variable, so you can loop across the records and process one by one. The use of table type variable is not significant.

When you open the cursor, during first fetch use bulk collect and take the records into table variables declared. After this close the cursot. YOU NEED NOT LOOP across the cursor.

Table type variable is very useful when you need to select in the array.

Declare
type empno_list is table of emp.empno%;
empno_list_var empno_list ;
begin
select empno BULK COLLECT into empno_list_var from
scott.emp where empno > 100 ;

for v_empno in empno_list_var.first ..
empno_list_var.last
loop
...
;;;
---
end loop;

end;

2007-12-07T09:40:00.000-08:00

Accessing Oracle Database

I have a oracle database out on my company's network which I need to access.

1. I have the IP address of where this oracle database is located.

2. I have the name of the database

3. I have username and password

How do I connect to this database. What all do I need to set up the enviroment on my computer to access this database.

You will need a tnsnames file. And you will some tool to connect with, either sqlplus, Toad or other similar products. In your tnsnames file on your machine there must be an entry for that database and ip address or host name. Then you can use one of the tools such as Toad or sqlplus to connect to the db. Login with your user name and password and try to connect in one of those tools or something similar. You can also tnsping the database to make sure you can reach it. on the command line in dos, c:\tnsping ip_address. Then make sure it gives you a return so that you know the db is there. This is just to start out to see if you can reach it.

To access oracle database you need to installed oracle client software i.e. sqlplus or another tool like TOAD, SQL Navigator etc.

SQL*PLUS is the oracle client software ship along with u r oracle Enterprice cd. While Installing oracle select Oracle client it will install sql*plus & sql*net.

After Successful Installation Modify your tnsnames.ora file (resides in ORACLE_HOME/network/adin

dir).

db_alias = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <db> )(PORT = <db>)) ) (CONNECT_DATA = (SERVICE_NAME = <db>) ) )

db_alias =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ..db server ip address.. )(PORT = ..db server port...))
)
(CONNECT_DATA =
(SERVICE_NAME = ...db server sid...)
)
)

After these setting use tnsping on command prompt :
tnsping ..db server sid..

If tnsping reply correctly
use sqlplus to connect to database
sqlplus dbuser/dbpassword@db_alias

2007-12-07T09:33:00.000-08:00

About Referential Integrity

First let me brief about the RFforeign key concept.

If you are thorough on this you will not have this doubt.So letz get going:

Referential integrity is about ensuring that relationships between rows in related tables are valid and that you do not accidentally delete or change related data.

When referential integrity is enforced in an Oracle database, you must observe the following rules:

1.You cannot enter a value in the foreign key column of the related table if that value does not exist in the primary key of the related table. However, you can enter a null in the foreign key column. For example, you cannot indicate that a job is assigned to an employee who is not included in the EMPLOYEE table, but you can indicate that an employee has no assigned job by entering a null in the JOB_ID column of the EMPLOYEE table.

2. You cannot delete a row from a primary key table if rows matching it exist in a related table. For example, you cannot delete a row from the JOBS table if there are employees assigned to the job represented by that row in the EMPLOYEE table. However, if cascading deletes are enabled, you can delete a primary key row; all matching rows in related tables are also deleted.

3. You cannot change a primary key value in the primary key table if that row has related rows. For example, you cannot delete an employee from the EMPLOYEE table if that employee is assigned to a job in the JOBS table.

Oracle supports only two kinds of referential integrity:

1. Enabling referential integrity to check values in related tables when you enter data. If the data value is not allowed (as determined by the rules above), the data entry fails and the data is not added to the database.

2. Cascading deletes.

By now you should have got the picture!!!

SOME MORE HINTS:

DELETE removes one or more records in a table, checking referential constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) there will be no problems. TRUNCATE removes ALL records in a table. It does not execute any triggers.Also, it only checks for the existance (and status) of another foreign key pointing to the table. If one exists and is enabled, then you will get your error. This is true even if you do the child tables first.

You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.

Final XPLANATION:

QUESTION:Suppose I have the tables:Father(A, B, C),Son(X,Y)
Father.Primary Key (A)
Son.Foreign Key (X) refers Father.primarykey(A)
This implies that "Son" table has child records of the parent table "Father".
Now I'm trying to delete a row using the query:
delete from Father where A = 123;
jst as you were asking!!

So now what happens?

ANS:Primary and foreign keys are defined the parent/child relationship, you CANNOT delete a record from parent table until it has record in child table.If your parent table R1 has any child record oracle does not allow to delete this parent record because oracle RESPECTS the parent/child relationship.
:+)

For your future reference you should define keyword ON DELETE CASCADE with your primary key, this key implicitly delete the child records when you try to delete parent record.

But for now the best way is to delete the associative child record of this primary key from the child table then delete it from parent table.

for more IDEAS ::::Also look at how to disable/enable constraints!!!!

A bit too detailed:but hope this helped you.

2007-11-26T23:59:00.001-08:00

List prerequisites to connect to oracle with a administrator previleges

1.User's O.S account has O.S previleges.

2.The user is granted the SYSDBA or SYSOPER previleges and database user the password files to authenticate database administration.

3.The database has a passsword for the internal login.

oracle server
/ \
Oracle database oracle instance
/ \
Physical database structure Logical database structure

(An oracle database's physical structure is datermined by the O.S files that constitute database)

Each oracle database is made of 3 types of files:

- one or more datafile
- 2 or more redo log files
- 1 or more control files

Oracle Instance
System global area(SGA) is an area of memory used for database admin. shared by the database servers.

The combinamtion of the background process and memory buffers is called an instance.

Oracle Instance
/ \
User process Oracle process
/ \
server process background process

A user process executes the code of an application program or an oracle tool.

Oracle process are server process and background process.

If the user and server process are on differrent computers of a network, if the user process connect to hares server process thro' dispatcher process, the user process and server process communicate thro' on using SQL* NET.

* Normally tablespace is online than offline.

b)Schemas and Schema objects

* Schema is collection objects

* Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures as tables,views,sequence.

(There is no relationship between a tablespace and schema).

1. table

2.view - A view can also be thought of as a stored query.

3.sequences

4.program unit

5.synonyms

6.Index,cluster and hash clusters

7.database link

Datablocks, Extents and segments

*one datablock corresponds to a specific number of bytes of physical database space on disk.

*An extent is a specific number of continumous datablock obtained in a single allocation used to store a specific.

2007-11-26T23:58:00.001-08:00

Problem Running 'ckrorole.sql' in Sqlplus

I've recently installed my Oracle 9i database and developers suite and I've created the repository owner and I've created my tablespaces to use in oracle 9i designer but I'm having problems granting roles to the repository owner.

I can login to sqlplus.exe but I can't seem to run the 'ckrorole.sql' file which as far as I know is used to grant roles to the repository owner. I keep getting the following message whenever I try to run it.

By the way, I'm using Windows XP.

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 22 19:05:07 2004

Enter user-name: sys@orcl as sysdba
Enter password:

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> @orahome1\repadm61\utl\ckrorole.sql
SP2-0310: unable to open file "orahome1\repadm61\utl\ckrorole.sql"
SQL>

The following happens whenever I try to run it the traditional way.

SQL> run d:\orahome1\repadm61\ckrorole.sql
SP2-0103: Nothing in SQL buffer to run.
SQL>

What do I need to do to get the 'ckrorole.sql' script to run?

Make sure this: orahome1\repadm61\utl\ is a valid directory -- try specifying the FULL path.

I did check. It is a valid directory. I found the file by searching for it in the windows XP search companion. The full path for that file is 'D:\OraHome1\REPADM61\UTL\CKROROLE.SQL'. This is exactly how windows recognises this file. ('D:\' being the 2nd partition of my 40 Gb Hard drive, 'C:\' being the 1st partition.)

What do you mean by 'full path' exactly? Add the 'd:\' onto the statement?

I found the problem. The code wasn't typed in properly. This is the correct code:

@ "D:\OraHome1\REPADM61\UTL\CKROROLE.SQL"

(Where 'D:' is the letter of the drive or hard disk partition that the database is installed on)

2007-11-26T23:56:00.000-08:00

Need to write one PL/SQL procedure to housekeep a table

I'm running Oracle 8.1.7 on Windows 2000 Server.

I need to write one PLSQL procedure to housekeep a table, on daily basis.

There are 2 methods i can think of:
1. Use DBMS_SQL
...............
stmt_char := 'DELETE FROM TABLE_A WHERE TRUNC(DT) <= TRUNC(SYSDATE) - 3'; -- always keep 3 days
................
................

2. Use cursor
.............
CURSOR lv_delete_cur IS
SELECT column_A FROM TABLE_A -- where column_A is primary key
WHERE TRUNC(dt) <= TRUNC(SYSDATE) - 3;
.............
.............

..............

My questions are:
(a) Between approach 1 and 2, which is faster/better performance?
(b) In terms of performance, is there any difference if i modify to commit every 500 or 5000 records?
(c) Actually, currently i m using approach 1, which is considerably very slow!!!
[needs 5 - 7 hours to delete approx. 250000 records from table consisting approx. 800000 records.]

You can go for Dynamic SQL also and probably it helps you to improve the performance.

The performance of native dynamic SQL in PL/SQL is comparable to the performance of static SQL because the PL/SQL interpreter has built-in support for native dynamic SQL. Therefore, the performance of programs that use native dynamic SQL is much better than that of programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent statements that use the DBMS_SQL package. Of course, your performance gains may vary depending on your application.

The DBMS_SQL package is based on a procedural API and, as a result, incurs high procedure call and data copy overhead. For example, every time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for later use during execution. Similarly, every time you execute a fetch, first the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead resulting from data copying. In contrast, native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.

for e.g
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table;';
END;
/

Please let me know if any issues

I want to share a few ideas..
first of all you have said:

I need to write one PLSQL procedure to housekeep a table, on daily basis

here ,the question is why PLSQL procedure...as Tom Kyte would say often,we should use sql instead of pl/sql whereever possible...now here i think you can definately use simple sql statement like this.

DELETE FROM TABLE_A WHERE TRUNC(DT) <= TRUNC(SYSDATE) - 3

the thing to watch here is whether any indexes are being used or not....is there an index on the column DT?
pl post the execution plan of the query.
they might not get used i guess and you may need function based index as you are using the function trunc on the column DT.
alternately ,just for experiment try the following sql statement (pl. rollback the results ,as this is just experiment)
(pl. index the DT column if not already indexed)
DELETE FROM TABLE_A WHERE DT <= SYSDATE - 4

see if there is some performance gain.

--------------------------------------------------------------------------------------------------------------------------------
Basically I do agree with your saying, but if i use dynamic sql, how do i perform commit for every 1000 records (deleted), like wat i have explained in my first post?
--------------------------------------------------------------------------------------------------------------------------------
Perhaps i have tried most with and without TRUNC function, but the performance seems almost the same. Fyi, the DT column is indexed. But i guess u should not use:

DELETE FROM TABLE_A WHERE DT <= SYSDATE - 4

bcoz it is not accurate. If you really want to get rid off the TRUNC, i supposed you should use:

lv_sysdate_date := TRUNC(SYSDATE);

DELETE FROM TABLE_A WHERE DT >= lv_sysdate_date - 3 AND DT <>

Please correct me, if i am wrong.

And to your question why am i using PL/SQL instead of SQL statement. This is bcoz:
(a) Actually the "3" as in "TRUNC(dt) <= TRUNC(SYSDATE) - 3", i need to query from another table. It is not hardcoded. (b) I need to commit after 1000 records have been deleted, due to rollback segment issue.

--------------------------------------------------------------------------------------------------------------------------------

Can u pl. post the execution plan of the query? what i would like to see is ,whether the index is being used or not..in fact i think that the index may not be used if this is not a function based index..so could u pl. post the execution plan of the query.for commiting,if i understand correctly,it really does not matter whether you commit after 1000 records or 10000 records..so don't expect any big gain in performance from that area...

Thats what i feel...key factor is ensuring & verifying that index is being used.also try NOT to use the index and see performance..and explain plan and post it and try TO USE the index and see performace and post the explain plan..i would like to compare the two plans..(PL. ROLLBACK if u are just experimenting)

2007-11-26T23:51:00.000-08:00

Use user defined functions in SQL statements

Can we use user definbed functions in SQL statements, as we can use in built oracle function like date etc..?

Yeah.Of course provided there is no dml in your function

Thanks for reply, pls tell you can't we use functions with DML in it. What will hapen if it conatins dml in it.

We can use function which has only select statments in it, but it should not contain other DML statements like INSERT,UPDATE and DELETE oracle doesnot allow because it might get conflicts

Add A Function To User Defined Object

DATABASE: Oralce 9i
TOOL : OEM 9.2.0.1.0
Can I add a Function to a User Defined Object Type.
The User Defined Object Has Dependencies.
I tried adding the function using OEM but an error was reported,says, cannot alter the definition.
I tried Oracle Change Manager,but that does not display User defined Types.

This is well explained in the Oracle manuals. You cannot simple modify the already existing object type. In order to modify object attributes or methods, an object type must be dropped and re-created. An attempt to drop or replace a type with type or table dependents will result in an ORA-02303. Here is the manual part:

QUOTE:
The object types can be instantiated as types (nested tables or VARRAYs) or object tables. If data preservation is required, data depending on the type must be manually moved into temporary tables.
Per the SQL Reference guide, the DROP TYPE FORCE option is not recommended since this operation is not recoverable and could cause the data in the dependent tables to become inaccessible.

The following 3 queries can be run to determine dependencies:

-- Find nested tables
select owner, parent_table_name, parent_table_column
from dba_nested_tables
where (table_type_owner, table_type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = ''
and elem_type_name = '');

-- Find VARRAYs
select owner, parent_table_name, parent_table_column
from dba_varrays
where (type_owner, type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = ''
and elem_type_name = 'typename');

-- Find object tables
select owner, table_name
from dba_object_tables
where table_type_owner = 'typeOwner'
and table_type = 'typeName.'
and nested = 'NO';

So first find the object dependencies on object types, make sure you have a means to backup the dependent object (nested tables, varrays, tables), drop the dependent object (nested tables, varrays, tables), modify the object type, re-create (restore) the object (nested tables, varrays, tables).

Hope that works for you - no OEM involved, so it would be a good practice.

2007-11-26T23:49:00.000-08:00

Update statements in Query

I have one query,
Update the salary of the employees who has finished one year service with the company by 10% and who has service less than 1 yr with the company by only 5%.

Thesre are basically two Update statements in your Query:

1. Update the salary of the employees who has finished one year service with the company by 10%

Ans:
update table employee set salary = (salary + 0.10*salary) where (to_number(to_char(sysdate,'YYYY'))
- to_number(to_char(hiredate,'YYYY'))) > 1
/

2. and who has service less than 1 yr with the company by only 5%

Ans:
Update table employee set salary = (salary + 0.05*salary) where (to_number(to_char(sysdate,'YYYY'))
- to_number(to_char(hiredate,'YYYY'))) <>
/

I think this is what you needed else revert me back.

..............

Use Decode or Case to solve this problem

.............

Give this inside a loop
{
UPDATE empincrement
SET salincrement =
(
SELECT decode(doj - sysdate >=1 ,sal*.10, sal*.05)
FROM emp
);
}
end loop

This is the way I have tried to solve this, but in the below part
doj - sysdate >=1 , its giving an error like right paranthesis missing.

...........

Use this query in your loop.

update emp
set sal=
(SELECT case when (hiredate - sysdate) >=1 then
sal*.10 else sal*.05 end
FROM emp)

2007-11-26T23:40:00.000-08:00

Why this Query Returns null

Why this query returns null? Why rownum does not work with equality?

select * from emp where rownum = 10;

SQL> select rowid,rownum from emp ;
ROWID ROWNUM
------------------ ----------
AAAHcIAABAAAMuiAAA 1
AAAHcIAABAAAMuiAAB 2
AAAHcIAABAAAMuiAAC 3
AAAHcIAABAAAMuiAAD 4
AAAHcIAABAAAMuiAAE 5
AAAHcIAABAAAMuiAAF 6
AAAHcIAABAAAMuiAAG 7
AAAHcIAABAAAMuiAAH 8
AAAHcIAABAAAMuiAAI 9
AAAHcIAABAAAMuiAAJ 10
AAAHcIAABAAAMuiAAK ! 11

ROWID ROWNUM
------------------ ----------
AAAHcIAABAAAMuiAAL 12
AAAHcIAABAAAMuiAAM 13
AAAHcIAABAAAMuiAAN 14
AAAHcIAABAAAMuiAAO 15
AAAHcIAABAAAMuiAAP 16
16 rows selected.

SQL> select * from emp where rowid='AAAHcIAABAAAMuiAAJ' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- --------------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

What I inquire is only this rowid is changed to rownumber for the userfriendly environment..and in order to retrieve it we should use only this rowid..This is what i feel and i may be right or wrong.just want to share what i feel.

.............

Rownum is not stored in database, it is a pseudocolumn, and generated only when we try to retrieve data from the tables. oracle assigns the sequential number in the same order it retrieves the records from the table.

Now if you use "<" then oracle can find out the records rownum and returns the records. while in case of equality, its not possible to identify a record order in which it is going to be returned.

.........

Its wrong query

select * from emp where rownum = 10;

Right query is : select * from emp where (cloumn name) = 10;

...............

To remove duplicate rows using rownum, do the following:

select rownum, ...column name.. from ...table name..;

you will get the list of records with unique rownum. Even the duplicate records will have different rownum.

Now, you can use the DELETE statement to remove one of the duplicate record.

delete ....table name.. where rownum= X; ( where X is the rownum of the duplicate record)

Hope you have understood!!!

2007-11-26T23:36:00.001-08:00

Query about Data Storage and Retrieval

Is there any database parameter that we can set, which controls the way data is stored and retrieved for display purpose.

Or to put it other way round,

When we retrieve the data using select * statement, is the data displayed always in the chronological order ?

Context here is:

We have a table that contains past 2 months data (say 200 rows - 100 rows loaded on 1st June as May data, 100 for June data loaded on 1st July). Using some DB feature/parameter can we seprate out the recently inserted rows i.e. June data ?

PS: there is no date-time stamp related column in the row.

Just a blind shot:

If I do select * from table, and copy the last 100 of the displayed records, will it always serve my objective?

When you want to querry data from a table you have to know the structure of the table(i.e the columns in the table) these are what you need to restrict your select.

For example if you have a table called urtable has column like

name
address
salary
date_loaded

You can query for data loaded in june this way:

select name,address,salary,date_loaded from urtable
where date_loaded like '%06-01-2005%'

You can play around with several this query to achieve your desired result.

2007-11-26T23:34:00.000-08:00

Finding How Much Data Has Been Filled

I would like to know how can one identify the current location that oracle is writing for transaction ie. extent location has reached the maximum allocated for it within the tablespace.

In plain words finding if the extents have reached the maxextent?

----->
You can use the view dba_extents and dba_tables for that

CODE

select b.table_name,a.used,b.max_extents-a.used remaining from
(select segment_name,count(extent_id) used from dba_extents group by segment_name) a,
(select table_name,max_extents from dba_tables) b
where a.segment_name=b.table_name

----->
Are you sure extent_id identifies the used extents?

I thought its just an identification

----->
extent_id identifies extents that are allocated to the table. so max_extents-count(extend_id) would give the number of extents yet to be allocated.

That is what you wanted to know? isn't it?

2007-11-26T23:30:00.000-08:00

Find How Much Time Left For Query Completion

Need to find out how long will a query take to run or when I am running the query where can I check to see that X% of my query is complete.

I know there is some place in Oracle Enterprise manager or something like that which shows a progress bar once the query is started, but I dont exactly recollect it can somebody help me with this..

Also when do we run analyze plan estimate statistics and how does this help if I have to run a complex query. does estimating statistics on a table increase the query performance if so how ?

You can use this query to find out the information you want to know:

CODE

REM Displays the long running operations

SET LINESIZE 200

COLUMN operation FORMAT a15
COLUMN username FORMAT a15
COLUMN object FORMAT a25

SELECT a.sid,
a.serial#,
b.username ,
opname OPERATION,
target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)",
TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a,
v$session b
WHERE a.sid = b.sid AND
b.username not IN ('SYS', 'SYSTEM') AND
totalwork > 0
ORDER BY elapsed_seconds;

Needed some clrafication regrding this

"when do we run analyze plan estimate statistics and how does this help if I have to run a complex query. Does estimating statistics on a table increase the query performance if so how ?"

Your last post contains three questions:

1>when I am running the query where can I check to see that X% of my query is complete

2>predict the time to answer of a query

3>estimating statistics on a table increase the query performance if so how ?

My point of view is

1>Answers to the first question have given above..

2>The second and the third questions implie to understand what is a CBO (cost based optimizer) and a RBO (rule based optimizer). Are you familiar with that? The ROB might not be used any more by oracle (it is an another story). You cannot predict the time of answer of a sql request. There are too many factors.

3>Regarding the third question, the Analyze is useful if the sql request is analyzed by the COB engine. And it should be.

Yes, Analyzing the table allows oracle to know the volume and the repartition of the tables. Therefore it performs the appropriate tasks to compute the data together. But sometimes some complex sql requests are not really well written. In that case you need to analyze the plan table.