CSMN 666 Lecture 4

1
CSMN 666 Lecture 4

• Database objects

2
CSMN 666 Lecture 4

• Two types of tablespaces
• Dictionary managed - tablespaces with info stored
  in data dictionary
• Locally managed info such as extent info is in
  data file header, not data dictionary

3
CSMN 666 Lecture 4

• Create tablespace command shown on p. 34 in
  workbook
• You must do USERS and USER_TEMP today
• The other 3 on p. 34 are in the repository. Check
  the sizes of the other 3. Need to add your
  tables.

4
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1" LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE
  UNLIMITED EXTENT MANAGEMENT LOCAL

5
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• Name should reflect tablespace function

6
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• Keep redo log information on objects in
  tablespace

7
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• Create a data file on disk 04 in this path

8
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• interesting to me because used to have to ask SA
  to create data files. Now can do through dbms.

9
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K
• Make the initial size 20480 k- YOU NEED YOUR OWN
  SIZE HERE!!!!!
• So find contiguous space this big and reserve it
  for this tablespace

10
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K
• REUSE
• If a data file with this name already exists, use
  it again

11
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K
• REUSE
• AUTOEXTEND ON
• If the initial allocation of space fills up,
  automatically give this tablespace more space of
  the size in the next parameter

12
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K
• REUSE
• AUTOEXTEND ON
• NEXT 640K
• If the initial allocation of space fills, give
  this tablespace more space of this size

13
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K REUSE
• AUTOEXTEND ON
• NEXT 640K
• MAXSIZE UNLIMITED
• Dont limit the number of next extents

14
CSMN 666 Lecture 4

• CREATE TABLESPACE "USER_DATA1"
• LOGGING
• DATAFILE '/u04/dbprof/user_data01.dbf'
• SIZE 20480K REUSE
• AUTOEXTEND ON
• NEXT 640K
• MAXSIZE UNLIMITED
• EXTENT MANAGEMENT LOCAL
• Keep info in the file header rather than the data
  dictionary

15
CSMN 666 Lecture 4

• Other parameters are possible
• Can look on web for some examples

16
CSMN 666 Lecture 4

• Place tablespaces on disk so there is minimum
  amount of contention
• Hopefully, we did with our 7-disk architecture

17
CSMN 666 Lecture 4

• Good views DBA_TABLESPACES and DBA_DATA_FILES
• Can do a desc in sqlplus for columns of DBA views

18
CSMN 666 Lecture 4

• Can move tablespace from one disk to another
• can resize a tablespace bigger or smaller

19
CSMN 666 Lecture 4

• Can run a tablespace status script periodically
• Part of monitoring done by DBA

20
CSMN 666 Lecture 4

• Temporary or permanent tablespace
• Temporary can only be used for sort (order by,
  joins, index builds, etc.) operations
• Permanent used to store tables, indexes, etc.

21
CSMN 666 Lecture 4

• Transportable tablespaces can move a tablespace
  from one database to another
• Eg. Production to dev or test
• So, can plug and unplug tablespaces into databases

22
CSMN 666 Lecture 4

• Be careful with tablespaces and moving them
• Need to keep tablespaces and files synchronized

23
CSMN 666 Lecture 4

• Document your database
• Keep a hard copy of objects and where placed
  based on reports from views
• Might need for disaster recovery

24
CSMN 666 Lecture 4

• Revisit DDL for tablespaces and get ready to
  execute them
• Refer to p. 34 in workbook
• So, need 5 tablespaces shown there

25
CSMN 666 Lecture 4

• Also, after tablespaces are created, tidy up
  users we created last week
• So
• Alter users (system and dba) and assign temp and
  default tablespaces refer back to p. 63 in
  workbook

26
CSMN 666 Lecture 4

• Alter user your_ID default tablespace users
  temporary tablespace temp_user

27
CSMN 666 Lecture 4

• Drop a tablespace
• Take offline
• Drop tablespace including contents and data files
• Problem another semester dropped tablespace
  without and lost synchronization between data
  dictionary and file - Ooops

28
CSMN 666 Lecture 4

• Creating objects in a database

29
CSMN 666 Lecture 4

• Create tables
• Relational
• Partitioned
• Advanced queue table used to integrate
  message-queuing system in db
• Index-organized store index columns as a table

30
CSMN 666 Lecture 4

• Nested tables store multiple columns of data
  within one column on a table
• Object table create object types
• Temporary tables store things temporarily

31
CSMN 666 Lecture 4

• Create tables
• Load data
• Give access synonyms and grants
• We will do here but not yet

32
CSMN 666 Lecture 4

• Create table parameters are many
• We are just using some

33
CSMN 666 Lecture 4

• Look at our sample DDL starting on p. 36
• Again, note that each table is assigned to a
  tablespace

34
CSMN 666 Lecture 4

• Create complex table various options
• Can look on web for examples

35
CSMN 666 Lecture 4

• Index creation
• Seems simple
• Could become very complex
• We will not use most options

36
CSMN 666 Lecture 4

• DDL for index be sure to assign to a tablespace

37
CSMN 666 Lecture 4

• Note that partitioned tables can be local or
  global

38
CSMN 666 Lecture 4

• Create users
• Two steps create user and grant access

39
CSMN 666 Lecture 4

• create user we will not use the profile option

40
CSMN 666 Lecture 4

• Wessler chap. 5

41
CSMN 666 Lecture 4

• This chapter discusses a lot of the things we did
  last week
• Be sure to take a look
• The only thing to add are the queries on p. 126-7
  they are handy queries to know about

42
CSMN 666 Lecture 4

• W also talks about other users that were created
• In production environment, need to check
• Here we will not worry about

43
CSMN 666 Lecture 4

• Listener.ora
• Listens for incoming requests to connect
• Passes requests to database
• Specifies which port, protocols, and service name
• P. 135

44
CSMN 666 Lecture 4

• Once started not yet can lsnrctl status
• Lsnrctl stop
• Lsnrctl start
• Lsnrctl help