How to Remove and Drop Datafiles from Tablespace in Oracle Database Kako to Maknuti i Kap Datafiles iz Tablespace u Oracle baza podataka

Oracle RDBMS databases stores data logically in the form of tablespaces and physically in the form of data files. Oracle RDBMS baza podataka pohranjuje podatke logično u obliku tablespaces i fizički u formi datotekama. The datafiles are added to the tablespaces as database space requirement grows bigger. The datafiles su dodani to tablespaces kao baza podataka zahtjev za prostorom raste veći. However, there are several reasons you may want to remove or delete data files from a tablespace. Međutim, postoji nekoliko razloga ti svibanj ištanje to maknuti ili izbrisati podatke iz datoteke tablespace. Such as accidentally add a wrongly sized or unwanted datafile to a tablespace, or the data space usage has became smaller and some data files want to be removed, or attempt to recover Oracle database which fails to start due to missing or corrupted datafiles by removing them, Oracle does not provide an easy way or user interface to delete or drop datafiles from a tablespace. Kao što je slučajno dodati pogrešno veličine ili neželjeni datafile na tablespace, ili podaci prostor običaj je postao manji i neki podaci kartoteka ištanje biti maknuti, ili pokušati oporaviti Oracle bazu podataka koja ne uspijeva pokrenuti zbog nestale ili oštećene datafiles uklanjanjem ih , Oracle ne pruža jednostavan način ili korisničko sučelje to izbrisati ili kap datafiles iz tablespace. Once a datafile is made part of a tablespace, it can no longer be detached or removed from the tablespace, albeit there are several workarounds. Jednom datafile dio izrađen je od tablespace, to se više ne može biti odvojen ili ukloniti iz tablespace, iako postoji nekoliko workarounds.

How to Completely Drop the Whole Tablespace with All Datafiles Kako Kap Potpuno cijeli Tablespace sa svim Datafiles

The easiest way to drop a or multiple datafiles is by dropping the entire tablespace together with its datafiles. Najlakši način da ispusti ili više datafiles je pada cijeli tablespace zajedno sa svojim datafiles. If you no longer need the data contents of the tablespace, the following command will drop the tablespace, the datafile, and the tablespace's contents from the data dictionary. Ako više ne trebate podatke o sadržaju tablespace, slijedeće naredba će ispustiti tablespace je datafile, a tablespace's sadržaja iz podataka rječnik. All of the objects that where contained in that tablespace are permanently removed. Svi predmeti koji, gdje su sadržane u tom tablespace trajno uklanjaju.

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES; Drop TABLESPACE <tablespace name> UKLJUČUJUĆI sadržaja i DATAFILES;

If you don't specify “AND DATAFILES”, Oracle will not drop the physical datafile after the DROP TABLESPACE command, but you can always delete the files from operating system shell (If the data files are locked, restart the server). Ako ne odredite "DATAFILES I", Oracle neće ispustiti do fizičkog datafile nakon pada TABLESPACE naredbu, ali uvijek možete brisati datoteke iz ljuske operacijskog sustava (Ako se podaci kartoteka jesu zaključan, ponovo pokrenuti poslužitelj).

But always perform a backup of the database, as the “DROP TABLESPACE” command is irreversible. Ali uvijek izvršiti backup baze podataka, kao što je "Kap TABLESPACE" naredba je nepovratan. It's also a good practice to check and identify how many datafiles a tablespace actually has before performing the drop action in order to avoid mistake. Također je dobra praksa da provjerite i utvrdite koliko datafiles jedan tablespace zapravo je prije vršenja drop akciju kako bi se izbjegla pogreška.

How to Check How Many Datafiles a Tablespace Has Kako to Ček Koliko Datafiles Ima jedna Tablespace

To determine and identify all datafiles that link to a tablespace, use the following query, with tablespace name in capital letter: Da biste utvrdili i identificirati sve datafiles koje vode na tablespace, koristite sljedeći upit, s tablespace ime u veliko slovo:

SELECT file_name, tablespace_name ODABERITE FILE_NAME, tablespace_name
FROM dba_data_files OD dba_data_files
WHERE tablespace_name ='<tablespace name>'; GDJE tablespace_name = '<tablespace name>';

If a tablespace contains multiple datafiles, and you just want to drop one or some of the datafiles and keep the remaining datafiles together with the objects and contents, the objects or data must be exported for the affected table space. Ako tablespace sadrži više datafiles, a vi jednostavno želite ispustiti jedan ili neke od datafiles i zadržati preostali datafiles zajedno s predmetima i sadržajima, objekata ili podaci moraju se izvesti za zahvaćene tablice prostora. Once exported, the tablespace can be dropped with above “DROP TABLESPACE” command. Jednom izvezeno je tablespace može biti izostavljen sa gore "Drop TABLESPACE" naredba. Then, recreate the tablespace with the datafile(s) required (that you initially want to keep), and then import the objects into the recreated tablespace. Zatim, ponovno je tablespace s datafile (a) obavezna (da se u početku želi zadržati), a onda uvesti predmete u recreated tablespace.

If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped. Ako jedna ili više datafiles je nestalo nakon što je proces oporavka ili slučajnog brisanja, možete koristiti Alter BAZE DATAFILE <datafile name> Offline Drop naredba to izraditi datafile offline tako da baza može se pokreće nakon što je uznemiri tablespace može biti izostavljen. (See (Vidi instruction to recover from missing datafiles uputstvo za oporavak od nestalih datafiles .) .)

How to Resize a Datafile to Minimum Size Kako Resize jedan do Datafile Minimalna Veličina

Another alternative to drop the datafile is by shrinking the size of datafile instead of dropping. Druga alternativa ispustite datafile je smanjenje veličine datafile umjesto pada. This option is only possible if there is no extents in the datafile. Ova opcija je moguća samo ako nema extents u datafile. If there are none, it's possible to resize the data file down to a very small file (2 blocks), where Oracle database will no longer create any extent in the datafile. Ako postoji ništa, to je moguće promijeniti veličinu datoteka podataka dolje do vrlo malih datoteka (2 bloka), gdje je Oracle baza više neće stvarati bilo kakve mjere u datafile. However, this workaround does not remove the datafile from tablespace nor delete the file physically, but it reduce the risk as no data will be truncated as only empty blocks are reduced. Međutim, ovo rješenje neće ukloniti ni datafile iz tablespace izbrisati datoteku fizički, ali se smanjio rizik kao nema podataka će biti skraćen kao samo prazni blokovi su smanjene. It simply makes it unusable and takes up almost close to no disk space. To se jednostavno čini neupotrebljiv i uzima gotovo u blizini nema prostora na disku. To resize a datafile, use the following query: Za veličinu jednog datafile, koristite sljedeći upit:

alter database datafile '<datafile name>' resize 8M; alter database datafile '<datafile name>' veličinu 8m;

How to Determine the Extents inside Datafile Kako utvrditi Extents unutar Datafile

To check and determine the extents that exists in a datafile, use this query: Da biste provjerili i utvrdili da postoji extents u datafile, koristite ovaj upit:

SELECT owner, segment_name ODABERITE vlasnik, segment_name
FROM dba_extents a, dba_data_files b OD dba_extents a, b dba_data_files
WHERE a.file_id = b.file_id GDJE a.file_id = b.file_id
AND b.file_name = '<datafile name>' I b.file_name = '<datafile name>'

IMPORTANT : The page is machine translated and provided "as is" without warranty. VAŽNO: Na stranici je stroj prevedeno i dostavlja "kakav je" sa garantni. Machine translation may be difficult to understand. Strojno prevođenje svibanj biti teško za razumjeti. Please refer to Molimo pogledajte original English article Engleski originalni članak whenever possible. kad god je to moguće.

Related Articles Povezani članci

• How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile Kako Kap Tablespace i oporavak Oracle Database Kada slučajno Izbriši Datafile
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error Oracle ORA-01658 Stvoriti Ne može se početno mjere za segment u Tablespace Greška
• How to Rename or Move Oracle Tablespace Datafile to Another Location Kako to Preimenovati ili Premjestiti Oracle Tablespace Datafile na drugu lokaciju
• Delete, Remove or Drop Oracle Stored Packages with Drop Package Izbrisati, Maknuti ili Kap Oracle Pohranjenim Paketi s Kap Package
• ORA-25153 Temporary Tablespace is Empty Error in Oracle ORA-25153 Privremen Tablespace je Prazan Greška in Oracle
• Oracle Database Link Oracle Database Link
• ORA-02449 Oracle Drop Table Error ORA-02449 Oracle Kap Stol Greška
• Change Oracle Database User Password Promijeni Oracle Database Korisnik Lozinka
• Oracle Database Import Error 3113/3114 Oracle baza podataka Uvoz Greška 3113/3114
• View and Retrieve Oracle Database Link Details Pogledajte i Preuzeti Oracle Database Link Details

This entry was posted on Saturday, August 4th, 2007 at 12:29 am and is filed under Ovaj ulaz je pošta na Subota, Kolovoz 4th, 2007 at 12:29 i dokumentirane pod Databases Baze podataka . . You can follow any responses to this entry through the Možete udarac bilo koji reakcija to ovaj ulaz preko RSS 2.0 RSS 2.0 feed. You can feed. Moľete leave a response ostaviti odgovor , or , Ili trackback kugla from your own site. iz vlastite web lokacije.