0
votes
1answer
26 views

Altering multiple tablespaces at once in Oracle

I need help in altering multiple tablespaces at once in Oracle Database 11g. My particular scenario is that this command will be used in a shell script to set all of the tablespaces in read-write mode ...
1
vote
0answers
187 views

How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?

I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping. Here's the error I get: ...
0
votes
0answers
90 views

While altering tablespace shows error tablespace does not exist

SQL> select * from v$tablespace where name = 'EXAMPLE'; TS# NAME INC BIG FLA ENC CON_ID 4 EXAMPLE YES NO YES 3 Above query shows that ...
0
votes
1answer
38 views

List all privileges granted on tablespace

Frineds, DB: 11gR2 OS: Unix So I'm moving all objects of a tablespace-A to new tablespace-B. Is there a way to list all privileges/quota granted for tablespace-A? Since tablespace-A can have ...
2
votes
2answers
43 views

Restoring a tablespace to a different database

Any ideas as to how we can re-create a tablespace on a different server? We have a cold backup, so we can copy the datafiles to the target server. Can we just create a new instance on the target ...
-1
votes
1answer
153 views

Oracle Partition Table Space Issue

I have a table with list partitions on it. When i insert data i see that all the partitions have default initial size of 8 MB and I also noticed that when i truncate full table and check size it still ...
0
votes
1answer
95 views

Tablespace of an index organized table

I want to find the tablespace of an index organized table with primary key. With dba_segments I can only find the tablespace for the primary key. But how can I find the tablespace for the table?
1
vote
2answers
67 views

what is better in terms of allocated space in Oracle Tables: NUMBER or BINARY_FLOAT?

I have a 10 billion records table so I need to improve space allocation. My table has only fields of type floating point (single-precision). What is the best choice, NUMBER or BINARY_FLOAT or ...
0
votes
0answers
167 views

ORA-01658 on index tablespace when trying to insert into empty table of new database

We are getting ORA-01658 (Error :ORA-01658: unable to create INITIAL extent for segment in tablespace MYDATA_LDATA_INDEX) in our application logs. This happens when application is trying to insert ...
2
votes
2answers
150 views

What are the performance benefits of segregating database objects into different tablespaces?

As the question states, what are the benefits of doing this given the following information/constraints: I will not be using partitioning I will not be placing the tablespaces and its datafiles into ...
0
votes
1answer
108 views

Tablespaces in oracle

I understand that tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which is the physical collection of objects). Apart from this, what is ...
1
vote
1answer
2k views

Oracle, Create table with inline constraint declarations using a different tablespace

I have two questions about inline constraints declarations for Oracle tables: Is it a bad practice? If so, why? How could one declare a different tablespace for the primary key and index like it's ...
1
vote
1answer
282 views

Any advantage of creating table in temporary tablespace - Oracle

My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?
1
vote
1answer
2k views

Oracle database created whats next? Create Schema or Create Tablespace?

I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here. I'm supposed to create a database for an application. ...
4
votes
2answers
16k views

“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
1
vote
1answer
659 views

Oracle delete technique for table's segment size (data compactness)

I would like to ask if there is any effect on the following deletion technique of a database to the tablespace size. The existing code for clearing the data has the following style of code before: ...
2
votes
1answer
7k views

Expanding system tablespace

I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?
2
votes
1answer
1k views

Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES?

I tried to compute the audit log table usage using the following steps: Truncate all the audit log table data (i.e. AUD$) Perform test case that inserts and update data, also delete statement but it ...
1
vote
0answers
447 views

How do I import a dump for a deleted tablespace?

I'm trying to import a database dump, however I am encountering this error: ORA-39083: Object type TABLE:"MYSCHEMA"."MINF" failed to create with error: ORA-00959: tablespace '_$deleted$5$0' does not ...
3
votes
1answer
2k views

Drop user did not reduce size of tablespace file

I have a development environment in which I frequently copy a certain schema. When I'm done with the copy, I will use OracleEM to drop that user/schema. My problem is that this does not seem to ...
2
votes
1answer
86 views

Why does connecting to a database use tempspace?

Earlier today we had a problem logging into a database that had just been created automatically. When on the actual box (Linux) sqlplus schema/pw, i.e. a local connection, was fine, though, it was ...
3
votes
2answers
5k views

In Oracle how do you move a Schema to a different Tablespace?

I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?
1
vote
1answer
291 views

file not found - how to remove its configuration?

I run oracle 10.2.0.5. I have the following: SQL> select file#, status, error, recover, tablespace_name, name 2 from v$datafile_header 3 where recover='YES' 4 or (RECOVER IS NULL AND ...
4
votes
1answer
5k views

What units used in DBA_TABLESPACE_USAGE_METRICS?

What units are used in the usage metrics (USED_SPACE and TABLESPACE_SIZE columns) described in Oracle's DBA_TABLESPACE_USAGE_METRICS static data dictionary view? The Oracle Database Reference only ...
9
votes
2answers
1k views

Recovering logical space from a tablespace

I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space. A week ago I queried ...
3
votes
1answer
3k views

How can I delete a corrupted table space?

Problem: On an Oracle 11.2g DB; We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager). ...
1
vote
1answer
213 views

Oracle block usage

I am wondering about a weird deadlock and after checking all other components I am considering it to be a INITRANS problem in Oracle 11.2. Now three questions arose: Is there some way of analyzing ...
2
votes
1answer
2k views

How do I create a database+tablespace+user in Oracle 11gR2?

Good evening, I'm trying to figure out how to create a database+tablespace+user in Oracle 11g. I've tried some tutorials, but nothing seems to work. I cannot find Database Configuration Assistant ...
2
votes
1answer
236 views

Tablespace consumed by views

I was in an SQL class where the instructor said "DBAs don't like it when users create views, because they will clobber tablespace." Obviously, I understand that any schema object will consume some ...
3
votes
2answers
1k views

What are the benefits of using a different tablespace for a new user schema?

The tables in the schema are intended to be used read only and is only updated once each three month. My question refers to performance maintenance (backup/restore, export/import) What about the ...