DROP TABLESPACE when the data file is automatically deleted

2010-03-29  来源:本站原创  分类:Database  人气:186 

DROP TABLESPACE when the data file is automatically deleted

OMF OMF and non-management in the DROP TABLESPACE data files will automatically deleted when doing the test:

SQL> alter system set db_create_file_dest = '/ oradata / data / standby' scope = both;

System altered.

SQL> show parameter db_create

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
db_create_file_dest string / oradata / data / standby
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> create tablespace t3 datafile size 10m;

Tablespace created.

SQL> select * from v $ tablespace;

TS # NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 INDX YES
4 USR YES
5 KONG YES
6 TEST YES
9 T1 YES
8 LOGMNRTS YES
10 T2 YES
11 T3 YES

11 rows selected.

SQL> select name from v $ datafile
2 where ts # = 11;

NAME
-------------------------------------------------- ------------------------------
/ Oradata/data/standby/o1_mf_t3_464smo7s_.dbf

SQL> create table t100 (id int) tablespace t3;

Table created.

SQL> drop tablespace t3;
drop tablespace t3
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> drop tablespace t3 including contents
2;

Tablespace dropped.

SQL>! Ls-l / oradata/data/standby/o1_mf_t3_464smo7s_.dbf
ls: 0653-341 The file / oradata/data/standby/o1_mf_t3_464smo7s_.dbf does not exist.

See DROP TABLESPACE, then even if that option is not added and datafiles, ORACLE will automatically delete the data file;

SQL> create tablespace t3 datafile '/ oradata/data/standby/data/t3.dbf' size 10m;

Tablespace created.

SQL> create table t100 (a int) tablespace t3;

Table created.

SQL> drop tablespace t3;
drop tablespace t3
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> drop tablespace t3 including contents;

Tablespace dropped.

SQL>! Ls-l / oradata/data/standby/data/t3.dbf
-Rw-r ----- 1 oracle dba 10493952 Jun 25 23:42 / oradata/data/standby/data/t3.dbf

Been added and datafiles parameters, oracle will not automatically remove the datafiles.

SQL> create tablespace t3 datafile '/ oradata/data/standby/data/t4.dbf' size 10m;

Tablespace created.

SQL> create table t100 (a int) tablespace t3;

Table created.

SQL> drop tablespace t3 including contents and datafiles;

Tablespace dropped.

SQL>! Ls-l / oradata/data/standby/data/t4.dbf
ls: 0653-341 The file / oradata/data/standby/data/t4.dbf does not exist.

Together and datafiles parameter, oracle will automatically delete datafiles.

SQL> alter system set db_create_file_dest =''scope = both;

System altered.

SQL> create tablespace t3 datafile '/ oradata/data/standby/data/t4.dbf' size 10m;

Tablespace created.

SQL> create table t100 (a int) tablespace t3;

Table created.

SQL> drop tablespace t3 including contents and datafiles;

Tablespace dropped.

SQL>! Ls-l / oradata/data/standby/data/t4.dbf
ls: 0653-341 The file / oradata/data/standby/data/t4.dbf does not exist.

SQL>

相关文章
  • DROP TABLESPACE when the data file is automatically deleted 2010-03-29

    DROP TABLESPACE when the data file is automatically deleted OMF OMF and non-management in the DROP TABLESPACE data files will automatically deleted when doing the test: SQL> alter system set db_create_file_dest = '/ oradata / data / standby' scope =

  • How do I view Oracle database tablespace size (idle. Has been used), whether to increase the table space data file 2010-11-24

    To view the Oracle database table space, the need to increase the table space data file, database management, disk space is a DBA will encounter the problem, the problem is more common. - 1, has been used to view the percentage of table space select

  • How to check Oracle tablespace and data file information 2010-08-20

    Oracle database table space is the logical unit with the largest unit of storage space, the database system table space for database objects through the allocation of space. Table space is physically embodied in the disk data file, each table space b

  • How to write a batch file to automatically import data into oracle database 2010-10-02

    Oracle Database automatically import and export, in many places there is a wide range of applications. For example: an Oracle database application system development after the end of the system if you want to install to the client computer, you need

  • How to write a batch file to automatically import the data into oracle database 2010-10-02

    Oracle database automatically import and export, in many places there is a wide range of applications, such as: development of an Oracle database application system after the installation if you want the system to the client computer, you need to ini

  • oracle in the block, panel, segment, tablespace, data file relationships 2010-10-17

    ORACLE in physical storage, mainly divided into the control files, data files, log files. Database instance starts, first load the control file, then load the data file. We usually store the data object is physically stored in the main data file, log

  • Oracle data file management 2010-03-17

    ■ create a data file of SQL statements and instructions CREATE TABLESPACE Create a table and its data files CREATE TEMPORARY TABLESPACE Create a temporary table space and composition of its temporary data files ( Temporary data file is a special type

  • How to space from the table, "delete" data file 2010-03-17

    This article describes how to delete data files from the database. Because the ALTER DATABASE DATAFILE OFFLINE DROP command to delete data files can easily lead to doubts, so this article introduces the true meaning of OFFLINE DROP command. There are

  • A data file is corrupted, how to open the database? 2010-04-19

    A data file is corrupted, how to open the database? System environment: 1, the operating system: Windows 2000 Server, the machine memory 128M 2, Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition 3, the installation path: C: \ ORACLE Error beha

  • orcle import data file 2010-06-21

    Bj has to work in 2 months, and feeling into the state has not only forced himself into. In order not to neglect the things learned before the planned start to understand from the database. Then made a copy from somewhere 3.xG database files, restore

  • ORA-01157: can not identify / lock data file 2010-09-30

    ORA-01157: can not identify / lock data file 3 Today restart the server, the database get up suddenly discovered the following error: SQL> startup ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1334380 bytes Variable Siz

  • oracle can not lock the data file identifies the solution 2010-11-10

    Can not identify / lock data file Error message is: SQL> startup ORACLE Routine has been started . Total System Global Area 293601280 bytes Fixed Size 1290208 bytes Variable Size 209715232 bytes Database Buffers 75497472 bytes Redo Buffers 7098368 by

  • oracle lock data file can not identify the solution 2010-11-10

    Can not identify / lock data file Error is: SQL> startup ORACLE Routine has started . Total System Global Area 293601280 bytes Fixed Size 1290208 bytes Variable Size 209715232 bytes Database Buffers 75497472 bytes Redo Buffers 7098368 bytes Completed

  • Change the oracle data file location 2010-12-03

    First, change the location of the process of oracle data files 1, the tablespace read-only, read-only data can still be accessed for the user. SQL> alter tablespace tscsfm read only; 2, the physical copies of documents 3, the table space is offline S

  • Completely remove the data file ORALCE 2011-03-08

    Create table space will usually specify the appropriate data file, but even if you delete the table space is added INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; oralce still useless data is deleted at the same time, Oh, look at my solution, I

  • Data file is successfully created, deleted 2011-04-29

    Data file does not create success alter database datafile'......' offline drop Then manually delete the data file but that there may be hidden Before we detail, we must more clearly: Oracle does not provide, such as deleting tables, views the same wa

  • ORA-01157 (table space data file to be rebuilt when damaged) 2011-06-09

    EM console error is as follows: Processing steps: 1. Backup the data file where the table space (TBS_HY) data exp pcs / hs_hn_pcs @ jwzh tablespaces = TBS_HY file = d: \ jwzhpt_oracle_bak \ tbs_hy.dmp log = d: \ jwzhpt_oracle_bak \ tbs_hy.log owner =

  • Oracle data file directory approach to obtain 2011-10-07

    Is as simple as a statement: select substr (file_name, 1, instr (file_name, '\', -1)) from dba_data_files where rownum = 1; Access to the data file directory, can be done using a script to create table space, or add data files. You can use PLSQL to a

  • Change the table space data file path 2010-07-22

    1. First, connect to the database sql plus 2. Four steps - change the table space data file path 1.alter tablespace tbs_sttu offline; Physical copy to the specified path to this very important] [ 2.alter tablespace tbs_sttu rename datafile 'c: \ orac

  • Only when the oracle data file recovery 2010-08-12

    Brother just using ORACLE, because the IP address has changed, the play of the half, leaving only the data files, online information search of the day, finally getting out successfully restore data files to the new database. (Mostly never played befo