My team maintains an Oracle database that is approx. 200GB in size. All of the data (tables, indexes, etc) lives inside a single 'USERS' tablespace. Is this a bad idea? What benefits are there to having multiple tablespaces, and under what circumstances would I want to add more to my database? Thanks! ...
In DB2 8.2 for LUW can you restore one tablespace to a 2nd server from a full database backup without having to restore the entire database? The database backup was taken when the system was up so log files are involved. I can provide further clarification if needed. Thanks, Dave ...
I'm importing a database dump from one Oracle 10g installation into another. The source has a layout with several tablespaces. The target has one default tablespace for the user I'm importing the dump into. Everything works fine, for ordinary tables. The tables are relocated from their original tablespace to the user's default. The prob...
I have a partitioned table that belongs to tablespace report. I want to move it to tablespace record instead. One possibility is to drop the table and recreate it in the new tablespace, but that is not an option for me, since there is data in the table that needs to survive the move. I started by checking that the partitions actually b...
Hi Experts, I am using the below script for generating a DDL to create tablespaces in the database. select 'create tablespace ' || df.tablespace_name || chr(10) || ' datafile ''' || df.file_name || ''' size ' || df.bytes || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize ' || maxbytes) || chr(10) || 'default...
I'm looking for a way to quickly "clone" a database from 1 postgresql server to another. Assuming... I have a postgresql server running on HostA, serving 2 databases I have 2 devices mounted on HostA, each device stores the data for one of the database (i.e. 1 database => 1 tablespace => 1 device ) I am able to obtain a "safe" point i...
I have a table X in tablespace T1 and a table Y in tabelspace T2.(Oracle DB) I have to create a trigger in tablespace T1 that will, on the event of updating a column C in table X, update column D in table Y (tablespace T2). Because they are in different tablespaces, my first question is can this be done at all? And if yes then how it...
Hello, I need to know true tablespace size in Oracle. I have some tablespace and I need to know how many space it uses now and how many space is free (and maybe percent of free space). I found in web some sqls but all of them showed size based on water mark... which is not true space allocated now but as far as I know the highest value...
I need to determine the MAXSIZE that was set for a tablespace when it was created (Oracle 10g) I'm sure I'm missing something obvious, but the information isn't immediately apparent in the information in DBA_TABLESPACES ...
I'm on Sybase Iq 15.1 and try to add a dbspace to a demo database, using a raw device on Linux. I always get SQL error 1010000, file already exists: CREATE DBSPACE KLMTEST USING FILE DF1 '/dev/disk/by-id/scsi-1HITACHI_730109670008' IQ STORE; Could not execute statement. The file '/dev/disk/by-id/scsi-1HITACHI_730109670008' already exis...
This is a practice assignment where I have to create a table space with two datafiles. We have to use the exact naming conventions that are given to us. I believe I have it right, but I get this error: "ORA-00972: identifier is too long". I think I'm getting this because the path and filename are so long. The question is, how do I get ar...
I tried to delete the data from tablespace using the command DROP USER xyz CASCADE; I found that user was dropped but size of my tablespace did not decreased. Is my data fom the tablespace have been deleted? If yes, how can I confirm that? And why was the space not deallocated? ...
I am practicing SQL, and suddenly I have so many tables. What is a good way to organize them? Is there a way to put them into different directories? Or is the only option to create a tablespace as explained here? ...
I have postgres database running on Amazon EC2 instance. I have few tablespaces created for some monthly tables, such that each table is on individual tablespace. To get the maximum performance, I have created each tablespace on individual amazon ebs volume. I want to move some of this tables to different instance and database. I will e...