How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'
i want to add a column data of datatype number of all the tables at a time in a database..
i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...
I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1; create table t1 (slno number, acc_no number);
drop table t2; create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3; create table t3 (acc_no1 number primary key, acc_name1 varchar2(100)); [code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
select framedquery from ( SELECT CASE WHEN table_name='MT_MEMBERS_SCOPED' THEN ' case when 1>0 then nvl (( select value from MT_MEMBERS_SCOPED where source_code=123499 and member_id= soln_test.member_id and attribute_id= '||attribute_id ||''||'),0) else ''00'' end ' || ATTRIBUTE_NAME ||', ' WHEN table_name='MT_MEMBERS'
[Code]....
My requirement is soln_test table contains 3 records ,for each record need to match member_id in the MT_MEMBERS_SCOPED for the four records in the table.. output should be 3 records in soln_test table + 4 records in SOLN_STAR_MATRIX_123456_TMP1 totally 7 records it means column to row...
if the member_id not exists in both tables it should return '0000' value.
I am getting ORA-39726 error when a dropping a column on a non-compressed table.
SQL> select distinct TABLE_OWNER,COMPRESSION,COMPRESS_FOR from dba_tab_partitions where TABLE_NAME='STM_AE_STMT_HISTORY_DETAIL'; TABLE_OWNER COMPRESSION COMPRESS_FOR ------------------- ---------------------- ------------------- APP_SU DISABLED SQL> ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR; ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR * ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables
I have one table in a particular database and will have to create another table in a different database. I will have to join these two tables from two different DB's.
How should I go about doing this? Is this a good practice? or is it always better to use a single DB?
What are the disadvantages of joining two tables from two different DB's?
The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.
i work on oracle 8i with around 950 tables in my database. when i export or import it gives me the no of rows exported / imported from each table. is it possible to take a print out of the no of rows in each table through a single query .
select count(*) from each table takes a long time , since there are 950 tables.
we are looking to use oracle 11g as a log database using partitioned tables.
- The tables will have only a 3-5 columns of ~varchar(50) size - We are looking at a volumn of ~33million rows (inserts) per day.
1) Will partitioned tables be able to handle this type of volume? 2) if yes, will a Composite Partitioning (using last modified datetime as range) then subparition with range be the best choice?
if this type of volume is too high for 11g, what are some of the alternative products we can use.
I've recieved a recent request wherein the requirement is to swap values between columns across multiple tables in a database.Following is a visual sample of what needs to be done.
I am trying to describe all the tables in a database.We use desc or describe tablename; to describe a table, but what is the command to describe all the tables in a database (i don't need the system tables)
Once i log into sqlplus as a say ABC (SID or HostString) as a user then if i do a desc table name i get column name, data type and null not null etc but i i need that for all the tables in that ABC database..
I have installed oracle 10g software and created Database on RHEL 4. when i run the run.sql its successively done but im not getting tables. whats the prob.
“AUDIT DELETE ANY TABLE BY <username> BY ACCESS” or “AUDIT UPDATE ANY TABLE<username> BY ACCESS”
enable audit for delete and updates for given username/schema. I want to enable auditing on delete and update on my entire database.
Why? And have we tested it in our any of existing setup? I am thinking of “Trigger after delete” but again this logic gets struck at individual tables. It do not work simply once and all for complete database/all users/all schemas
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob 7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
I have seen the below blog abut database driven menu in java. uRL..... look into the below tables and its data below.I have concerned inserting data in the tables and particularly about
ROLE_DETAILS,ROLE_MASTER and EM_MENUMAS data. select level lvl, ID, NAME, lpad(' ', level * 14, ' ') || NAME menu, parent_NAME,nodetype, page_name from em_menumas [code].....
I'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.
declare i INTEGER:=0; flag boolean:= FALSE; vcInsert:='INSERT INTO CRM_DELT_IMPORT ('; if length(i_vcColumn1) > 0 then flag:=TRUE;
[code]....
i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.
if i put this condition vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS') i get the error to_char must be declare.
if i remove this line
i am getting this error ORA-01843: not a valid month
