i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?
following Output needed
userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername
i would like to view all table statructure of a schema in a file. i need to take print out of it for one analysys.
i did like this
exp user/pwd file=dumpfile.dmp full=y rows=n log=logfilename.log
imp user/pwd file=dumpfile.dmp full=y indexfile=indexfile.sql logfile=logfilename.log
but
its look like this i could not format
REM CREATE TABLE "SYSTEM"."DEF$_AQCALL" ("Q_NAME" VARCHAR2(30), "MSGID"
REM RAW(16), "CORRID" VARCHAR2(128), "PRIORITY" NUMBER, "STATE" NUMBER,
REM "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO"
REM TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN"
REM NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" NUMBER,
[Code] ..........
how can i export all table structure as script
note : i have multi schema's not one schema
i use
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM DBA_TABLES u;
but i need it for all schemas
a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.
View 2 Replies View RelatedI have a small question is it possible to find the details of a user who modified the structure of a table, including what command he ran to change the structure of the table?
View 2 Replies View RelatedI want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
has copied one table data from Production to any other schema , know which user has done this ?
View 1 Replies View RelatedI have scenario here.
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
what is the the use of views and synonyms if changes made to them affects the original table contents?It is data insecurity know?
View 2 Replies View RelatedI have got 2 users as user1 and user2.I have used the following statements from user 'user1':
create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;
In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.
create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;
[code]...
why I cannot access that table from procedure?
I want to send mail whenever the schema password is changed by the developers (clients) . I created this trigger to send mail. I am able to know which schema's password is changed but i want know how to get the client details who is changing the password.
Create Trigger Pwd_chg_trigger
before alter
on database
declare
begin
if (ora_dict_obj_type = 'USER') then
DBMS_OUTPUT.PUT_LINE(' ORA_DICT_OBJ_OWNER = ' || ORA_DICT_OBJ_NAME); -- to know which schema
-- using the above value and additional formatting i am sending the mail using my SMTP
end if;
end;
/
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.
I have two users say A and B. I have all the tables,views,indexes, types,procedures,packages etc. User B wants to access all the objects from user A.
View 4 Replies View RelatedI am struck in a requirement to show the organization structure in a Top Down Organizational structure.
Is there any way we can make the well structured report OR form?
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View Related--this for txn details
CREATE TABLE txn_det(
txnid NUMBER PRIMARY KEY,
amount NUMBER,
status varchar2(50),
cust_id NUMBER);
----this for customer details
CREATE TABLE cust_det(
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50),
cust_acc number(15));
--data to insert for customer table
INSERT INTO cust_det VALUES(101,'Miller','12345');
INSERT INTO cust_det VALUES(201,'Scott','45678');
----data to insert for txn table
INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);
INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);
insert into txn_det values('tx0049',78.12,'success',101);
INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);
Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.
How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"
any other way to get it? I need like table name field name datatype
create table my_rows
(
my_envvarchar2(100),
anumber(2),
bnumber(2)
)
/
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
[code]....
The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
I had done following steps,
schemas(toy,toys)
1) i open the session of toy schema
First i taken backup of table
create table bck20121103_himan as select * from himan;
Backup table is created.
After taking the Backup table
delete himan;(deleting the records)
2) i log in to another session(toys)
exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/
3) i log in to toy schema
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y
i tried the above statement it taken so much of time..
Later i tried
I log in to toy session
i rename the table with other name.
later i imported
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y
it's successfully imported.
I am receiving two large export files from a vendor, so I have no control over the contents. I need to import these into our database. The two export files are very similar, except the one has slightly differenet columns in it. So, export file 1 may have a table:
COLUMN_A
COLUMN_B
COLUMN_C
The second file may have:
COLUMN_A
COLUMN_B
COLUMN_D
At the destination, I have a table that has:
COLUMN_A
COLUMN_B
COLUMN_C
COLUMN_D
Is there a parameter that would let me interchangably import either (or both) files into this destination table? This is my first attempt at data pump - but I know using import this has caused me issues. Not sure if the same limitations exist? Will the missing columns cause it to fail?
my working is relating with PUMP of oracle.
I would like to use command, for ex:
expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp COMPRESSION=ALL LOGFILE=export.log SCHEMAS=hr
But some tables in Schema HR, I don't want to export data, just only need table structure.
we have daily partitioned table, and for backup we are using data pump (expdp). we policy to drop partition after backup (archiving).
we have archived dump files for 1year, few days back developer made changes with table structure they added one new column to table.
Now we are unable to restore old partitions is there a way to restore partition if new column added / dropped from currect table.
know the process of exporting only the table structure of a Database without the actual content of it.
Note:: I don't know how many tables are present in the DB.
create trigger on certain column for table structure.
SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....
I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .
Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)
Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column
I have a requirement to be coded like this:
A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,
means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type
func f return pl_tab
as
...
end;
note : pl_tab_type will vary for each run of function f
i.e.,for example, pl_tab_type can be changed to as follows:
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))
how to return pl/sql table of dynamic type from func,
does a non schema user have to append schema prefix for every database object ?
View 7 Replies View RelatedI have one use "A" . I would like to rename user "A" to "B". how its possible ?
View 11 Replies View RelatedI want to free up some space , as i have created some testing users and schema.
View 3 Replies View RelatedI want to find out all index of schema user X. what will be the query? t will be like below schema name index name
View 8 Replies View Related