summit office supply schema

Hi Friends,

While practicing oracle forms , you may require summit office supply schema .

Below is the link  for the schema and also the way you have to import the script to the local DB.


Steps:

 1. 1st download the zip file to your D: drive from following URL.

  https://docs.google.com/file/d/0B0ES7eZT7FMZbnNsSWZDVXNyREU/edit?usp=sharing

summit10gr2-128838.zip : From File Menu click download ( Ctrl+S) : From google doc/drive and save it in your D:\drive.
 2.  Extract summit folder to D:\ drive.
 3. Then import the sql script from DMP files from command prompt.

C:\Documents and Settings\mohantyr>d:
D:\>cd summit
D:\Summit>imp userid=rajani/rajani@sys file=summit.dmp full=y
Import: Release 10.1.0.4.2 - Production on Fri Jul 26 19:39:32 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SUMMIT, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SUMMIT's objects into RAJANI
IMP-00008: unrecognized statement in the export file:
. . importing table                            "A"          0 rows imported
. . importing table                        "ACCTS"          5 rows imported
. . importing table                   "ACCT_ADDRS"          5 rows imported
. . importing table                         "BLOB"          0 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
 ", "COMM" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              "
 "       LOGGING NOCOMPRESS"
. . importing table              "CALENDAR_RANGES"         44 rows imported
. . importing table                   "CHESS_SAVE"          0 rows imported
. . importing table        "CHESS_SAVE_BOARDSTATE"          0 rows imported
. . importing table          "CHESS_SAVE_CAPTURED"          0 rows imported
. . importing table            "CHESS_SAVE_PLAYER"          0 rows imported
. . importing table                       "CITIES"        205 rows imported
. . importing table              "COMPANY_SUMMARY"          3 rows imported
. . importing table                     "CONCLASS"          9 rows imported
. . importing table                     "CONLABEL"          6 rows imported
. . importing table                   "CONTAINERS"        169 rows imported
. . importing table                     "CUSTOMER"          9 rows imported
. . importing table                    "CUSTOMERS"         14 rows imported
. . importing table                         "DAYS"          7 rows imported
. . importing table              "DB_STATE_SUMMIT"         18 rows imported
. . importing table                        "DUMMY"          1 rows imported
. . importing table                    "FLRPEOPLE"         65 rows imported
. . importing table                        "FUNDS"          5 rows imported
. . importing table                 "FUND_CONTRIB"         16 rows imported
. . importing table                    "FUND_XACT"         45 rows imported
. . importing table                    "F_EMPCOMP"          2 rows imported
. . importing table                  "F_XACT_TYPE"          7 rows imported
. . importing table               "GAME_SEMAPHORE"          0 rows imported
. . importing table                  "GRID_LABELS"         10 rows imported
. . importing table                       "INDCAT"         11 rows imported
. . importing table                      "INVINFO"          5 rows imported
. . importing table                   "INVREQUEST"         12 rows imported
. . importing table                         "ITEM"         64 rows imported
. . importing table                     "NEWS_LET"         13 rows imported
. . importing table                          "ORD"         21 rows imported
. . importing table                      "ORDPICT"         21 rows imported
. . importing table                       "O_DEPT"          4 rows imported
. . importing table                        "O_EMP"         14 rows imported
. . importing table             "PERF_RES_MONITOR"         51 rows imported
. . importing table                     "PORTDESC"          5 rows imported
. . importing table                    "PORTFOLIO"         39 rows imported
. . importing table                        "PRICE"         17 rows imported
. . importing table                      "PRODUCT"         10 rows imported
. . importing table                     "PRODUCT1"         70 rows imported
. . importing table                     "PRODUCT2"         71 rows imported
. . importing table                   "SALES_FACT"      34815 rows imported
. . importing table                "SALES_REVENUE"         16 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER)  P"
 "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIST"
 "S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                     LOGGING NOCO"
 "MPRESS"
. . importing table                       "STOCKS"        328 rows imported
. . importing table                "STOCK_HISTORY"         11 rows imported
. . importing table                        "STORE"         20 rows imported
. . importing table                   "S_CUSTOMER"         15 rows imported
. . importing table                       "S_DEPT"         12 rows imported
. . importing table                        "S_EMP"         25 rows imported
. . importing table                      "S_IMAGE"         28 rows imported
. . importing table                  "S_INVENTORY"        114 rows imported
. . importing table                       "S_ITEM"      12509 rows imported
. . importing table                 "S_ITEM_AUDIT"    1798249 rows imported
. . importing table                   "S_LONGTEXT"         33 rows imported
. . importing table                        "S_ORD"       3725 rows imported
. . importing table                    "S_PRODUCT"         33 rows imported
. . importing table                     "S_REGION"          5 rows imported
. . importing table                      "S_TITLE"          8 rows imported
. . importing table                  "S_WAREHOUSE"          5 rows imported
. . importing table                        "TIMES"        731 rows imported
. . importing table                       "TRIVIA"         60 rows imported
. . importing table                      "TVTABLE"         25 rows imported
. . importing table                          "USA"         50 rows imported
. . importing table        "VIDEO_CATEGORY_BY_QTR"        640 rows imported
. . importing table                 "VID_SUMM_ALL"          0 rows imported
. . importing table            "VID_SUMM_DEPT_REG"          0 rows imported
. . importing table                  "VID_SUMM_YR"          0 rows imported
. . importing table            "VID_SUM_DEPTREGYR"          0 rows imported
. . importing table                      "VIEWDEF"          3 rows imported
. . importing table                   "VIEWDEFCOL"         17 rows imported
. . importing table                 "WORLD_CITIES"         68 rows imported
About to enable constraints...
Import terminated successfully with warnings.
D:\Summit>

What is difference between View and Materialized View in Oracle ?

Difference between View and Materialized view is one of the popular SQL interview question, much like truncate vs delete

, correlated vs noncorrelated subquery or primary key vs unique key. This is one of the classic question which keeps appearing in SQL interview now and then and you simply can’t afford not to learn about them. Doesn’t matter if you are a programmer, developer or DBA, this SQL questions is common to all. Views allows a level of separation than original table in terms of access rights but it always fetch updated data. Let’s see What is View in database, What is materialized View and difference between view and materialized view in database. 　
What is View in database
Views are logical virtual table created by "select query"
but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. Performance of the view depend upon our select query. If we want to improve the performance of view we should avoid to use join statement in our query or if we need multiple joins between table always try to use index based column for joining as we know index based columns are faster than non index based column. View allow to store definition of the query in the database itself.

What is Materialized View in database Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed
so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

Difference between View vs Materialized View in databaseBased upon on our understanding of View and Materialized View, Let’s see, some short difference between them :
1) 1st difference is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) 2nd difference is that, when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) 3rd difference is that , In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) 4th difference is that , Performance of View is less than Materialized view.
5) 5th difference is that, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
6) 6th and last difference is that , In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL Mostly in application we use views because they are more feasible, only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

That’s all on difference between View and materialized View in database or SQL.