How to view the database's default tablespace, and temporary table space. (Turn)

2010-07-16  来源:本站原创  分类:Database  人气:206 

How to view the database's default tablespace, and temporary table space.

How to view the database's default tablespace, and temporary table space.

Oracle system tables query

Oracle data dictionary dict always belong to the user sys's.

1, the user:

select username from dba_users;

Change password

alter user spgroup identified by spgtest;

2, table space:

select * from dba_data_files;

select * from dba_tablespaces; / / table space

select tablespace_name, sum (bytes), sum (blocks)

from dba_free_space group by tablespace_name; / / free the table space

select * from dba_data_files

where tablespace_name = 'RBS'; / / table space for the corresponding data files

select * from dba_segments

where tablespace_name = 'INDEXS';

3, database objects:

select * from dba_objects;



4, table:

select * from dba_tables;

analyze my_table compute statistics; -> dba_tables after 6

select extent_id, bytes from dba_extents

where segment_name = 'CUSTOMERS' and segment_type = 'TABLE'

order by extent_id; / / table of the extent of information use. segment_type = 'ROLLBACK' View rollback segment space allocation information

Columns of information:

select distinct table_name

from user_tab_columns

where column_name = 'SO_TYPE_ID';

5, Index:

select * from dba_indexes; / / index, including the primary key index

select * from dba_ind_columns; / / index of column

select i.index_name, i.uniqueness, c.column_name

from user_indexes i, user_ind_columns c

where i.index_name = c.index_name

and i.table_name = 'ACC_NBR'; / / connection and usage

6, sequence:

select * from dba_sequences;

7, view:

select * from dba_views;

select * from all_views;

text can be used to view the generated script query

8, cluster:

select * from dba_clusters;

9, a snapshot:

select * from dba_snapshots;

Snapshot, there should be a corresponding partition table space.

10 synonyms:

select * from dba_synonyms

where table_owner = 'SPGROUP';

/ / If owner is PUBLIC, then the synonyms is a public synonym.

if owner is one of users, then the synonyms is a private synonym.

11, the database links:

select * from dba_db_links;

In the construction of the database chain under spbase

create database link dbl_spnew

connect to spnew identified by spnew using 'jhhx';

insert into acc_nbr @ dbl_spnew

select * from acc_nbr where nxx_nbr = '237 'and line_nbr = '8888';

12, the trigger:

select * from dba_trigers;

Stored procedures, functions from dba_objects find.

The text: select text from user_source where name = 'BOOK_SP_EXAMPLE';

Build error: select * from user_errors;

oracle always stored procedures, functions and other software on the SYSTEM tablespace.

13, constraints:

(1) constraints and the associated table can create table or alter table table_name add / drop / modify to create, modify, delete constraints.

May temporarily prohibit constraints, such as:

alter table book_example

disable constraint book_example_1;

alter table book_example

enable constraint book_example_1;

(2) primary key and foreign key constraint is called the table, but not null and unique constraints like constraints are called out. Usually the primary key and foreign key constraints on the naming as a separate list of the following fields, while the column constraint can be defined on the same line of the column, which is more readable.

(3) column constraint can be seen from the table definition, or describe; table or primary key constraints and foreign keys, and dba_cons_columns from dba_constraints check.

select * from user_constraints

where table_name = 'BOOK_EXAMPLE';


from user_constraints

where constraint_type = 'R'

order by table_name;

(4) defines the constraints can be anonymous (the system automatically generates constraint names) and their own definition of constraint name (especially primary keys, foreign keys)

Such as: create table book_example

(Identifier number not null);

create table book_example

(Identifier number constranit book_example_1 not null);

14, rollback segments:

The results of all changes to disk before the rollback segment to restore the firm to maintain all the information required, must occur in the transaction database to determine the appropriate size (DML statements can be rolled back, create, drop, truncate, etc. DDL can not be rolled back).

Rollback segments = the number of concurrent transactions / 4, but can not exceed 50; so that each rollback segment size sufficient to handle a complete transaction;

create rollback segment r05

tablespace rbs;

create rollback segment rbs_cvt

tablespace rbs

storage (initial 1M next 500k);

So that rollback segment online

alter rollback segment r04 online;

With dba_extents, v $ rollback_segs monitor rollback segment size and dynamic growth.

Rollback interval information

select * from dba_extents

where segment_type = 'ROLLBACK' and segment_name = 'RB1';

Rollback segment information, which shows the current rollback bytes bytes

select * from dba_segments

where segment_type = 'ROLLBACK' and segment_name = 'RB1';

Return for the things specified in paragraph

set transaction use rollback segment rbs_cvt

Bytes can be used for rollback segments retraction.

alter rollback segment rbs_cvt shrink;

select bytes, extents, max_extents from dba_segments

where segment_type = 'ROLLBACK' and segment_name = 'RBS_CVT';

Rollback of the current status information:

select * from dba_rollback_segs

where segment_name = 'RB1';

More state than the rollback segment status, rollback segment belongs instance instance_num

Optimizing the value of optimal search

select, s.optsize

from v $ rollname n, v $ rollstat s

where n.usn = s.usn;

Rollback segments in the data

set transaction use rollback segment rb1; / * rollback segment name * /

select, s.writes

from v $ rollname n, v $ rollstat s

where n.usn = s.usn;

When the transaction is completed, re-check $ rollstat, compared writes (rollback segment entry number of bytes) the difference, can determine the size of the transaction.

Query rollback of transaction

column rr heading 'RB Segment' format a18

column us heading 'Username' format a15

column os heading 'Os User' format a10

column te heading 'Terminal' format a10

select rr, nvl (s.username, 'no transaction') us, s.osuser os, s.terminal te

from v $ lock l, v $ session s, v $ rollname r

where l.sid = s.sid (+)

and trunc (l.id1/65536) = R. USN

and l.type = 'TX'

and l.lmode = 6

order by;

15, operation

Query job information

select job, broken, next_date, interval, what from user_jobs;

select job, broken, next_date, interval, what from dba_jobs;

Query operations are running

select * from dba_jobs_running;

Use package exec dbms_job.submit (: v_num, 'a;', sysdate, 'sysdate + (10 / (24 * 60 * 60))') joined the operation. Every 10 seconds

exec dbms_job.submit (: v_num, 'a;', sysdate, 'sysdate + (11 / (24 * 60))') joined the operation. Interval of 11 minutes using the package exec dbms_job.remove (21) to delete 21 jobs.