SQL & PL/SQL :: Truncate Master Table?
May 17, 2013
The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.
1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully..
2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table)
3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table
View 15 Replies
ADVERTISEMENT
Sep 7, 2012
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
View 1 Replies
View Related
Aug 16, 2012
i need to import into table A. Before that i will do a truncate.
I will disable the constraint.
DO i need to disable any triggers on the table before importing?
View 12 Replies
View Related
Aug 2, 2012
I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .
If their is any problem in insert in to table t2 , the truncate command should not work .
Truncate command should work only after successfully insert command .
View 3 Replies
View Related
May 13, 2011
I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
I am using Oracle 11g Release 11.1.0.7.0
View 17 Replies
View Related
Jan 30, 2013
on 11G R2,
We want to grant truncate any table to a user.
How ?
We should create the following procedure ?
1. Create the procedure to truncate the table.
create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)
[Code]....
grant execute on <procedure_name> to <user>Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?
View 6 Replies
View Related
Aug 9, 2013
I couldn't either DROP or TRUNCATE the table partitions that were created. Here are the DDLs and DMLs I'm using.
Create table student(no number(2),name varchar(2)) partition by range(no) (partition
p1 values less than(10), partition p2 values less than(20), partition p3 values less
than(30),partition p4 values less than(40));
Insert into student values(1,'a');
Insert into student values(11,'b');
Insert into student values(21,'c');
Insert into student values(31,'d');
When I do the following query, it returns data.
SELECT * FROM STUDENT PARTITION(p1);
But, when I try to perform any of the following queries, it says invalid partition name.
ALTER TABLE STUDENT DROP PARTITION p4;
ALTER TABLE STUDENT TRUNCATE PARTITION p3;
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
View 9 Replies
View Related
Mar 7, 2013
Our DB is 11g and approximately, how much time it takes and do we need to rebuild indexes after truncate?
View 5 Replies
View Related
Sep 16, 2008
I want to remove the master site which is in the multi master replication environment.
I have a doubt here. When I try to suspend the master activity,I need to give the gname.
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'NAGADMIN');
END;
/
Where gname=master group name.But I have 9 master groups in my database. If i give one of the gname present in the master site will suspend the replication of the entire database from the replication.
eg; i am only giving NAGADMIN gname in the suspend activity script.. I have other gnames like, NAGUSER,NAGAUTH, etc....
View 3 Replies
View Related
May 24, 2012
Our client has this scenario:May 23th 4:00 PM a user truncate a critical table. The customer need to recover the data before truncate.
The recover materials list like this:
1. A cold backup of this database at May 1th.
2. The archive log except May 1th ~ May 15th.
3. Every day exp at 00:30 and 12:00.
Is there any way to recover the data before truncate table with these material?
View 3 Replies
View Related
Nov 20, 2009
Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.
View 6 Replies
View Related
Oct 21, 2012
Any impact is there if is do the following:
ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;
will there be any lock on the table during this operation? DML operations will work without any issue or not?
View 10 Replies
View Related
Jul 17, 2010
My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.
View 6 Replies
View Related
Apr 24, 2013
I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
View 3 Replies
View Related
Feb 16, 2012
I have approximately 1200 transaction to be updated to a master table. There are other columns in the master table but only one column is being updated. I would like to use sqlloader if possible or any other efficient means. Those 1200 record is stored in an excel spreadsheet. The col1 of the excel spreadsheet have to match col1 of the master table inorder for update col2 from the excel spreadsheet. Here is an example of the data. My operation system is HPUX and database is Oracle 10g.
Master table
col1 col2 col3 col 4
4238 susan 56e
5879 h698c rich 12g
7091 joyce 34b
0876 mike 25n
7501 k956b robert 87c
9498 angela 67r
3645 doris 92y
excel spreadsheet
col1 col2
9498 a784r
3645 a784r
4238 a784r
7091 a784r
0876 a784r
View 2 Replies
View Related
Jun 9, 2011
Is there any way to design a form to act like Master Detail from one table which is not normalized?
View 2 Replies
View Related
Nov 16, 2011
i have 60 tables i want to create all columns in single table how ? and how to create master table
can u explain briefly what is master table? and why we are using master table in companies..
View 2 Replies
View Related
Jan 18, 2012
I have two table master and detail i want to update both tables with update command in master i want to update voc_date and in detail i want to update item_code in one command but i am not understand how to control this query for example when i use this command
1). update master set voc_date=sysdate it works very well
2). update detail set item_code='12345' it also work very well but i want to update master,detail table in one query
pls guide me with some query example
View 2 Replies
View Related
Jul 5, 2012
we are creating item_codes in our master table called om_item ,and then at one point of time every day we are shipping these newly created items based on date and tmie stamp as items.sql to our brance office and the person there will run these scripts there.Actually there are two persons involved in this process and i want to remove this manual intervention.All i need is to create a trigger for this insert statements and write it in text file as .sql.
create table om_item (item_code varchar2(20), item_name varchar2(60),item_cr_dt date)
insert into om_item values ('a','aaaa',sysdate);
commit;
insert into om_item values('b','bbbb',sysdate+1);
commit;
View 29 Replies
View Related
Feb 28, 2013
I have a contact table that will be used for more than one entity (Customers, Sites, Suppliers, etc.) Using APEX, when we define the master-detail, we need to add a FK to the Detail table -- otherwise the Master-Detail Forms can not be implemented.
I am looking for a way to defined/implemented tables so that I can utilize the same table (Contacts) for more than one Master-details relationship.
View 3 Replies
View Related
Jan 22, 2013
I have created a wallet (11g R2 OEL 5.5) using the OWM.Tried opening the wallet (encryption_wallet_location set in sqlnet.ora). then while creating a table it said the master encryption key is not present. Have created the master key using the following command.
alter system set encryption key identified by "Password";
Here the strange thing i observed is that when we create a wallet using the OWM, it asks for the password and when i open the same wallet the master key is not created and it allows the master key to be generated with the same password that i have created the wallet in the first place with the OWM, with any other passwords it says that the wallet is not open.
After creating the wallet and creating the master key... I have the following questions, and its becoming quite hard to find the solutions as well.
1. Can we have multiple encryption keys... say i want to encrypt a table or column with one key and other with an another key.
2. How many keys can we have for objects in the table? or can we have only one key and many certificates.
3. wallet created, and encrypted tables present, the wallet is not in auto open mode, but somehow the database open after it is shutdown, here no encrypted tablespaces are present.
4. while creating an encrypted tablespace the default storage (encrypt ) has to be added to the add tablespace clause.
View 2 Replies
View Related
Sep 4, 2012
I am getting error while Full database logical export backup
Physical RAM 8GB
SGA 5632MB
Database info
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Error details
ORA-31694: master table "SYSTEM"."ORCL" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-00604: error occurred at recursive SQL level 3
ORA-21780: Maximum number of object durations exceeded.
ORA-06512: at "SYS.DBMS_METADATA", line 1548
ORA-06512: at "SYS.DBMS_METADATA", line 1585
[code]....
View 6 Replies
View Related
Jul 2, 2013
While exporting on 11g xe on windows 32, i got messageORA-31626: job does not existORA-31633: unable to create master table
"System.Sys_export_schema_06"ORA-06512: at "SYS.DBMS_SYS_ERROR", LINE 95ORA-06512: at "SYS.KUPV$FT", line 1020ORA-01658:
unable to create INITIAL EXTENT FOR segment in tablespace SYSTEM I checked and found there is no pace in table space 600(allocated) 600(used mb) 0(free mb) 1(used) Q1) Is there any table which i can empty to free some space in system schema.Q2) Is there any way to shift some space from other table space to system becuse i have free space inother table space.i could find many segments of system table space, can i delete few of them, if they are not used ?
View 7 Replies
View Related
Sep 24, 2010
ORA-31694: master table "SYS"."SYS_IMPORT_FULL_02" failed to load/unload
ORA-31640: unable to open dump file "D:oradataPSPRODDBdata_pump_dirpowerschool-fri.dmp" for read
ORA-19505: failed to identify file "D:oradataPSPRODDBdata_pump_dirpowerschool-fri.dmp"
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 2192117862)
View 10 Replies
View Related
Mar 6, 2010
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
View 8 Replies
View Related
Apr 9, 2010
i have one question that why oracle not set high water mark when we delete data from a table and commit it, on the other hand it set in case of truncate.both of these statement release physical structure(in case of delete after commiting)
View 7 Replies
View Related
May 8, 2013
I have an Oracle Package with a procedure in which
package with a procedure in which there is a truncate partition, ALTER TABLE table_name TRUNCATE partition_name DROP STORAGE and the all is run with an EXECUTE IMMEDIATE 'alter table ...' .
The point is that the procedure in the package is started from another DB via DB Link (schema USER1) and doesn't work because of lack of privileges.
Instead, if the same procedure is started as a procedure, standalone, not in the package but from the same user (USER1) it works perfectly.
Don't understand why and which privileges must give to the user to run the procedure from inside the package.
View 3 Replies
View Related
Jan 19, 2012
Is it possible to recover a table which is Truncated
View 13 Replies
View Related
Dec 3, 2010
I am using oracle11g. I want to truncate subpartition on specific partion.
I have partition on statewise. Each state partion has 7 day sub partition.
For intance,
Partion TX
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion CA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion IA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Now i want to perform following tasks.
1. Need to truncate TUE sub partiion on TX partition.
2. Need to truncate WED sub partiion on CA partition.
3. Need to truncate SUN sub partiion on IA partition.
How do we do this?
The below statment truncate all TUE partition on all the partitions.
ALTER TABLE TRX_TABLE
TRUNCATE SUBPARTITION TUE;
How do i tuncate specfic sub partition on specific partition?
View 4 Replies
View Related
Mar 10, 2011
I have two tablespace a and a_idx , a is comtaining all tables data and it fixed size is 19000MB and b containing all the index associated with these tables and its size also 19000MB.
but this is am testing database, and we need some space on my unix machine for this i truncate all tables from a tablespace a
and resize the tablespace a with size 1000M with no error.
but as per my understanding after truncating all tables from tablespace a its also release space from tablespace b but its not hapening.
so my questin is how can i reize tablespace B using alter database datafile 'full path of dbf file' resize 1000M.
View 6 Replies
View Related
