i'm just trying to find out DML Statements, that where executed on a special table at a defined time. By googeling i just found a statement to find out the last DML, that was executed on a table or something else. But i want to look into the past. How can i do this?
View 16 RepliesMy query is "How to find all special characters like (%$*&@,;'/+- etc. in a single query?"
e.g.
A_MIR
A%SIM
A*SIM
A)SIM
There two users a and b,and the table b.test_part.And one procedure under a ,text like below:
create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;
now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!
I have created a table with space by mistake.how to drop that..?
QL> select * from cat where table_name like 'OM_DW_RTNORD%';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE
[code]....
no rows selected
SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character
I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
CREATE TABLE CHECK_SPL_CHARAC
(ADDRESS VARCHAR2(100));
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC%ABC');
INSERT INTO CHECK_SPL_CHARACVALUES ('ABC_ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC?ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC*ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('1234');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('AB C');
INSERT INTO CHECK_SPL_CHARAC VALUES ('12 34');
COMMIT;
I need to get the first 4 rows alone in the sql output. I do not wan to get the last 2 rows with space.
I have to copy a table from oracle 8 to oracle 10
- dblink don't work between 8 - 10
- can I dump table ?
- when I try to use sqlload via TXT file, I have error messages due to a column containing 'special' caracters (
,..) and line too long (splitted in several rows)
how can I do ?
A query to find the table fragmentation.
View 1 Replies View Relatedi have table like TWRCHAIN_WS_SUM. it is regularly updated by another object like procedure, function. how can i find which object is updating the particular table across the database.
View 4 Replies View Relatedwhy am i getting this?
SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');
TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD
but the table exists
SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;
COUNT(*)
----------
3704
SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';
no rows selected
why is it showing no rows even if the table exists
I have written a script to get count of each tables of a schema.
DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;
[Code].....
SQL> select count(*) from tab;
COUNT(*)
----------
63
I need to find out how many rows in each table.I've tried with cursor.
To find out column in which table it is located.
View 1 Replies View RelatedWith Data in a table, is it possible to find out the table name and column name? Means i know the account number (data) of the table and with this data i want to find out column name and table name.
View 3 Replies View RelatedI want to know constraint name of a particular table if it exists.Here are the queries that i tried to find the constraint name of a particular table but the results were 'no rows selected'.
Queries:
select constraint_name from user_cons_columns where table_name='x';
select constraint_name from user_constraints where table_name='x';
I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_
---------
20-MAR-12
SQL> TRUNCATE TABLE PREM;
Table truncated.
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_
---------
10-APR-12
Note: With out enable the auditing I want to know that .
i have a employee table .i want to find out duplicate name from this table.so i write following query
select empname from employee
having count(empname) >1
group by empname
i find the result but problem is how i can find out the same name if it have any space like ramkumar ram kumar both are same but above query did not.give this data. how i can solve it
I've written the code (see below) and after run I get an error:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
I don't know how to find out the wrong value from table. How to find it out?
DECLARE
TYPE rowids IS TABLE OF ROWID;
r1 rowids;
type t_varchar is table of varchar2(50);
n1 t_varchar ;
cursor c1 is select e.rowid rid,msisdn_displayed
from the_table
where contract_id is not null;
BEGIN
OPEN c1;
[code].........
I have two table in the database. I want to find the maximum routine date for a case_id when the lock_date is between 20 jan to 29 jan 2012 .
But I am not getting actual output (Output routine_date should be "28-jan-2012" but I m getting "31-jan-2012")
Master
case_id lock_date
101 23-jan-2012
101 24-jan-2012
102 27-jan-2012
102 29-jan-2012
101 30-jan-2012
101 29-jan-2012
Routine (for routine work)
case_id routine_date
101 23-jan-2012
103 28-jan-2012
102 21-jan-2012
102 29-jan-2012
101 21-jan-2012
101 28-jan-2012
101 31-jan-2012
select m.case_id, r.routine_date from master m, routine r,
(select case_id, max(routine_date) from routine group by case_id) rr
where m.case_id=r.case_id
and m.case_id=rr.case_id
and r.routine_date=rr.routine_date
I've got plenty of sequences. How can I identify that what are the sequences acting on a table?
View 8 Replies View RelatedI have a table where there is no sequence maintained and there is no time stamp column to track the inserted record.How do I find the latest inserted record in the table.
View 5 Replies View RelatedI want to know the DDL statements executed on a table. Example:
i have a table test1 with structure as below: test1 (aa number, bb varchar2(10))
After some period of time, one DDL statement to alter the column is issued.
alter table test1 modify (bb varchar2(30));
so new table structure is: test1(aa number, bb varchar2(30))
Basically i would like to find these 2 DDL statements executed on this table - test1, so that i compare which column get modified and what modification done.
Is this information stored in any data dictionary table?
note - audit, flashback option not enabled in this database.
1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.
P/s: I don't have OEM installed so I must use SQL command
Example of dropping the index
ALTER TABLE table_name
drop CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
drop CONSTRAINT supplier_pk;
I need the column name and table name which is having the particular value in the whole database.
E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.
How to find out the base table of a synonym?
View 3 Replies View Relatedi need an sql query or procedure to find out table name based on the column name..
View 4 Replies View Relatedi need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:
EMP
EMP_1
EMP_2
EMP_3
EMP_4
i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.
How to find out the required column from user tables using query ?
View 6 Replies View Relatedi want a simple query to find out for example the highest age in the table.
View 9 Replies View Relatedsomebody has created the table , who had created the table Note: not required the schema name,
View 4 Replies View RelatedI am using an Oracle Forms application. When you click a check-box in the form and save it,a record is inserted to a log table, they say. The DB schema has over 1000 tables and over50 log tables. Is there way using the data dictionaly to find out which table was inserted tomost recently? I don't have access to the Forms code. My goal is to find out the table that the Form inserted the log entry to.
View 7 Replies View Related