Server Utilities :: Remove All Dependencies On Tablespace

Mar 4, 2011

I have been asked to take the export of a schema and then asked to remove all dependencies on tablespace. Provide me the syntax.

View 1 Replies


ADVERTISEMENT

Server Utilities :: How To Import Dump Into Specific Tablespace Instead Of Default Tablespace Users

Jan 18, 2012

How to import dump into specific tablespace instead of default tablespace users.

I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .

View 2 Replies View Related

Server Administration :: Remove Datafile From Operating System After Dropping Its Tablespace

Jul 26, 2012

I want to remove a tablespace with it's datafile.

I've ran: DROP TABLESPACE x_tbs INCLUDING CONTENTS;

I've read(should have read it prior to action) [URL]tm

and I understand now that I should have stated "AND DATAFILES", However, too late now. the tablespace is removed, but the datafile x_tbs is still present in a folder of my OS.

Is it safe to manually remove it by deleting it from the operating system?

View 2 Replies View Related

Server Utilities :: Remove Current Archive Logs

Sep 12, 2011

Developer is importing some large volumes of data. As the filesystem was filling up fast, I removed the all the archive log files. Will this affect the functioning of database? It is a development environment.

View 6 Replies View Related

Server Utilities :: Exp / Imp Tables From One Tablespace To Another?

Oct 19, 2012

C:UsersNeetesh>expdp system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2

Export: Release 11.2.0.1.0 - Production on Fri Oct 19 16:39:06 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2

[code]...

then i import it in new user

SQL> create user temp identified by temp;
User created.
SQL> grant create session ,create table to temp;
Grant succeeded.
SQL> alter user temp quota 10 m on users;

[code]...

it gives 2 errors both are same as -ORA-01950: no privileges on tablespace 'new_tbl'

exported table are exist in this 'new_tbl' tablespace but this is not exist in importing database.then is there any way to import these tables in 'users' tablespace or other tablespace other than 'new_tbl'?

View 2 Replies View Related

Server Utilities :: Tablespace Management?

May 22, 2012

how we can perform the tablespace management? monitor the tablespace and tune the tablespace space.

View 1 Replies View Related

Server Utilities :: Import To Different Tablespace?

Jul 2, 2011

How to import a dump file into different tablespace.

normally when i am importing a dump file it goes to the table space where it was exported but i need to import in to different tablespace

View 9 Replies View Related

Server Utilities :: Transportable Tablespace

Jul 24, 2010

I want to transportable tablespace feature of EXP/IMP. I did it with no error. But when I check user by user I found that no Function Procedures were imported. How to deal with these objects using transportable tablespace feature.

View 8 Replies View Related

Server Utilities :: Refresh Tablespace Containing Indexes Only?

Sep 27, 2011

I am trying to refresh tablespace containing indexes only. Can you let me know whether i am on the right track. Below is my query :

expdp directory=expdp DUMPFILE=user_index.dmp LOGFILE=user_index.log TABLESPACES=user_index

impdp directory=expdp dumpfile=user_index.dmp logfile=user_index_impdp.log tablespaces=user_index

View 4 Replies View Related

Server Utilities :: Import Data In One Tablespace

Nov 6, 2013

I have taken an export using expdp of schema, data of the schema spread across different tablespaces , now i want to import the data to only one tablespace.

View 1 Replies View Related

Server Utilities :: How To Move All Data From One Tablespace To Another

Aug 23, 2012

I want to move all data from my users tablespace to anohter new tablespace i have created name test1

so how can i do this using expdp

i want to move all objects from this my users tablespace to this new tablespace

View 4 Replies View Related

Server Utilities :: Tablespace For Importing Objects

Sep 20, 2010

Two different says in different documentation as below.

1) Import required same tablespace to fit the objects, otherwise it throws error.

2) Import by default uses the default tablespace to fit the objects, but some times it uses SYSTEM tablespace also.

confirm with the correct one.

View 8 Replies View Related

Server Utilities :: Migrating Using Transportable Tablespace?

Jun 3, 2012

I am using transportable tablespace to migrate from 10g to 11g from Windows to Linux System.

The steps are as follow On 10g make the tablespace read only mode and export the metadata information and copy the tablespace datafiles to the 11g server. Now on 11g when i am importing the exported metadata it says that the user does not exist and if i create the user and tablespace it does not work as it says tablespace already exist.

For transportable tablespace do i have to create the user already on 11g ? If yes then i also need to create the tablespace which i need to assign to the user.

View 2 Replies View Related

Server Utilities :: Transport Tablespace With Database Link

Jun 1, 2012

Is this possible to run a direct impdp with database link for transport tablespace instead of first creating export dump and then using impdp.

I tried the below command and it shows up error.I have gone through the entire Oracle docs but could not find the exact command with network link for transport tablespace.

$impdp system/passwd DIRECTORY=data_pump_dir transport_tablespaces=TBS1,TBS2,TBS3 LOGFILE=tbs.log network_link=dblink

UDI-00015: invalid context or job state for parameter, 'transport_tablespaces'

View 1 Replies View Related

Server Utilities :: Added A Datafile In Undo Tablespace

Apr 28, 2011

When I am importing, I get these errors

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....

I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.

View -1 Replies View Related

Server Administration :: Invalidation Of Dependencies?

Aug 10, 2011

In a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".

View 4 Replies View Related

Server Utilities :: Estimate Tablespace Growth While Loading Data Using Sqlldr?

Jun 1, 2011

We load large amount of data into multiple tables using sqlldr. Amount of data that we need to load varies according to the situation. We want to estimate the tablespace usage growth due to this data load, so we can verify/extend the tablespaces before the data load. Though, setting to autoextend will work in this case, We want to avoid extending the tablespace during sqlldr executing due to performance.

Our initial attempt was to note the tablespace size before and after executing the sqlldr and use the delta. But this delta was not consistent in different environments for the same amount of data. Different environments mean different oracle servers, different existing sizes of tablespaces, One data file Vs multiple data files etc.

How do we reliably estimate how much tablespace we need for the given amount of data?

View 3 Replies View Related

Server Utilities :: ORA-01652 - Unable To Extend Temp Segment By 8192 In Tablespace IDX-TAB?

Nov 14, 2011

I am doing an import job and the following error occurs during Index import. is the reason for this error?

View 1 Replies View Related

Server Utilities :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace USERS

Apr 4, 2013

while importing data i got this error in my log file.and i cannot import my data successfuly

in my log file error i found like this

ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:

i can import my data using imp utility using this syntax

C:UsersAdministrator>imp tiger/****@tcs file=E:DUMP s.
dmp log=E:DUMP s.log fromuser=tiger121 touser=tiger statistics=none

this my user tiger default tablespace its uses and its a auto extend on and locally managed tablespace,and i have enough space on my drive also.

View 21 Replies View Related

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?

creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces

View 9 Replies View Related

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

My os version is

Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux

My database is OLP system.

My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?

Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.

View 7 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.

The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.

This database is a mission critical database and very less downtime can be allowed.

View 6 Replies View Related

Find Column Dependencies

Oct 22, 2010

I am having to pull out all the dependencies for a column as below.

When I select a column in a table, it should list

1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.

Is there a easy way of getting this information from the Oracle dba tables?

View 3 Replies View Related

SQL & PL/SQL :: How To Add Row Dependencies In Existing Table

Jul 20, 2012

How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..

View 2 Replies View Related

SQL & PL/SQL :: Hierarchical Query With Many-to-many Dependencies?

Apr 22, 2010

I have a hierarchical data structure where a child can have many parents, and a parent can have many childs.See the attached file hierarchy_iliustration.jpg. This example has 4 hierarchy levels, in real problem there can be unlimited number of levels.I want to write a SQL query that lists all indirectly dependent child nodes for a given parent node.

Test structure for example attached:

CREATE TABLE TEST.NODE_T
(
ID NUMBER PRIMARY KEY,
TEXTAS VARCHAR2(254)
);

[code]....

View 5 Replies View Related

SQL & PL/SQL :: Dependencies Between Objects In Database?

Dec 3, 2012

Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.

Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.

View 9 Replies View Related

SQL & PL/SQL :: List Of Dependencies For Invalid Objects?

Jun 11, 2013

find out the list of referenced objects which are dependent on INVALID objects.

I wrote the below query to get the referenced objects which depends on invalid objects.

select owner,name,type, referenced_owner,referenced_name, referenced_type, referenced_link_name , dependency_type from dba_dependencies
where type not in ('JAVA CLASS')
AND referenced_type NOT IN('JAVA CLASS')
AND (NAME, TYPE) IN (
select object_name, OBJECT_TYPE from dba_objects
WHERE STATUS='INVALID')

And the output is something like below -

Sr. No OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE
1PUBLICVEHICLE_INSPECTION_REQUESTSYNONYMINSBIN$Uu99fysmRj6Ppn2QppCTWg==$0TABLEHARD
2PUBLICPRODUCT_HIERARCHY_MAP_TEMPSYNONYMCONFSYSBIN$ndGddLcKSDWRwsn5g91Rcg==$0TABLEHARD
3PUBLICACPKG_SUB_RECEIPTINGSYNONYMINSACPKG_SUB_RECEIPTINGPACKAGEHARD

[code].....

Our requirement is to drop all the invalid objects. But we need to know whether it effecting to any other valid objects?Do any valid objects gets INVALID after dropping of the INVALID objects. We need to know the hierarchy of it.

View 5 Replies View Related

SQL & PL/SQL :: How To Find Remote Dependencies Of Database Object

Feb 16, 2010

As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.

Is there any data dictionary in oracle which proivides information about remote dependencies.

Any other way to get a list of remote dependencies other then manual checking.

View 3 Replies View Related

SQL & PL/SQL :: Dependencies Of Tables On Package At Column Level

Mar 29, 2010

I need to identify the dependencies of all the Tables on Packages at column level.

E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T

The resultant query / code should return like this

PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T

Identify the dependencies at column level.

View 5 Replies View Related

Server Administration :: How To Remove Deleted Entry From Sys.ts$

Oct 15, 2011

### Changes made ###
1 week before we did a change on tablespace segment management - from MANUAL to AUTO by following method:
1. create INVD2 & INVX2 & LOBD tablespace.
2. Move TABLE from INVD to INVD2.
3. Rebuild INDEX from INVX to INVX2.
4. Move LOBSEGMENT from INVD to LOBD tablespace.
5. After confirm no segments exist in old tablespace, offline and drop INVD & INVX.
6. Change default tablespace for INV user to INVD2.
7. RENAME TABLESPACE INVD2 to INVD, INVX2 to INVX.
8. Change default tablespace for INV user to INVD back.
9. Run Gather Schema Stat for INV using UNIX scheduler which work usually. However, error ended with ORA-03113 & ORA-03114.
10. Manual execute with same statement the following day, procedure completed successfull.

After 1 week later, inventory forms detected error FRM-40735 in all forms. Checked the gather schema stat job was run in the morning before user feedback..

AFter refer notes from metalink, I understand this is a bug where RENAME of the tablespace could not rename as the previous one, as the deleted entry is still exist in sys.ts$?

There is no segments exist in the deleted tablespace, or any user default tablespace is assigned to the deleted tablespace.

My Question:How can we delete the deleted entry from sys.ts$?And should we rename the tablespace from INVD to INVD3 (or can we use back INVD2) to avoid any unforseen error again?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved