Oracle basic operation command Learning

---------------------------------------------------------------------------

---- This is andkylee's personal originality. Please repost it with respect to the author's Labor achievements;

---- The original source must be specified for reprinting.
:
Http://blog.csdn.net/andkylee

---


2010-07-28
16:06:16

---- Keyword: Oracle basic command tablespace User table purge analyze

----------------------------------------------------------------------------

Create a tablespace

Create tablespace dultest datafile 'e:/Oracle/oradata/orcl/dultest. dbf'
Size 100 m autoextend on next 100 m maxsize Unlimited
Default storage (

Initial 20 m

Next 20 m

Minextents 1

Maxextents Unlimited

Pctincrease 0

);

The tablespace has only one data file. The initial size of the data file is 100 MB, and the subsequent growth is 100 MB. The default space allocation information of objects in the tablespace dultest is: the initial size of the object is 20 MB, and the subsequent space growth is 20 mb.

View the tablespace information in the System View:

SQL> set linesize 2000 <br/> SQL> Col tablespace_name format A16 <br/> SQL> Col datafile_name format A40 <br/> SQL> select ts. TS #, ts. name tablespace_name, file #, DF. name datafile_name, block_size, blocks, bytes, status, enabled <br/> 2 from V $ tablespace ts, V $ datafile DF <br/> 3 where ts. TS # = DF. TS # and Ts. name = 'dultest '; <br/> TS # tablespace_name file # datafile_name block_size blocks bytes status enabled <br/> ---------- ---------------- ---------- --------- ---------- <br/> 8 dultest 6 E: /Oracle/oradata/orcl/dultest. DBF 8192 25600 209715200 online read write <br/> SQL> <br/>

Delete all objects in a tablespace and its tablespace.

Drop tablespace dultest including contents and datafiles;

Create user dultest and authorize

Create user dultest identified by "DB"
Default tablespace "dultest"
Temporary tablespace temp
Profile default
Account unlock;

SQL> select user_id, username, account_status, lock_date, expiry_date, default_tablespace, temporary_tablespace, <br/> 2 created, profile from dba_users where username = 'test '; <br/> user_id username account_status lock_date defaults default_tablespace created profile <br/> ---------- please wait ---------------- ------------ Please wait -------------- ----------- <br/> 78 test open dultest temp 28-7-10 default <br/>

Authorize role connect and Resoure to the dultest user

Grant connect, resource to dultest;

Grant dultest the permission to create a table

Grant create table to dultest;

SQL> select * From dba_role_privs <br/> 2 where grantee = 'test '; <br/> grantee granted_role ADM def <br/> ------------------------------------------------------ --- <br/> test resource no yes <br/> test connect No Yes

SQL> select * From dba_sys_privs <br/> 2 where grantee = 'test '; <br/> grantee privilege ADM <br/> ---------------------------------------------------------------- --- <br/> test unlimited tablespace no <br/>

Delete user dultest

Drop user dultest cascade;

If the deleted user is online, the following error occurs: <br/> drop user dultest cascade <br/> * <br/> row 1st: <br/> ORA-01940: unable to delete the currently connected user <br/>

Wait until dultest exits and then perform the delete operation.

Merge tablespace fragments

Alter tablespace dultest coalesce;

Query the deleted objects in the recycle bin

Select * From SYS. recyclebin $;

Clear recycle bin objects

Purge table dultest. AAA;

Import the user dultest object exported with exp to user test.

IMP system/db@192.168.2.178 files = C:/dultest_tblspace.dmp fromuser = dultest touser = test ignore = y

View table information

Select O. Owner, T. tablespace_name, O. object_id, O. object_name table_name, O. Created, O. last_ddl_time, O. status,
T. num_rows, T. blocks, T. empty_blocks, T. avg_space, T. avg_row_len,/* space usage information */
T. pct_free, T. pct_used, T. ini_trans, T. max_trans, T. initial_extent, T. next_extent,
T. min_extents, T. max_extents, T. pct_increase
From dba_objects o, dba_tables t
Where o. object_name = T. table_name and T. Owner = 'test ';

SQL> Col owner format A10 <br/> SQL> Col tablespace_name format A15 <br/> SQL> Col table_name format A30 <br/> SQL> select O. owner, T. tablespace_name, O. object_id, O. object_name table_name, <br/> 2 T. pct_free, T. pct_used, T. ini_trans, T. max_trans, T. initial_extent, T. next_extent, <br/> 3 T. min_extents, T. max_extents, T. pct_increase <br/> 4 from dba_objects o, dba_tables T <br/> 5 where o. object_name = T. table_name and T. owner = 'test '; <br/> owner tablespace_name object_id table_name pct_free blank comment next_extent blank comment <br/> ---------- blank ---------- empty blank comment -------------- <br/> test dultest 56181 pm_hou_j_resmssqlinstance 10 1 255 20971520 20971520 1 2147483645 0 <br/> test dultest 56179 pm_day_j_resdatafile 10 1 255 20971520 20971520 1 2147483645 0 <br/> test dultest 56180 pm_day_s_rescpu 10 1 255 20971520 20971520 1 2147483645 0 <br/> test dultest 56182 pm_hou_s_resprocess 10 1 255 20971520 20971520 1 2147483645 0 <br/> SQL> <br/>

From the data above, we can see that the initial_extent and next_extent of the four tables are both 20971520 (20 m ). This is because the storage attribute of the table is not specified during table creation, but inherits the space attribute of the tablespace dultest.

SQL> Col owner format A10 <br/> SQL> Col tablespace_name format A15 <br/> SQL> Col table_name format A30 <br/> SQL> select O. owner, T. tablespace_name, O. object_id, O. object_name table_name, O. created, O. last_ddl_time, O. status, <br/> 2 T. num_rows, T. blocks, T. empty_blocks, T. avg_space, T. avg_row_len/* space usage information */<br/> 3 from dba_objects o, dba_tables T <br/> 4 where o. object_name = T. table_name and T. owner = 'test '; <br/> owner tablespace_name object_id table_name created dynamic status blocks blocked space blocks <br/> ---------- certificate ------- ---------- ------------ --------------- <br/> test dultest 56181 release 28-7 month-10 28-7 month-10 valid 31982 172 0 0 33 <br/> test dultest 56179 pm_day_j_resdatafile 28-7 month-10 28-7 month-10 valid 165823 905 0 0 32 <br/> test dultest 56180 pm_day_s_rescpu 28-7 month-10 28-7 month-10 valid 17860 100 0 0 32 <br/> test dultest 56182 pm_hou_s_resprocess 28-7 month-10 28-7 month-10 valid 1536573 8601 0 0 34 <br/> SQL>

We can see the number of rows in each table, blocks used, empty blocks, and average length of each row. However, the current information may be inaccurate.

Because the default initial_extent and next_extent of the pm_hou_j_resmssqlinstance table are both 20 m (2560 blocks ). The sum of blocks and empty_blocks is not an integer multiple of 2560.

The statistical information of the row table:

Analyze table test. pm_hou_j_resmssqlinstance compute statistics; <br/> analyze table test. pm_day_j_resdatafile compute statistics; <br/> analyze table test. pm_day_s_rescpu compute statistics; <br/> analyze table test. pm_hou_s_resprocess compute statistics;

The table information is as follows:

SQL> Col owner format A10 <br/> SQL> Col tablespace_name format A15 <br/> SQL> Col table_name format A30 <br/> SQL> select O. owner, T. tablespace_name, O. object_id, O. object_name table_name, O. created, O. last_ddl_time, O. status, <br/> 2 T. num_rows, T. blocks, T. empty_blocks, T. avg_space, T. avg_row_len/* space usage information */<br/> 3 from dba_objects o, dba_tables T <br/> 4 where o. object_name = T. table_name and T. owner = 'test '; <br/> owner tablespace_name object_id table_name created dynamic status blocks blocked space blocks <br/> ---------- certificate ------- ---------- ------------ --------------- <br/> test dultest 56181 release 28-7 month-10 28-7 month-10 valid 32400 214 2346 2299 36 <br/> test dultest 56179 pm_day_j_resdatafile 28-7 month-10 28-7 month-10 valid 164550 918 1642 38 <br/> test dultest 56180 pm_day_s_rescpu 28-7 month-10 28-7 month-10 valid 17860 150 2410 3358 38 <br/> test dultest 56182 pm_hou_s_resprocess 28-7 month-10 28-7 month-10 valid 1576338 8828 1412 38 <br/> SQL> <br/>

We can see that the data of the num_rows blocks empty_blocks avg_space avg_row_len columns in the table has been updated.

Blocks + empty_blocks is an integer multiple of the value 2560.

The number of rows is also the most accurate at present.

SQL> select count (*) from test. pm_hou_j_resmssqlinstance; <br/> count (*) <br/> ---------- <br/> 32400 <br/> SQL> select count (*) from test. pm_day_j_resdatafile; <br/> count (*) <br/> ---------- <br/> 164550 <br/> SQL> select count (*) from test. pm_day_s_rescpu; <br/> count (*) <br/> ---------- <br/> 17860 <br/> SQL> select count (*) from test. pm_hou_s_resprocess; <br/> count (*) <br/> ---------- <br/> 1576338 <br/>