SQL & PL/SQL :: How To Add Row Dependencies In Existing Table
Jul 20, 2012How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..
View 2 Replies
ADVERTISEMENT
Find Column Dependencies
Oct 22, 2010I 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?
SQL & PL/SQL :: Hierarchical Query With Many-to-many Dependencies?
Apr 22, 2010I 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]....
SQL & PL/SQL :: Dependencies Between Objects In Database?
Dec 3, 2012Dependencies 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.
Creating Table From Existing Table In Another Schema?
Jan 4, 2009I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
View 4 Replies View RelatedCreate Temp Table From Existing Table
Sep 2, 2010I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
SQL & PL/SQL :: List Of Dependencies For Invalid Objects?
Jun 11, 2013find 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.
Server Administration :: Invalidation Of Dependencies?
Aug 10, 2011In 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 RelatedSQL & PL/SQL :: Partition Of Existing Table
Nov 30, 2011We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.
View 5 Replies View RelatedSQL & PL/SQL :: Update Existing Table
Apr 24, 2012I want to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table
the output will shows like below
empID DeptNo New_column Start_Date
11 7778 1 01-02-2010
11 3400 202-06-2010
25 4444 103-02-2010
25 7775 204-07-2011
25 7777 305-02-2012
30 2223 109-02-2012
the forth one column is in date format and I want to update table with respect to order by the column start_date
Server Utilities :: Remove All Dependencies On Tablespace
Mar 4, 2011I 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 View RelatedSQL & PL/SQL :: How To Find Remote Dependencies Of Database Object
Feb 16, 2010As 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.
SQL & PL/SQL :: Dependencies Of Tables On Package At Column Level
Mar 29, 2010I 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.
Getting Timestamp For Existing Records In Table?
Nov 15, 2010I need to get the timestamp for all the existing records in my table...I am having one user definition field, is this possible?
View 4 Replies View RelatedSQL & PL/SQL :: Modifying Existing Constraint On Table
Apr 1, 2013Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint
Ex
CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;
SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';
ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);
ORA-00933: SQL command not properly ended
Third statement is not executing fine .
SQL & PL/SQL :: Check Existing Entries In Table
Apr 3, 2012I am abolute new in PL SQL and I am working on following topic:
I have to check if an entry exists in a table.
- if it exists ...... do something
- if not ...... simply go on
I tried the following:
select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;
I fetched the result into a variable entries
if entries > 0 .....
MY problem is the following:
If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash
I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!
SQL & PL/SQL :: Cluster Creation On Already Existing Table
Feb 20, 2012I have table property and flat , in both of these tables I have cvp_id common colomn .Now I want to add cluster on this colomn so how can I add cluster to table which is already exists.
View 3 Replies View RelatedSQL & PL/SQL :: Create Partition On Existing Table?
Aug 13, 2010I need to create partition on the non partition table without dropping a table.
Range partition on stage tables and hash partition on fact tables.
SQL & PL/SQL :: CREATE Table From Existing Tables
Oct 31, 2013I have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.
Below is example
TABLE1
ACOL1 ACOL2 ACOL3
A1 A2 A3
B1 B2 B3
C1 C2 C3
TABLE2
BCOL1 BCOL2 BCOL3
11 12 13
21 22 23
31 32 33
COMBINED_TABLE
ACOL1 BCOL2 BCOL3
A1 12 13
B1 22 23
C1 32 33
I tried below query but no luck. It gives below error:
Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum
Error : ORA-01747:"invalid user.table.column, table.column, or column specification"
PL/SQL :: Creation Of A Table After Checking Whether It Is Existing
Apr 22, 2013declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception
when others then
vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception
when others then
dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;
Compress Existing Table Which Is Not Partitioned
Aug 1, 2013Im having table which is of 45M rows table [Not partitioned], Now I want to compress the old data other than last 3Months data, I should not go for partition compress. Rarely some select queries will be fired on that Old data. Now how can I compress that table without affecting the Indexes , Dependencies proc, pkgs, Functions.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"
How To Add Sequence Column To Existing Table With Records
Dec 8, 2012I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04. I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
Partitioning - Switch Partition Key On Existing Table?
Jan 23, 2011I recently started working with legacy code and noticed that some huge tables (5 years worth of data, don't have more details on me right now but can post later if needed) are partitioned based on time sequence number column while majority of queries are done based on time (different column). Queries performance is degrading and I'd like to try to modify partitioning and run some tests to evaluate performance improvement.
My only concern is with so much live data I have to come up with solution on how to switch partitioning with the least impact on applications running 24 x 7. Something you have done in the same situation and it worked?
SQL & PL/SQL :: How To Add Sequence Column To Existing Table With Records
Dec 8, 2012I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04.
I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option"
SQL & PL/SQL :: Adding Column In Middle Of Existing Table
Sep 6, 2010below query.
1) How to add a new column to the existing table's particular position, instead of atlast.
2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?
Possible To Compress Existing Table In Oracle Database
Nov 29, 2012it's possible to compress the existing table in oracle database directly.
View 1 Replies View RelatedSelect Privs On Table To Existing User
Mar 18, 2013I have granted select privs on below tables to an exisiting user.grant select on WEB.ALMSTAT to nms...I would like to view the privs that i have granted to nms user?
*"In production, nms user will need the ability to read the following tables:*
*WEB.ALMSTAT*
*NET.ASSET*
*NET.LOCATIONNAME"*
PL/SQL :: XML Errors While Partitioning Existing Table By DBMS_REDEFNITION
Jan 28, 2013I am trying to partition an existing table through DBMS_REDEFNITION. Following are the steps that I have taken and the error I have got.
1. Creating a table to be partitioned.
CREATE TABLE SO33070_ORIGINAL
(
SERIAL_ID NUMBER(15,0),
INSERTED_TIME DATE DEFAULT SYSDATE,
PRIMARY KEY (SERIAL_ID)
);
Success
2. Checking if the table can be partitioned
DECLARE
v_name VARCHAR2(256);
BEGIN
SELECT sys_context('userenv', 'current_user') INTO v_name FROM dual;
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'SO33070_ORIGINAL', dbms_redefinition.CONS_USE_ROWID);
END;
Success
3. Creating a duplicate table
CREATE TABLE SO33070_NEW
(
SERIAL_ID NUMBER(15,0),
INSERTED_TIME DATE DEFAULT SYSDATE
)
PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
Success
4. Starting the redefnition process
EXEC DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'CDS_USER', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
Success
5. Copying the dependents
DECLARE
num_errors NUMBER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'CDS_USER',
orig_table=>'SO33070_ORIGINAL',
[code]....
SQL & PL/SQL :: Existing Role Claimed To Be Not Existing?
Dec 19, 2011I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.
GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'
[code]....
Convert Existing Table With Varchar Column To A Clob?
May 8, 2013how to convert existing table with varchar column to a clob
View 1 Replies View Related