I am working on Oracle 10g. I want to drop a schema and I want to re use the space in data files to other schema.
If I drop the user by "DROP USER USERNAME CASCADE" , tables in that schema are purged by default or do I have to explicitly drop the tables , purge the re cycle bin and then drop the schema?
Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?
View 5 Replies View RelatedI want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
I have a requirement wherein I have to migrate the data from one schema(source) to another(target).The source and target schema contains 50 tables each.For testing purpose two table are shown below
SOURCE>
DROP TABLE anchor;
CREATE TABLE anchor
(
anchor_idNUMBER(5),
angleNUMBER(5,2),
acad_colorVARCHAR2(20),
att_blkVARCHAR2(20)
);
[code]...
I'm able to migrate the data. On an average the each source table contains 50k to 1 lakh records.
I have 3 users in schema.
User09
User10
User11
Table Name PURCHASE exists in all above three schema with different data(according to date)
Like
User09 ----> purchase ---- data date Jan09 to Dec09
User10 ----> purchase ---- data date Jan10 to Dec10
User11 ----> purchase ---- data date Jan11 to Dec11
I want to select * from purchase, from above all three user with select statement.I can select data one by one with union all
select * from user09.pruchase Union all
select * from user10.pruchase Union all
select * from user11.pruchase
after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query. when I see this query. It gives me the all owner name of User09,User10,User11......
SELECT * FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'USER%'
and table_name ='PURCHASE'
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER PURCHASE USERS
USER09 PURCHASE USERS
USER10 PURCHASE USERS
3 rows selected
Can I get data from Purchase Table of all users with select statement .
I have two same DB schema (same structure, same data) and I need to provide update in one of them when data in the other one is updated. It is singe direction only (we change data in DB Schema A and synchronize data in the DB Schema B; there is not opposite direction). Only small portion of data (compared to the size of DB Schema) might be changed or added this way.
View 1 Replies View RelatedI'm trying to figure out how to import data from another schema for an oracle SQL project in an MIS Course.Each student/schema created the following tables STUDENT, FACULTY, COURSE, IS_QUALIFIED, SECTION, IS_REGISTERED. Each student was supposed to make up names and ID numbers for 20 different students, then use real university faculty, courses, and sections to populate the respective tables.
I need to import data from all of another student's tables, without duplicating any courses, faculty, etc I already have in my tables.
The other student already granted acess to her tables, so now I'm just having trouble with the insert statement. This is what I have so far for the first table.
INSERT INTO myschema.STUDENT
SELECT * FROM otherschema.STUDENT
I know i need to add a "WHERE" statement to successfully copy the other persons data into mine, without duplicating any results. This is the last part of this project and I'm having trouble finding the answer anywhere.
I am trying to move data from one schema in database A to another schema in database B and only move data not others (procedure, view, function). What is the best way to do it? I am thinking database link then insert into select from database link. Is there a better way?
View 8 Replies View RelatedI need to insert data from one schema table to other schema's table in same database.The thing is columns are not equal.so when I am trying to use insert statement it is throwing error as not enough values. The situation is explained clearly below.The insert stmt is implemented in second schema whose table name is b.
SQL> create table a(ID_A NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(10));
Table created.
SQL> insert into a(ID_A,Name,rollno,address)values(1,'ravi', 101,'hyd');
1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(2,'rav', 102,'delhi');
1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(3,'ra', 103,'chn');
1 row created.
SQL> create table b(ID NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(1
0),route varchar2(10),direction varchar2(10));
Table created.
SQL> ALTER TABLE B ADD
2 CONSTRAINT B_PK1
3 PRIMARY KEY
4 (ID);
Table altered.
SQL> create sequence b_seg start with 1;
Sequence created.
SQL> insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual;
insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual
*
ERROR at line 1:
ORA-00947: not enough values
So,for table b in ID column sequence needed to be inserted and other columns need to be taken from table a.I can understand the error is because two tables are not having equal columns.So,the insert stmt is throwing error.
I can manually write by taking columns from a and b and write insert stmt as follows,but this is tedious process.
SQL> insert into b(ID,Name,rollno,address)select b_seg.nextval,lexcom.a.Name,lex
com.a.rollno,lexcom.a.address from lexcom.a,dual;
3 rows created.
But this is time taking and I had tables which has many columns to be inserted.So is there any other way to solve it and implement insert stmt.
Can we use this format for accesing data from other DB or Schema?
In From clause
database_name.schema_name.table_name
This message for my clarification,we have two schemas MAD_LIVE and MAD_TEST we want to take data from MAD_LIVE to MAD_TEST for particular period of time. way to import data from MAD_LIVE to MAD_TEST for particular period
My SCHEMA MAD_LIVE using from year 2010 till now,we want to take only 2012 data from MAD_LIVE and restore it in MAD_TEST.
How to load data to another schema's table through Sql*loader.
I tried the below things in the control file.
Load data
"
Into table scott.emp
I am unable to drop a snapshot , i tried even from sys, it is giving the following error,
drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists
but, when i try to create a new snapshot with the same name
CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.
Do i have to create indexes again if i drop a partition on a table?
View 6 Replies View RelatedALTER TABLE table_name DROP PARTITION (partition_1000);
ALTER TABLE table_name DROP PARTITION (partition_1001);
...
.........
......
ALTER TABLE table_name DROP PARTITION(partition_1320);(b
it is a delta partition,so trying to remove 320 partitions at once in pl/sql developer for a single table.
Like this i have to remove for more then 15 tables one by one, will this effect the database like filling up the archinve log destination by writing more logs.
kind of problems that i am going to face , as i am doing it on the production box directly.
I want to list the tables in a schema which contains a particular value or data.
View 2 Replies View RelatedI have created different scripts to monitor growth planning of the database my idea was generate a simple output file to a directory and create a report and send it as mail thru mail server. but the client requested me to create new schema and population all the stats i have collected into the tables in this schema .
How to pool all the stats from different databases into this new schema efficiently.
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance) I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.
View 11 Replies View RelatedI am trying to access and modify data of a table of another schema which contains 80,000-90,000 records. My procedure is taking near about 30 mins to complete the operation. faster access and updation of table data.
Details:
I have two schema: TEST and PROD
I am running the below code from TEST Schema.
/* CODE START HERE*/
DECLARE
exc_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_bulk_errors, -24381);
v_block_count NUMBER := 1000;
[Code]....
The above code is taking near about 30mins to process.
I have also tried another approch: Creating a procedure in PROD schema to update COMPONENT_MASTER table and by calling the procedure from above code by passing component code.
/* PROCEDURE CALL FROM ABOCE CODE INTEST SCHEMA*/
PROD.PROCEDURE_TO_UPDATE(v_comp_code);
But still it is taking same time.
has copied one table data from Production to any other schema , know which user has done this ?
View 1 Replies View RelatedI have scenario here.
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
Would like to know:
When you drop a user profile, Oracle automatically assigns the default profile to that user - knowing that no other profile has been assigned to that user.
Does this happen in the same session or after a restart?
A user must have a profile at all times, so if a profile is dropped, then the default profile should be assigned in the same session because if not, then during that session the user has no assigned profile which shouldn't happen?
When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies View Relatedwe are refreshing data base for our application from the base load,Every time when ever there is refresh required we need to drop the users and recreate every thing from the base load. For this we need to kill the sessions and drop the users ,Putting the instance in the restricted mode and refreshing the db.Some time when ever During the killing and dropping process there are some errors like you can not drop the users which is currently connected .
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
[code]...
how to get rid of this
I created an encrypted tablespace for testing. I later dropped it but don't remember if I specified "including contents and datafiles". The tablespace was empty and there are no datafiles for it. However, the information for this dropped tablespace still shows up in v$encrypted_tablespaces. How do I get that lingering information removed?
View 3 Replies View RelatedI'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).
For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?
Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.
I am trying to drop a column from a huge partitioned table (non compressed including partitions). I am working on 11gR2 database for information.
i used below approach
1. alter table <tab_name> set t1 unused column;
2. alter table <tab_name> drop unused columns;
then i got the below error message
ORA-39726: unsupported add/drop column operation on compressed tables
First statement did work i could be able to add another column with the same name but still don't want the unused column on the table.
I am trying to drop a user but i get the following error
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
A couple of questions on this error:
- I did a search on the forum and the thread [URL] appears to have a solution to it by using
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);
What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?
- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?
I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?