ITPub博客

首页 > 数据库 > Oracle > Warm up - a normal table in oracle

Warm up - a normal table in oracle

原创 Oracle 作者:lazyzxm 时间:2007-08-28 12:37:08 0 删除 编辑

Table is some kind of thing in a database and oracle is some kind of database, so let's play with some table in oracle.

[@more@]

At the beginning, we create a table.

create table treasure(
id int,
owner varchar2(30),
type varchar2(30),
name varchar2(30)
);

Then we create a index on the table.

create unique index treasure_idx
on treasure (id);

Then we add a primary key constraint on the table using the index we just created.

alter table treasure add constraint treasure_pk
primary key (id)
using index treasure_idx;

Now you know that a primary key requires a index. The index needn't be unique. If you do not supply the index, Oracle will chose one automatically, and if there's no suitable index, Oracle will create one. We can do some test on this but now let's move on.

After the table is created, we can manipulate it with DML, like insert, delete, update. Oracle also offers merge and it's usefull somtimes. We can query it with select. Oracle offers some powerful tools like window functions. For now, let's focus on the fundamentals.

We can insert a result set into a table like this.

insert into treasure
select object_id, owner, object_type, object_name
from all_objects where owner = user;

A result set is a set. We'll get a result set when we select and we can put the result set into a table using the insert statement. ALL_OBJECTS is a dictionary view provided by Oracle. We can get a list of all user objects in a database by query this view provided that we have enough privilege (like SYSDBA). We can also get some system objects' info from this view. Try the following statement.

select * from all_objects where object_name = 'ALL_OBJECTS' ;

Now let's talk about SQL*Plus, with which we can talk to Oracle. We can talk to Oracle in other ways, like JDBC, PL/SQL and more. For now, let's use sqlplus like a DBA.

Assume we've login database via sqlplus. Try this.

column owner format a10
column type format a10
column name format a20
set pagesize 1000

select * from treasure;

Every time we create a thing in Oracle, Oracle'll record some information in it's dictionary. There are many different kind of things in Oracle, just name a few: table, index, sequence, view, directory, synonym, .etc. Dictionary is used to look up for information when needed. Both Oracle and we need dictionary and will look up for it, in different ways.

Now let's look up the dictionary from a user's perspective. What's treasure?

select * from all_tables where table_name = 'TREASURE';

We get a greate deal of information and let's focus on the ABC.

OWNER: FISH
TABLE_NAME: TREASURE
TABLESPACE_NAME: RIVER

The owner of treasure is fish. Fish is treasure's owning schema. Fish is what we're in the database. Now ask ourselves the question: Who am I?

select user from dual;

If we want to know more about FISH, we consult the dictionary.

select * from dba_users where username = 'FISH';
USERNAME FISH
USER_ID 76
PASSWORD
ACCOUNT_STATUS OPEN
LOCK_DATE
EXPIRY_DATE
DEFAULT_TABLESPACE RIVER
TEMPORARY_TABLESPACE TEMP
CREATED 28-AUG-07
PROFILE DEFAULT
INITIAL_RSRC_CONSUMER_GROUP DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME

We have much to say about user and schema, but let's focus on table. Another aspect of table is tablespace. A table does not live in vacuum, it lives in a tablespace. And now we know that treasure is in the river. Want to know more about river, the tablespace contains treasure? Ask the dictionary for help.

select * from dba_tablespaces where tablespace_name = 'RIVER';

When we created the table, we did not specify the schema of the table, we did not specify the tablespace. How oracle know the schema and tablespace the table should have?

A table has many parameters, if not specified when the table was created, oracle will chose the default parameters.

For example, when a table is created without specify the ownering schema, oracle will chose the current user as the table's schema, or if the current session set parameter current_schema, Oracle will chose current_schema as the table's owning schema.

Use the following SQL to get the current user.

select user from dual;

Change the current schema of a session.

alter session set current schema = tiger;

If the destination schema does not exist, oracle will send an error to you.

ERROR:
ORA-01435: user does not exist

We can check the session's current schema with the following select.

select sys_context( 'USERENV', 'SESSION_SCHEMA') from dual;

Now Oracle get the default schema of a table, how does he get the default tablespace? Oracle will use the chosed schema's default tablespace as the table's tablespace.

select default_tablespace from dba_users where username = 'FISH';

We can specify schema and tablespace when the table is created. Like this.

create table fish.rabbit(age int) tablespace river;

To get a full list of parameters a table has, try this.

set long 2000
select dbms_metadata.get_ddl('TABLE', 'TREASURE', 'FISH') from dual;

The output is like this.

CREATE TABLE "FISH"."TREASURE"
( "ID" NUMBER(*,0),
"OWNER" VARCHAR2(30),
"TYPE" VARCHAR2(30),
"NAME" VARCHAR2(30),
CONSTRAINT "TREASURE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "RIVER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "RIVER"

Also try this

select dbms_metadata.get_ddl('INDEX', 'TREASURE_IDX', 'FISH') from dual;

We'll explain every parameters. But now let's have a rest.

From a user perspective, there are different kinds of dictionary. For example, when we want to check the information of a table, we can query all_tables, user_tables, dba_tables. They are different.

ALL_TABLES describes the relational tables accessible to the current user.

DBA_TABLES describes all relational tables in the database.

USER_TABLES describes the relational tables owned by the current user.

Other dictionary start with ALL_, USER_, DBA_ have similar meaning in this perspective. We may get a list of these views using the following SQL.

select table_name from dict where table_name like 'ALL/_%' escape '/';

select table_name from dict where table_name like 'USER/_%' escape '/';

select table_name from dict where table_name like 'DBA/_%' escape '/';

In SQL, underline '_' means match any single character in like, so if we want to match the character '_' instead of matching any character, we need to escape it. We can also use the above method to escape the '%' character.

Here we list some common dictionary views. As a DBA, we at least need to know these views. Please see Oracle® Database Reference for details. In practice, we may use GUI tools (such as sql developer) to do misc things.

About table.
dba_tables
dba_tab_columns
dba_tab_cols
dba_indexes
dba_constraints

About tablespace.
dba_tablespaces
dba_segments
dba_extents

Please see SQL Reference for gramma of DDL and DML.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7745048/viewspace-966463/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: Test of html
请登录后发表评论 登录
全部评论
  • 博文量
    4
  • 访问量
    4351