Shulou(Shulou.com)06/01 Report--

1. Objects frequently used by SQL include tables, views, synonyms, indexes and views.

Tables (table) store data in rows and columns.

A view is a stored SELECT statement that can be referenced like a table.

Synonyms (synonym) are aliases for tables (or views).

An index (index) provides a pointer to the exact location of a row in a table, reducing the number of visits to rows in the table.

A sequence is a structure that generates unique numbers.

2. Naming rules for schema objects

(1) it cannot exceed 30 characters. Characters can be letters, numbers, underscores, dollar signs, and zigzag symbols. (database link can be 128 characters long)

(2) it must start with the letter Avalz, and lowercase letters will be automatically converted to uppercase letters.

(3) the reserved word cannot be used as the name of the object.

(4) enclosing the name in "(double quotation marks), all the above rules (except for length) can be broken.

SQL > show user

USER is "PEENBOO"

SQL > create tablelower (C1 date)

Table created.

SQL > create table "lower" (col1 varchar2 (4))

Table created.

SQL > selecttable_name from user_tables where lower (table_name) = 'lower'

TABLE_NAME

-

Lower

LOWER

3. Object namespace

Objects that share the same namespace

Table

View

Sequence

Private synonym

Independent process

Independent stored procedure

Program package

Materialized view

User-defined type

Object types with their own namespaces

Indexes

Constraint

Cluster

Database trigger

Private database link

Dimension

Note: tables, synonyms, and views cannot have the same name in the same schema.

DDL_P1: determine which objects the session can access

SQL > set pagesize50

SQL > set linesize200

SQL > selectobject_type,count (object_type) from dba_objects

2 group by object_type order by object_type

OBJECT_TYPE COUNT (OBJECT_TYPE)

-

CLUSTER 10

CONSUMER GROUP 25

CONTEXT 7

DESTINATION 2

DIRECTORY 4

EDITION 1

EVALUATIONCONTEXT 12

FUNCTION 309

INDEX 4895

INDEX PARTITION 118

INDEXTYPE 9

JAVA CLASS 28968

JAVA DATA 309

JAVA RESOURCE 940

JAVA SOURCE 2

JOB 14

JOB CLASS 14

LIBRARY 193

LOB 1012

LOB PARTITION 1

MATERIALIZEDVIEW 1

OPERATOR 55

PACKAGE 1333

PACKAGE BODY 1272

PROCEDURE 171

PROGRAM 19

QUEUE 35

RESOURCE PLAN 10

RULE 1

RULE SET 19

SCHEDULE 3

SCHEDULER GROUP 4

SEQUENCE 223

SYNONYM 33977

TABLE 2885

TABLE PARTITION 97

TABLESUBPARTITION 32

TRIGGER 626

TYPE 2909

TYPE BODY 243

UNDEFINED 11

VIEW 5206

WINDOW 9

XML SCHEMA 53

44 rows selected.

SQL > selectobject_type,count (*) from user_objects group by object_type

No rows selected

SQL > selectobject_type, count (*) from all_objects group by object_type

OBJECT_TYPE COUNT (*)

-

EDITION 1

CONSUMER GROUP 2

SEQUENCE 13

SCHEDULE 3

PROCEDURE 38

OPERATOR 55

DESTINATION 2

WINDOW 9

SCHEDULER GROUP 4

PACKAGE 401

PROGRAM 11

JAVA RESOURCE 938

XML SCHEMA 53

JOB CLASS 2

TABLE 122

SYNONYM 33888

VIEW 1936

FUNCTION 211

JAVA CLASS 28851

INDEXTYPE 9

TYPE 1543

EVALUATIONCONTEXT 1

22 rows selected.

SQL > selectdistinct owner from all_objects

OWNER

-

MDSYS

PUBLIC

CTXSYS

OLAPSYS

SYSTEM

EXFSYS

APEX_030200

ORDSYS

ORDPLUGINS

DBSNMP

XDB

ORDDATA

SYS

WMSYS

14 rows selected.

4. Data type

4.1 for alphanumeric data

Data type

Meaning

Size

VARCHAR2

Variable length character data

1B to 4KB

NVARCHAR2

Variable length character data (save national language character set (one of the Unicode character sets))

1B to 4KB

CHAR

Fixed length character data

1B to 2KB

4.2 applicable digital data

Data type

Meaning

Size

NUMBER

Precision and decimal places can be specified

Precision 1: 38, decimal-84: 127

FLOAT

Floating point number

Precision 126 bit binary, 38 bit decimal

INTEGER

Integer type

Zero relative to NUMBER decimal place

4.3 date and time Typ

Data type

Meaning

Size (remarks)

DATE

Including the century, month, day, hour, minute and second

Length is 0 or 7 bytes

TIMESTAMP

Similar to date, but with a precision of 9 bits for seconds and 6 bits by default

Length is 0 or 11 bytes

TIMESTAMP WITH TIMEZONE

Same as TIMESTAMP, but with a specified time zone

The length can reach 13 bytes.

TIMESTAMP WITH LOCAL TIMEZONE

Like TIMESTAMP, saving is normalized to the database time zone

INTERVAL YEAR TO MONTH

Record the time interval between two TIMESTAMP or DATE

In years and months

INTERVAL DAY TO SECOND

Record the time interval between two TIMESTAMP or DATE

In days and seconds

4.4 large object data types

Data type

Meaning

Size

CLOB

There is no limit to the size

4GB*block number

NCLOB

Like CLOB, save the national character set (one of the Unicode character sets)

4GB*block number

BFILE

Locator, pointing to the OS file where DB is located

4GB

LONG

Character data of character set in DB

2GB

LONG RAW

Same as LONG (oracle Net can't be converted to binary, all should be converted to BLOB)

2GB

BLOB

Same as CLOB, but Oracle Net cannot perform character set conversion

4GB*block number

4.5 RAW and ROWINSs data types

Data type

Meaning

Size (remarks)

RAW

Variable length binary data

1B to 4KB

ROWID

64 is the cardinality coded value, which is a position pointer to the rows in the table

It's a physical address.

Note: 1 Chinese character storage needs 2 bytes, and 1 English character storage needs 1 byte.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.