Tag Info

Hot answers tagged

12

When you delete records there is nothing that automatically compacts the segment, therefore you will need to do a segment shrink to reclaim the space. Here is excerpt from the 11.2 Administrator's Guide on Reclaiming Wasted Space: Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not ...


8

If you are planning to drop schemas, having a separate tablespace which can also be dropped will reduce fragmentation in whatever tablespace you would otherwise use (unless you are using uniform extents, but that is not the default). RMAN recovery options are different as you would be able to do TSPITR on each individual schema. Fragmentation is not an ...


6

Yes, it will be due to fragmentation. To reclaim the space, first get a list of tables in the tablespace with the following query (ignoring partitions - edit your question if you're using them): select distinct table_name from dba_tables where tablespace_name = 'DATA'; Then for each table, enable row movement: alter table TABLEINDATAPARTITION enable ...


6

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this: sql> alter user scott quota 50m on system; Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited. However it is a bad ...


6

Your select on dba_tables doesn't take into account: Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others) Empty space in the blocks (due to pct_free mainly) Block headers (initrans influences this size, among others) I.e. it doesn't take into account the physical storage of the data at all. ...


5

When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally. I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store. The biggest piece of the puzzle here is to move all ...


5

The following should do it: Shut down PostgreSQL Make sure PostgreSQL does not run any longer Check that PostgreSQL is really stopped Copy the old data directory to the new drive This is usually defined through a commandline parameter (-D) for your service or through the PGDATA environment variable. Update your PostgreSQL configuration (service, ...


5

A schema itself can not be stored nor can have changed tablespace en bloc in any way. In fact, it is just a meta-structucre. Instead - there is DEFAULT TABLESPACE attribute of underlying USER. If you change it, then new objects are created in this tablespace by default (unless you excplicitly specify another one). In addition, each type of structure ...


5

An additional benefit of using separate tablespace for each schema is that these tablespaces can be placed on different disk groups to meet differing performance, cost, and recovery requirements. For example, one may need to be on flash drives, another on large slow disks, and another on a dedicated RAID 61.


5

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted). Whats the next step? Create the Schema or Tablespace? Both! They're orthogonal. Users are logical entities that access your database. ...


4

Historically, PostgreSQL stored tables and indexes in individual files. Tablespaces are a means of placing multiple tables/indexes into a single file or group of related files in the same directory. Other database management systems use similar techniques, although tney can store multiple objects within a file. On PostgreSQL a tablespace is implemented as ...


4

If you just want to remove the tablespace have you tried logging in as sys and dropping it? DROP TABLESPACE <tablespace> INCLUDING CONTENTS CASCADE CONSTRAINTS; If so, what error message do you receive? (I'm assuming you've made a cold backup of the database at this point in case the tablespace is needed.) Alternatively, if you can determine ...


4

Not true, unless they meant "materialised views" ORAFAQ Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views.


4

I had no problems with a VirtualBox 4.14 Windows 8 preview VM and x64 Oracle 11gR2. My VM uses 40 GB Hard Disk. While installing it downloaded .Net Framework 3.51 I started with a fresh Windows 8 VM in VirtualBox 4.14 and installed guest additions and run Windows update once. Afterwards I installed Oracle and got the items below on my Start page. I find ...


4

Blocks. Blocks are the units of the DBA_TABLESPACE_USAGE_METRICS.USED_SPACE and DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE. The latter column accounts for possible AUTOEXTEND MAXSIZE. EDIT: I'm not sure what is the meaning of USED_SPACE for undo tablespace though. For example: SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents ...


3

I'm assuming you're not using ASM. Either set the current datafile to AUTOEXTEND: alter database datafile '/full/path/to/system01.dbf' autoextend on next 128m maxsize 8192m; Or, add a new datafile: alter tablespace SYSTEM add datafile '/full/path/to/system02.dbf' size 512m; Obviously, alter the numbers and path as needed. Adding a datafile ...


3

From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning LOB type data as well as deprecated types like LONG VARCHAR) into their own tablespaces. Main reason is for maintenance and support. If the data is in different tablespaces you can backup/restore based on ...


3

I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data? Is it enough? No, that is not enough - it is true that if "the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used ...


3

During the connect process Oracle builds information on what access you have. This remains static for the length of the connection. (If you need to access newly granted privileges, you need to reconnect.) Creating this data as temporary tables would seem the appropriate thing to do. This would require temporary tablespace and could trigger the problems ...


3

Like this: create table THIS_TABLE ( id number NOT NULL, constraint THIS_TABLE_PK PRIMARY KEY(id) USING INDEX TABLESPACE INDEX_TABLESPACE ) tablespace DATA_TABLESPACE; USING INDEX TABLESPACE is the syntax - you weren't far off. As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked ...


3

Few uses for the tablespaces: When you no longer need the data you can drop the tablespace and delete datafiles. This is basically the only way to release space back from Oracle to the operating system. When doing backups you can exclude tablespace from backup. E.g. if it contains non essential or easily recreatable data. When doing restore you can skip ...


3

First 2500*50kB ~= 128MB. Then if you will check CREATE TABLESPACE syntax default INITIAL_SIZE for the datafile is 128MB! So if you want to store more data you can either specify INITIAL_SIZE you need while creating tablespace or you can ALTER TABLESPACE ADD DATAFILE. By specifying STORAGE DISK in CREATE TABLE statement you stated that you want on disk ...


3

It depends on your data (and this assumes that you don't care about the different behaviors of the two data types). You can put together a test with sample data that is similar to what you're actually storing to find out what approach will use less space. I'll create a table num_test with two different columns, one declared as a number and one as a ...


2

If you know the original size, it is easy to calculate the number of extends that have occurred. The metadata for the datafile will include its age. The overhead for an auto extend is low, but datafile fragmentation may cause problems. I generally try to extend by 10 to 25 percent of the original size, and aim for as few extend operations as possible. ...


2

Use the TBSP_UTILIZATION administrative view - Retrieve table space configuration and utilization information. See the "Essential DB2 Health Check" for a recommendation on how to use it, especially the sections on File System Free Space and DMS Tablespace Free Space. Example: select substr(tbsp_name,1,10) "Name", tbsp_utilization_percent "Used%", ...


2

I agree with Tom Kyte's 2005+ assessment of this: I actually am a fan of system allocated extents now. It grows the extent allocation size as the table grows. Autoallocate is not going to cause widespread fragmentation. IF you know PRECISELY how big the object is, will be or will grow by -- go ahead, do the math, feel free to use ...


2

Shut the database down. Start it up again in mount mode: connect / as sysdba; startup mount; Drop the datafile: ALTER DATABASE DATAFILE '/full/path/of/file.dbf' OFFLINE DROP; Open the DB: alter database open; Drop the tablespace: DROP TABLESPACE <TS Name> INCLUDING CONTENTS;


2

The design doesn't meet third normal form, but not just because of the city. The fields STREET, CITY are functionally dependant on each other (if you change the city, the street should probably change as well and vice-versa). You could also have the same street, city combination represented in different ways (Foo St, Foo; Foo Street, Foo; etc.). To ...


2

I don't think there's much value in me copying what others have done so well in explaining in the past, so I'll just point you at a brilliant blog post: Reclaiming Unused Space in Datafiles. Essentially you just need to do: ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 111M;


2

How do you define the "tablespace size"? Are you interested in the total size of the data files on disk that comprise the tablespace? Or are you interested in the total size of all the segments that are part of the tablespace? Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under ...



Only top voted, non community-wiki answers of a minimum length are eligible