Flashback table operations are not supported for the SYS user . I have found the above line in one of the article.
I don't know why the SYS user can not able to flash back the table.
While doing scanning of character set, error occurred in between.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8MSWIN1252
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating tables to scan...
. process 1 scanning REINS.RI_TD_EXECUTION_LOG[AAASwXAAAAABwkAAAA]
. process 1 scanning INS.SERVICE_TRANSACTION_LOG[AAAS5hAAAAADWiAAAA]
. process 1 scanning REINS.RI_TD_VOUCHER_DETAILS[AAASx+AAAAAC6KAAAA]
. process 1 scanning REINS.RI_TT_VOUCHER_DETAILS[AAAS0OAAAAAAMiAAAA]
. process 1 scanning CONFSYS.DML_AUDIT_LOG_FOR_DESIGNER[AAASnwAAAAAAsIAAAA]
. process 1 scanning REINS.RI_TD_TRANSACTION_LOG[AAASx0AAAAACsoAAAA]
[code]...
I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);
So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.
create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;
create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;
alter table teta_admin.t_prac flashback archive audit_flash_archive;
and everything works fine but on sys user.
i can query this table using "as of timestamp" clause
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')
but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.
interface user have such sys privs:
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO
and table privs:
SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT
what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.
How to delete flashback log and to release the space?
I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
here's my settings
SYS@boston>SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
ESTIMATED_FLASHBACK_SIZE
------------------------
45195264
SYS@boston>select name, value, issys_modifiable from v$parameter where name='db
_recovery_file_dest_size';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ISSYS_MOD
---------
db_recovery_file_dest_size
9663676416
IMMEDIATE
SYS@boston>select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:Ora102chicagooston_recover
9663676416 1671241216 1103842304 50
SYS@boston>select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG .04 0 1
ARCHIVELOG .16 .01 21
BACKUPPIECE 16.84 11.41 22
IMAGECOPY 0 0 0
FLASHBACKLOG .25 0 6
6 rows selected.
SYS@boston>
I have configured a flash recovery area of 9g yet the system keeps complaining that i have not enough flashback logs?
SPACE_RECLAIMABLE 1103842304
ESTIMATED_FLASHBACK_SIZE 45195264
the space_reclaimable is more than ESTIMATED_FLASHBACK_SIZE, so how is it possible the system always complain not enough space?
[URL].....
Quote:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
it returns me 0, I try to flashback the now defunct former primary db chicago, the system complains. so how much is really enough?
from
[URL]......
Quote:
V$FLASHBACK_DATABASE_LOG
I have read it in books that flashback uses undo data to create the flashback data or to flashback the database to a time in the past.Then, what is the role of archive files in flashback operation. Why it is mandatory to turn on archiving before turning on flashback. Also, if you remove the latest archive files, you can NOT flashback the data to a time in past (Oracle complains of missing archive files).
View 8 Replies View RelatedI had added a new disk into disk group DATA1 with rebalance power of 5, it ran as estimated for about 2 days long, I can see the estimated time down to zero but the process never end.
The command used:
SQL> alter diskgroup DATA1 add disk '<path>' rebalance power 5;
Below is the disk status:
SQL> select group_number, disk_number, total_mb, free_mb, mount_status, header_status, state
from v$asm_disk where group_number=1 order by disk_number;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB MOUNT_S HEADER_STATU STATE
------------ ----------- ---------- ---------- ------- ------------ --------
1 0 1191626 314236 CACHED MEMBER NORMAL
1 1 1191626 314230 CACHED MEMBER NORMAL
1 2 1191626 314232 CACHED MEMBER NORMAL
1 3 1191602 314229 CACHED MEMBER NORMAL
1 4 1191626 314210 CACHED MEMBER NORMAL
1 5 1191626 314218 CACHED MEMBER NORMAL
1 6 1191626 314218 CACHED MEMBER NORMAL
1 7 1191602 314223 CACHED MEMBER NORMAL
1 8 1191626 314216 CACHED MEMBER NORMAL
1 9 1191626 314202 CACHED MEMBER NORMAL
1 10 1191626 314230 CACHED MEMBER NORMAL
[code]....
I've checked and follow the status for a few days more, it still running with EST_MINUTES=0. I think it was hang and never end.During the next few weeks, I've manually restarted the rebalance process by modifying its power to 6, 8, 10
SQL> alter diskgroup DATA1 rebalance power 10;
After every restart, the diskgroup rebalance again but has the same issue, never end.Here is the content in the alert log
NOTE: starting rebalance of group 1/0x9d2529a0 (DATA1) at power 5
Starting background process ARB0
Fri Sep 16 12:01:11 2011
ARB0 started with pid=15, OS id=21066
Starting background process ARB1
Starting background process ARB2
Fri Sep 16 12:01:11 2011
[code]....
As you can see, the rebalance process started 3 days ago, hanging till now
SQL> select group_number, operation, state, power, est_minutes from v$asm_operation;
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
1 REBAL RUN 5 0
I have a table with table_ID, date_created, user_id.
I have sequence, and a BEFORE INSERT trigger, which uses the seq to increment the table_ID by 1, everytime the webpage is saved.
ques:
(1).If i have date_creted defaulted to sysdate in the table, do I need to have it in the trigger?
-- Update create_date field to current system date
:NEW.DATE_CREATED := sysdate;
(2).How can I insert the user_id in the table, each time user SAVE the page ? web page procedure is getting the user info at the beginning. can i add it in the trigger
DECLARE
v_username varchar2(10);
BEGIN
[Code]....
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
I have deinstalled Oracle database, ASM and oracle software. I installed the oracle software, ASM and database. I have seen the error in the ASM trace called
Errors in file /opt/oracle/admin/+ASM/bdump/+asm_gmon_10518.trc:
ORA-29702: error occurred in Cluster Group Service operation
ORA-29702: error occurred in Cluster Group Service operation
I created an externally authenticated user in database. And can login without password with below syntax.
SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"
This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.
connect scott[proxy_user]/password_for_scott@TESTDB
So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?
I have a strange problem when creating a view in user from another user
I have a user called "Cash_tst"
its syntax creation is
-- Create the user
create user CASH_TST
identified by ""
default tablespace CASH
temporary tablespace TEMP
profile DEFAULT
quota unlimited on cash;
-- Grant/Revoke object privileges
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;
-- Grant/Revoke system privileges
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;
I want to create a view
CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO
"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"
I am having a pipeline function that is called from Java interface.The function is suppose to return a list of events.If it takes too long the customer might cancel it.
I would like to catch in a log table all the information of the input param when the request is canceled.I tried to log every time the sqlcode is -1013, if I dbms_output something then it works, but if i try to call a procedure to do logging, it does not work, or if i try to insert directly in a log table also is not working.
Is it possible to catch(log) this kind of exception.
I have used FLASHBACK TABLE option in my oracle 10g database.
It works successfully but the indexes got system generated name after flashback table option.
how i get all the indexes with same name which was before flashback.
one of my user POM create one table using this statement
12:55:59 SQL> create table hello_world (emp_id number(4), emp_name varchar2(30))
;
Table created.
then insert some raw into this hello_world table
12:56:41 SQL> insert into hello_world values (1, 'Bill Gates');
1 row created.
12:57:51 SQL> insert into hello_world values (2, 'Steve Jobs');
1 row created.
13:00:32 SQL> insert into hello_world values (3, 'Chris Eaton');
1 row created.
13:01:19 SQL> commit;
Commit complete.
then i have drop this hello_world table
13:02:08 SQL> drop table hello_world;
Table dropped.;
then i have to connect with sys user
13:03:03 SQL> conn sys as sysdba
Enter password:
13:08:21 SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
13-MAY-11 01.10.21.691000 PM +05:30
[code].....
i flashback my this HELLO_WORLD table but why i cannot Flashback RAW of this table .what can i do if i want to flashback raw of this table which query i have to run,
In my project a user has updated a column with wrong value which had caused some issues.
I wish to find the userid of person who did the update on the table.
There is no trigger on that table
I used the following queries but have not got success
select * from USER_TAB_modifications where table_name like '%XYZ%';
select * from USER_AUDIT_OBJECT ;
I WAS ABLE TO GET WHEN LAST UPDATE TOOK PLACE on table but not userid details
We have installed Oracle RAC 11gR2, installation was all successful.But i cannot see ASMSNMP user to login as ASM enterprise manager .If i tries to login as sys (even after assigning SYSASM role) he cannot login.
Quote:Set Oracle_sid=+ASM1
sqlplus / as sysasm
grant sysasm to sys;
conn sys/*** as sysasm
ORA-01031 insufficient privileges
Whenever I create a table using login A, the table gets created successfully. But when I query the dba_objects, the object owner is different, login B. What might be the reason behind this?
View 9 Replies View RelatedGot following error while droping user
DB:9.2.0.8.0 OS:Windows 2003
SQL> drop user custom cascade;
drop user custom cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21700: object does not exist or is marked for delete
I need to ask about PGA memory. Is this memory for every user or every session
i.e. PGA 200M
I have 2 users with 300 connection with one of them. Is 200M for 300 connection or for every connect.
I need to create one to many user DB link in oracle 10g. Meaning I have a user A in database 1 and I want to access the objects from user B,C,D in database 2, how to create a public database link so that i can have this one to many user access?
View 8 Replies View RelatedHow to determine number of connections establishing from application server to database server for a particular user and also query the user is running in database.
user -- application user created in database.
same user exist in application.
Is there any default way to assign user roles to uses ?
Suppose I want assign to user different roles to different users on views, tables etc.
I wan to implement Access control list & Access request objects (ACL/ARO).
I want to drop some users which are no longer been used .What are the precautions i need to take before i drop users? I have taken logical backup (Export) of users i want to drop.Is there anything i missed out before i drop user?
View 4 Replies View RelatedI just create a user and tried to log in but it gives the following error, why?
SQL*Plus: Release 10.1.0.4.2 - Production on Tue Sep 27 10:57:55 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> create user user1 identified by user1;
User created.
SQL> conn user1
Enter password: *****
ERROR:
ORA-12560: TNS:protocol adapter error
Warning: You are no longer connected to ORACLE.
WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE
ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.
I want whenever a user is created in database the server send me a mail about this.
View 3 Replies View Relatedlast two years i am using oracle 9i in windows 2003 server but now i installed oracle 10g on another server (PC) , i want to connect 9i to 10g with same database and user .
View 3 Replies View RelatedI have login through the sysdba and created dblink and modified the tnsnames.ora for necessary changes. And the it started working correctly and I can able to get the data by selecting it.But when I login with different user that is showing the following error.
ORA-12154: TNS:could not resolve the connect identifier specified
How to resolve this,is there any privileges or permission need to give to that user.
