SQL & PL/SQL :: Modify Supertype Without Dropping / Recreating Its Subtypes?
Oct 2, 2013is there a way to modify supertypes without dropping / recreating its subtypes?
View 6 Replies
ADVERTISEMENT
Dropping Users And Recreating?
Jun 18, 2012we 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
Change Start With Value On A Sequence Without Dropping And Recreating It?
Jul 26, 2010Is it possible to change the start with value on a sequence without dropping and recreating it?
View 3 Replies View RelatedXE :: Subtypes Created Incorrectly As Types In 11G R2?
Jun 1, 2013I'm trying to determine why subtype creation is not working in 11G R2 XE, while it works in 11G R2 EE.
Environment is XE 11.2.0.2 on Windows 7 X64 (yes, not supported but I don't have anywhere else to try this).
Here is a test case, using the SYSTEM account as that is what the developers use (yes, terrible practice) :
--type
-- this works, creates the type
CREATE OR REPLACE TYPE [schema].prod_type AS OBJECT (
pid INT,
pprice NUMBER,
MEMBER PROCEDURE display(pid IN NUMBER))
NOT INSTANTIABLE NOT FINAL;
[code]....
I've tried adding the schema name after UNDER, e.g. UNDER [schema].prod_type with the same result.
SQL & PL/SQL :: Column Validation For Types And Storing Data For Subtypes?
Apr 3, 2010I have following structures:
SQL> CREATE TYPE address_typ AS OBJECT (line1 VARCHAR2(20), city VARCHAR2(20), COUNTRY VARCHAR2(20));
2 /
Type created.
SQL> CREATE TYPE phone_typ AS OBJECT (area_cd VARCHAR2(10), phone# VARCHAR2(10));
2 /
Type created.
SQL> CREATE TYPE phone_list_typ AS TABLE OF phone_typ;
[code]...
My questions are:
1. I have created an object table person_obj of parent type. I have not created object tables of salesperson_typ and customer_typ and want to store data related to both in person_obj. What is the difference between storing data in an object table of parent type and object table of subtypes? In which case should I consider storing data of all subtypes in an object table of parent type instead of object tables of individual subtypes?
2. Second question is regarding validating the column values before the creation of object instance. Suppose before creating an instance of salesperson_typ, I want to check if salary is NOT NULL and greater than 0. I guess constructor is the only option to achieve this but how full-proof it is if constructors are used for this purpose? Is there any other alternate way?
SQL & PL/SQL :: Recreating Existing Trigger
Nov 28, 2011I am trying to update an existing trigger in my Prod DB, but it may be possible that while recreating the trigger,existing trigger might be under any transaction.
So what could be the possible scenarios? Will I be throwing any error like ORA-0054: resource busy or any other error or what? If it does what could be possible escape ways apart from downtime.
Data Guard :: Recreating Physical Standby
Jun 4, 2010I have an issue whereby our standby instance was down for a couple of days and when it came back online we found an archivelog file was missing, so recovery only happened up to a point. It appears that the archivelog was deleted therefore we are unable to continue with this standby. We are now planning to rebuild the standby database. As far as i know all of the steps originally performed need not be performed to complete this task.
1. Shutdown standby
2. Delete all datafiles & redo logs
3. Copy over datafiles to respective directories
4. Copy online logs and archivelogs to standby.
5. Create a control file for standby database
6. Startup mount standby database
7. start redo apply
SQL & PL/SQL :: How To Modify Partition
Nov 16, 2011There is a customer_account table. It is partitioned by Range. In one of the partition, the range value is given wrongly, and irrelevant data sits in that partition.
How can i modify the range partition value and re-arrange the data ?
SQL & PL/SQL :: Row Modify In Table?
Apr 12, 2013is there any way we can find out which row is updated in oracle table if we don't have any date/time column in table.
let say.
Create table T1 (ename varchar2(200), age number);
insert into T1 values ('A',20);
insert into T1 values ('B',30);
insert into T1 values ('C',40);
insert into T1 values ('D',50);
commit;
update t1 set age=50 where ename='A'
commit;
looking some oracle query which identify which record is update in table in given time period.
Modify Trigger Into Procedure
Sep 12, 2011Moidfy trigger into procedure
I am quite a beginner One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).
I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.
I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.
create or replace
TRIGGER TRG_RATING_EXP_DLS
BEFORE DELETE OR UPDATE OR INSERT
OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALI GNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODU CT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER _ACCOUNT_ID
ON RATING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
[Code]...
-------------------
Don't play what's there, play what's not there
SQL & PL/SQL :: How To Modify Column Data
Jun 8, 2012i have one table emp in this table already data exist now i have to modity this existing column data so how can i do this
example:existing emp table
emp_no ename
1 dk patel
2 sk patel
3 jk patel
now i want to change this ename column data i want output like this below table
emp_no ename
1 dk
2 sk
3 jk
how can i do this.
SQL & PL/SQL :: Modify Number Data?
Mar 16, 2011We have table with number as datatype which starts with number 10 like 1056723. Here we need to update the first 2 digits which is 10 to 04.Digit 10 will be always constant across all the data.
select * from temp;
id
----------
1056723
After update, it should be
id
----------
0456723
What function in pl/sql should be used for achieve the desired results.
SQL & PL/SQL :: Modify BLOB Data
Dec 24, 2010I have a table containing BLOB column which stores scanned images. Due to an application error, few extra data was padded with BLOB data and now we want to remove it.
The table count will be near to 10 million rows.
We need to remove data from 161byte to 167byte of the blob data. I tried to do with DBMS_LOB.ERASE.But it will create blank spaces for the removed data. Here we need to reduce the size of BLOB data by 6 bytes by removing data from 161bytes to 167 bytes.
How To Modify Existing Spfile
Jul 15, 2013I need to know how to modify the spfile - I know how to create it from scratch (from pfile to spfile), but need to modify an existing spfile.
View 3 Replies View RelatedSQL & PL/SQL :: Dropping A Snapshot
Apr 13, 2008I 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.
PL/SQL :: Dropping A Partition?
Jul 7, 2012Do i have to create indexes again if i drop a partition on a table?
View 6 Replies View RelatedPL/SQL :: Dropping Partitions At Once
Nov 2, 2012ALTER 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.
Modify One Table To Trigger Other Tables
Aug 20, 2012We are running an Oracle 10g server.
We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.
I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.
I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.
SQL & PL/SQL :: Can Modify Oracle Data Dictionary
Feb 6, 2011can i modify oracle data dictionary
View 6 Replies View RelatedSQL & PL/SQL :: How To Alter Reference Partition (add Or Modify)
Apr 18, 2011I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.
CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,
[Code]...
Security :: Modify Profile To Use Old Password?
Aug 23, 2010I have a database in which a user xxxx is assigned a password 'bbbbb'.I want to change the password to the one which was used before which was 'aaaaa'.But when I change the password it was saying "Password cannot be Reused".So I checked in user profile and found out that password_reuse_time=unlimited and password_reuse_max=5.
So what I did was change the password 6 times to something else(Since it is 5) and then tried changing it to 'aaaaa' but still it is saying "Old password cannot be reused".
Reports & Discoverer :: Modify REP File
Oct 19, 2010I am working on Oracle reports 6i. Just for R&D purpose I have opened REP file in notepad. In the notepad file I could find the queries which are there in the data model.
I did a small addition to the query and saved it in the same location. I tried to open the modified REP file from RWRUN60, but it is throwing me error saying "REP-0110- Unable to open file".
Original Query in data model:
SELECT empno, sal, job, hiredate
FROM emp;
Modified Query:
SELECT empno, sal, job, hiredate
FROM emp
WHERE deptno in (10,30);
Dropping A User Profile
Jun 9, 2010Would 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?
SQL & PL/SQL :: Dropping A Procedure During Execution
Apr 26, 2012When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies View RelatedDropping Encrypted Tablespace
Mar 29, 2013I 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 RelatedSecurity :: Audit Update / Modify And Insert
Oct 18, 2011I am using 'Novell Sentinel Log Manager' to collect/fetch logs from my Oracle 11g R2.To enable auditing, first I did following:
login as sys, then
SQL> create user testuser identified by "testuser";
SQL> grant connect to testuser
SQL> grant dba to sharf
SQL> grant CREATE SESSION to testuser;
SQL> grant select on v_$session to testuser;
SQL> grant select on v_$version to testuser;
SQL> grant select on SYS.DBA_AUDIT_TRAIL to testuser;
SQL> grant select_catalog_role to testuser;
SQL> grant select any dictionary to testuser;
Now logon/logof of user 'testuser' are logged , as well as if testuser drops a table or creates a table, its also logged . but when 'testuser' insert a new record, this information does not logged ;( while I need to know exactly what was added SQL> insert into emp (empid, name, salary) values (10002, 'Ron', 6000)
likewise if 'testuser' modify/update an existing record it also does not logged.
SQL> update emp set salary=700 where empid=10001;
which sql statements I have to execute to start auditing 'insert' and 'update', so that I know what was added/inserted and exactly what was updated/ changed/modify by user 'testuser'.
Dropping Column From Huge Table
Aug 9, 2012I 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.
SQL & PL/SQL :: Dropping Users - Queue Tables
Dec 9, 2010I 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?
SQL & PL/SQL :: Dropping Unused Columns In A Partition?
Jan 21, 2003I 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?
SQL & PL/SQL :: Does Dropping Schema Purges Data
Oct 21, 2011I 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?