OraFAQ Forum: SQL & PL/SQL » SYS_GUID FUNCTION RETURNING SAME VALUE

Home » SQL & PL/SQL » SQL & PL/SQL » SYS_GUID FUNCTION RETURNING SAME VALUE (Oracle 11g Enterprise Edition Release 11.2.0.3.0 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SYS_GUID FUNCTION RETURNING SAME VALUE [message #644906]

Thu, 19 November 2015 12:20

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Hello,

I have a function called "newguid" which is used everywhere in the application which returns sys_guid() value. Recently we found that it is returning same id & causing problem.

We are having this problem in testing server, but the same function is working fine (fetching different values) in production database. Help me on this please.

CREATE OR REPLACE FUNCTION NEWGUID
RETURN RAW
AS
BEGIN
RETURN SYS_GUID();
END NEWGUID;

SQL> select newguid from dual
2 /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

SQL>

1. Production & test database versions are same.
2. If I run sys_guid function (select sys_guid()) from dual) is returning different values every time
3. If I run the function "newguid" (select newguid()) from dual) is returning same value again and again.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644907 is a reply to message #644906]

Thu, 19 November 2015 12:39

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Like:

SQL> CREATE OR REPLACE FUNCTION NEWGUID
  2  RETURN RAW
  3  AS
  4  BEGIN
  5  RETURN SYS_GUID();
  6  END NEWGUID;
  7  /

Function created.

SQL> select newguid from dual;
NEWGUID
---------------------------------------------
0EB473C7B75A45D8BBD7E70D81ECE1F1

1 row selected.

SQL> /
NEWGUID
---------------------------------------------
260D0E1A87FC4BE2B1BE5762C62C6678

1 row selected.

SQL> /
NEWGUID
---------------------------------------------
BAFE2558372D4964B4C0249534C0DCB3

1 row selected.

SQL> /
NEWGUID
---------------------------------------------
94AB3ECF577B4B2985E5DB5D8BB957A6

1 row selected.

SQL> @v

Version Oracle : 11.2.0.1.0

Post the result of "show parameter result".

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644908 is a reply to message #644907]

Thu, 19 November 2015 13:21

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Thanks Michel. It is working fine for me in production database. Am not sure what database settings do I need to compare between two (test & prod).

I am doing exactly the same steps you did but it is returning same value. Any suggestion please?

SQL> SELECT NEWGUID FROM DUAL
2 /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24E6B70E01F52B84E053F3F0558C63BB

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644909 is a reply to message #644908]

Thu, 19 November 2015 13:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

When 2 DB's are EXACTLY the same, they produce the same results.
When you see different results then by definition something is different.
Only you can compare & contrast your two DB's, since the problem is local to only you.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644910 is a reply to message #644908]

Thu, 19 November 2015 13:30

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You neither post in the way I mentioned and pointed you to nor post the result of the command I gave.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644911 is a reply to message #644910]

Thu, 19 November 2015 13:43

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

SQL>
SQL> CREATE OR REPLACE FUNCTION NEWGUID
2 RETURN RAW
3 AS
4 BEGIN
5 RETURN SYS_GUID();
6 END NEWGUID;
7 /
Function created

SQL> select newguid from dual;
NEWGUID
--------------------------------------------------------------------------------
24EAF1833982CA1FE053F3F0558CDE50

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24EAF1833981CA1FE053F3F0558CDE50

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24EAF1833981CA1FE053F3F0558CDE50

SQL> /
NEWGUID
--------------------------------------------------------------------------------
24EAF1833981CA1FE053F3F0558CDE50

SQL>

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
PL/SQL Release 11.2.0.3.0
CORE 11.2.0.3.0
TNS for Linux: Version 11.2.0.3.0
NLSRTL Version 11.2.0.3.0

First & second guid results are different. 2nd,3rd &4th guid results are same.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644912 is a reply to message #644911]

Thu, 19 November 2015 13:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 19 November 2015 19:39

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...
Post the result of "show parameter result".

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644913 is a reply to message #644912]

Thu, 19 November 2015 14:45

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Sorry Michel. Please find the output of show parameters below.

SQL> SHOW PARAMETERS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
_ksmg_granule_size integer 16777216
_memory_imm_mode_without_autosga boolean FALSE
_trace_files_public boolean FALSE
active_instance_count integer
aq_tm_processes integer 1
archive_lag_target integer 0
asm_diskgroups string
asm_diskstring string
asm_power_limit integer 1
asm_preferred_read_failure_groups string
audit_file_dest string /MT/log/audit
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string NONE
awr_snapshot_time_offset integer 0
background_core_dump string partial
background_dump_dest string /MT/log/diag/rdbms/MT/MT/trace
backup_tape_io_slaves boolean FALSE
bitmap_merge_area_size integer 1048576
blank_trimming boolean FALSE
buffer_pool_keep string
buffer_pool_recycle string
cell_offload_compaction string ADAPTIVE
cell_offload_decryption boolean TRUE
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
circuits integer
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
clonedb boolean FALSE
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
commit_logging string IMMEDIATE
commit_point_strength integer 220
commit_wait string
commit_write string
compatible string 11.2.0.0.0
control_file_record_keep_time integer 7
control_files string /MT/oradata/data01/control01.ctl, /MT/oradata/data02/control02.ctl, /MT/oradata/data03/control03.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
core_dump_dest string /MT/log/diag/rdbms/MT/MT/cdump
cpu_count integer 4
create_bitmap_area_size integer 8388608
create_stored_outlines string
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_buffers integer 0
db_block_checking string full
db_block_checksum string TYPICAL
db_block_size integer 8192
db_cache_advice string ON
db_cache_size big integer 5008M
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
db_domain string
db_file_multiblock_read_count integer 32
db_file_name_convert string
db_files integer 1024
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440
db_keep_cache_size big integer 0
db_lost_write_protect string NONE
db_name string MT
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_recycle_cache_size big integer 0
db_securefile string PERMITTED
db_ultra_safe string OFF
db_unique_name string MT
db_unrecoverable_scn_tracking boolean TRUE
db_writer_processes integer 2
dbwr_io_slaves integer 0
ddl_lock_timeout integer 0
deferred_segment_creation boolean TRUE
dg_broker_config_file1 string /MT/oracle/product/112/dbs/dr1TERAHT.dat
dg_broker_config_file2 string /MT/oracle/product/112/dbs/dr2TERAHT.dat
dg_broker_start boolean FALSE
diagnostic_dest string /MT/log
disk_asynch_io boolean TRUE
dispatchers string
distributed_lock_timeout integer 60
dml_locks integer 4400
dst_upgrade_insert_conv boolean TRUE
enable_ddl_logging boolean FALSE
event string
fal_client string
fal_server string
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
file_mapping boolean FALSE
fileio_network_adapters string
filesystemio_options string SETALL
fixed_date string
gcs_server_processes integer 0
global_context_pool_size string
global_names boolean FALSE
global_txn_processes integer 1
hash_area_size integer 131072
hi_shared_memory_address integer 0
hs_autoregister boolean TRUE
ifile file
instance_groups string
instance_name string MT
instance_number integer 0
instance_type string RDBMS
java_jit_enabled boolean TRUE
java_max_sessionspace_size integer 0
java_pool_size big integer 160M
java_soft_sessionspace_limit integer 0
job_queue_processes integer 10
large_pool_size big integer 160M
ldap_directory_access string NONE
ldap_directory_sysauth string no
license_max_sessions integer 0
license_max_users integer 0
license_sessions_warning integer 0
listener_networks string
local_listener string LISTENER_TERAHT
lock_name_space string
lock_sga boolean FALSE
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/MT/arch MANDATORY
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string TERAHT_%r_%t_%s.arc
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 63037440
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 3600
log_checkpoints_to_alert boolean TRUE
log_file_name_convert string
max_dispatchers integer
max_dump_file_size string 20480
max_enabled_roles integer 150
max_shared_servers integer
memory_max_target big integer 0
memory_target big integer 0
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string $
nls_date_format string DD-MON-RR
nls_date_language string AMERICAN
nls_dual_currency string $
nls_iso_currency string AMERICA
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
nls_sort string BINARY
nls_territory string AMERICA
nls_time_format string HH.MI.SSXFF AM
nls_time_tz_format string HH.MI.SSXFF AM TZR
nls_timestamp_format string DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string DD-MON-RR HH.MI.SSXFF AM TZR
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size big integer 0
open_cursors integer 1500
open_links integer 4
open_links_per_instance integer 4
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean FALSE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
os_authent_prefix string
os_roles boolean FALSE
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 160
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
permit_92_wrap_format boolean TRUE
pga_aggregate_target big integer 2000M
plscope_settings string identifiers:none
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
pre_page_sga boolean FALSE
processes integer 900
processor_group_name string
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
rdbms_server_dn string
read_only_open_delayed boolean FALSE
recovery_parallelism integer 0
recyclebin string OFF
redo_transport_user string
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
replication_dependency_tracking boolean TRUE
resource_limit boolean FALSE
resource_manager_cpu_allocation integer 4
resource_manager_plan string
result_cache_max_result integer 5
result_cache_max_size big integer 12960K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0
resumable_timeout integer 0
rollback_segments string
sec_case_sensitive_logon boolean TRUE
sec_max_failed_login_attempts integer 10
sec_protocol_error_further_action string CONTINUE
sec_protocol_error_trace_action string TRACE
sec_return_server_release_banner boolean FALSE
serial_reuse string disable
service_names string MT
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 1372
sga_max_size big integer 12000M
sga_target big integer 12000M
shadow_core_dump string partial
shared_memory_address integer 0
shared_pool_reserved_size big integer 200M
shared_pool_size big integer 1264M
shared_server_sessions integer
shared_servers integer 0
skip_unusable_indexes boolean TRUE
smtp_out_server string
sort_area_retained_size integer 0
sort_area_size integer 65536
spfile string /MT/oracle/product/112/dbs/spfileTERAHT.ora
sql92_security boolean TRUE
sql_trace boolean FALSE
sqltune_category string DEFAULT
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
star_transformation_enabled string FALSE
statistics_level string TYPICAL
streams_pool_size big integer 112M
tape_asynch_io boolean TRUE
thread integer 0
timed_os_statistics integer 0
timed_statistics boolean TRUE
trace_enabled boolean TRUE
tracefile_identifier string
transactions integer 1100
transactions_per_rollback_segment integer 5
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
use_indirect_data_buffers boolean FALSE
use_large_pages string ONLY
user_dump_dest string /MT/log/diag/rdbms/MT/MT/trace
utl_file_dir string
workarea_size_policy string AUTO
xml_db_events string enable

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644914 is a reply to message #644913]

Thu, 19 November 2015 14:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 19 November 2015 20:49

Michel Cadot wrote on Thu, 19 November 2015 19:39

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...
Post the result of "show parameter result".

Can't you read?
I now know the reason of your problem but I'm waiting you follow the rules and post what I ask in the way we request.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644915 is a reply to message #644914]

Thu, 19 November 2015 15:02

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Hi Michel, Just found that option to upload the file. Please find the attached output in csv format. Thank you.

Attachment: SHOW_PARAMETERS.csv
(Size: 11.88KB, Downloaded 1097 times)

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644916 is a reply to message #644915]

Thu, 19 November 2015 15:07

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

The cause your problem is absolutely clear. If you run the command MC told you to run.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644917 is a reply to message #644916]

Thu, 19 November 2015 16:27

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Hi, Please find the attached plain text file output of show parameter results. Thank you.

Attachment: show-parameters.txt
(Size: 19.08KB, Downloaded 1102 times)

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644918 is a reply to message #644917]

Thu, 19 November 2015 17:12

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Hi, Below is the code. It is returning the same id every time when I run this function.

CREATE OR REPLACE FUNCTION NEWGUID
RETURN RAW
AS
BEGIN
RETURN SYS_GUID();
END NEWGUID;
/

select newguid from dual;
24E6B70E01F52B84E053F3F0558C63BB

select newguid from dual;
24E6B70E01F52B84E053F3F0558C63BB

select newguid from dual;
24E6B70E01F52B84E053F3F0558C63BB

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644919 is a reply to message #644918]

Thu, 19 November 2015 17:13

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Here is the output of show parameters result.

SQL> SHOW PARAMETERS
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
_ksmg_granule_size                   integer     16777216
_memory_imm_mode_without_autosga     boolean     FALSE
_trace_files_public                  boolean     FALSE
active_instance_count                integer     
aq_tm_processes                      integer     1
archive_lag_target                   integer     0
asm_diskgroups                       string      
asm_diskstring                       string      
asm_power_limit                      integer     1
asm_preferred_read_failure_groups    string      
audit_file_dest                      string      /MT/log/audit
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string      
audit_trail                          string      NONE
awr_snapshot_time_offset             integer     0
background_core_dump                 string      partial
background_dump_dest                 string      /MT/log/diag/rdbms/MT/MT/trace
backup_tape_io_slaves                boolean     FALSE
bitmap_merge_area_size               integer     1048576
blank_trimming                       boolean     FALSE
buffer_pool_keep                     string      
buffer_pool_recycle                  string      
cell_offload_compaction              string      ADAPTIVE
cell_offload_decryption              boolean     TRUE
cell_offload_parameters              string      
cell_offload_plan_display            string      AUTO
cell_offload_processing              boolean     TRUE
circuits                             integer     
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
clonedb                              boolean     FALSE
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string      
commit_logging                       string      IMMEDIATE
commit_point_strength                integer     220
commit_wait                          string      
commit_write                         string      
compatible                           string      11.2.0.0.0
control_file_record_keep_time        integer     7
control_files                        string      /MT/oradata/data01/control01.ctl, /MT/oradata/data02/control02.ctl, /MT/oradata/data03/control03.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
core_dump_dest                       string      /MT/log/diag/rdbms/MT/MT/cdump
cpu_count                            integer     4
create_bitmap_area_size              integer     8388608
create_stored_outlines               string      
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_block_buffers                     integer     0
db_block_checking                    string      full
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
db_cache_advice                      string      ON
db_cache_size                        big integer 5008M
db_create_file_dest                  string      
db_create_online_log_dest_1          string      
db_create_online_log_dest_2          string      
db_create_online_log_dest_3          string      
db_create_online_log_dest_4          string      
db_create_online_log_dest_5          string      
db_domain                            string      
db_file_multiblock_read_count        integer     32
db_file_name_convert                 string      
db_files                             integer     1024
db_flash_cache_file                  string      
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     1440
db_keep_cache_size                   big integer 0
db_lost_write_protect                string      NONE
db_name                              string      MT
db_recovery_file_dest                string      
db_recovery_file_dest_size           big integer 0
db_recycle_cache_size                big integer 0
db_securefile                        string      PERMITTED
db_ultra_safe                        string      OFF
db_unique_name                       string      MT
db_unrecoverable_scn_tracking        boolean     TRUE
db_writer_processes                  integer     2
dbwr_io_slaves                       integer     0
ddl_lock_timeout                     integer     0
deferred_segment_creation            boolean     TRUE
dg_broker_config_file1               string      /MT/oracle/product/112/dbs/dr1MT.dat
dg_broker_config_file2               string      /MT/oracle/product/112/dbs/dr2MT.dat
dg_broker_start                      boolean     FALSE
diagnostic_dest                      string      /MT/log
disk_asynch_io                       boolean     TRUE
dispatchers                          string      
distributed_lock_timeout             integer     60
dml_locks                            integer     4400
dst_upgrade_insert_conv              boolean     TRUE
enable_ddl_logging                   boolean     FALSE
event                                string      
fal_client                           string      
fal_server                           string      
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
file_mapping                         boolean     FALSE
fileio_network_adapters              string      
filesystemio_options                 string      SETALL
fixed_date                           string      
gcs_server_processes                 integer     0
global_context_pool_size             string      
global_names                         boolean     FALSE
global_txn_processes                 integer     1
hash_area_size                       integer     131072
hi_shared_memory_address             integer     0
hs_autoregister                      boolean     TRUE
ifile                                file        
instance_groups                      string      
instance_name                        string      MT
instance_number                      integer     0
instance_type                        string      RDBMS
java_jit_enabled                     boolean     TRUE
java_max_sessionspace_size           integer     0
java_pool_size                       big integer 160M
java_soft_sessionspace_limit         integer     0
job_queue_processes                  integer     10
large_pool_size                      big integer 160M
ldap_directory_access                string      NONE
ldap_directory_sysauth               string      no
license_max_sessions                 integer     0
license_max_users                    integer     0
license_sessions_warning             integer     0
listener_networks                    string      
local_listener                       string      LISTENER_MT
lock_name_space                      string      
lock_sga                             boolean     FALSE
log_archive_config                   string      
log_archive_dest                     string      
log_archive_dest_1                   string      LOCATION=/MT/arch MANDATORY
log_archive_dest_10                  string      
log_archive_dest_11                  string      
log_archive_dest_12                  string      
log_archive_dest_13                  string      
log_archive_dest_14                  string      
log_archive_dest_15                  string      
log_archive_dest_16                  string      
log_archive_dest_17                  string      
log_archive_dest_18                  string      
log_archive_dest_19                  string      
log_archive_dest_2                   string      
log_archive_dest_20                  string      
log_archive_dest_21                  string      
log_archive_dest_22                  string      
log_archive_dest_23                  string      
log_archive_dest_24                  string      
log_archive_dest_25                  string      
log_archive_dest_26                  string      
log_archive_dest_27                  string      
log_archive_dest_28                  string      
log_archive_dest_29                  string      
log_archive_dest_3                   string      
log_archive_dest_30                  string      
log_archive_dest_31                  string      
log_archive_dest_4                   string      
log_archive_dest_5                   string      
log_archive_dest_6                   string      
log_archive_dest_7                   string      
log_archive_dest_8                   string      
log_archive_dest_9                   string      
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string      
log_archive_format                   string      MT_%r_%t_%s.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
log_buffer                           integer     63037440
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     3600
log_checkpoints_to_alert             boolean     TRUE
log_file_name_convert                string      
max_dispatchers                      integer     
max_dump_file_size                   string      20480
max_enabled_roles                    integer     150
max_shared_servers                   integer     
memory_max_target                    big integer 0
memory_target                        big integer 0
nls_calendar                         string      GREGORIAN
nls_comp                             string      BINARY
nls_currency                         string      $
nls_date_format                      string      DD-MON-RR
nls_date_language                    string      AMERICAN
nls_dual_currency                    string      $
nls_iso_currency                     string      AMERICA
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      .,
nls_sort                             string      BINARY
nls_territory                        string      AMERICA
nls_time_format                      string      HH.MI.SSXFF AM
nls_time_tz_format                   string      HH.MI.SSXFF AM TZR
nls_timestamp_format                 string      DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format              string      DD-MON-RR HH.MI.SSXFF AM TZR
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
olap_page_pool_size                  big integer 0
open_cursors                         integer     1500
open_links                           integer     4
open_links_per_instance              integer     4
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     FALSE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
os_authent_prefix                    string      
os_roles                             boolean     FALSE
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string      
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     160
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     64
parallel_threads_per_cpu             integer     2
permit_92_wrap_format                boolean     TRUE
pga_aggregate_target                 big integer 2000M
plscope_settings                     string      identifiers:none
plsql_ccflags                        string      
plsql_code_type                      string      INTERPRETED
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL
pre_page_sga                         boolean     FALSE
processes                            integer     900
processor_group_name                 string      
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
rdbms_server_dn                      string      
read_only_open_delayed               boolean     FALSE
recovery_parallelism                 integer     0
recyclebin                           string      OFF
redo_transport_user                  string      
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string      
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
replication_dependency_tracking      boolean     TRUE
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     4
resource_manager_plan                string      
result_cache_max_result              integer     5
result_cache_max_size                big integer 12960K
result_cache_mode                    string      FORCE
result_cache_remote_expiration       integer     0
resumable_timeout                    integer     0
rollback_segments                    string      
sec_case_sensitive_logon             boolean     TRUE
sec_max_failed_login_attempts        integer     10
sec_protocol_error_further_action    string      CONTINUE
sec_protocol_error_trace_action      string      TRACE
sec_return_server_release_banner     boolean     FALSE
serial_reuse                         string      disable
service_names                        string      MT
session_cached_cursors               integer     50
session_max_open_files               integer     10
sessions                             integer     1372
sga_max_size                         big integer 12000M
sga_target                           big integer 12000M
shadow_core_dump                     string      partial
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 200M
shared_pool_size                     big integer 1264M
shared_server_sessions               integer     
shared_servers                       integer     0
skip_unusable_indexes                boolean     TRUE
smtp_out_server                      string      
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
spfile                               string      /MT/oracle/product/112/dbs/spfileMT.ora
sql92_security                       boolean     TRUE
sql_trace                            boolean     FALSE
sqltune_category                     string      DEFAULT
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
star_transformation_enabled          string      FALSE
statistics_level                     string      TYPICAL
streams_pool_size                    big integer 112M
tape_asynch_io                       boolean     TRUE
thread                               integer     0
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
trace_enabled                        boolean     TRUE
tracefile_identifier                 string      
transactions                         integer     1100
transactions_per_rollback_segment    integer     5
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
use_indirect_data_buffers            boolean     FALSE
use_large_pages                      string      ONLY
user_dump_dest                       string      /MT/log/diag/rdbms/MT/MT/trace
utl_file_dir                         string      
workarea_size_policy                 string      AUTO
xml_db_events                        string      enable

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644920 is a reply to message #644919]

Thu, 19 November 2015 18:17

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Please read carefully. Michel is not asking you to post the results of "show parameter". He is asking you to post the results of "show parameter result", like this:

SCOTT@orcl12c> show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 3936K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SCOTT@orcl12c>

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644924 is a reply to message #644920]

Thu, 19 November 2015 20:44

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Function wasn't declared as result_cache or deterministic, so it has to be a bug if result cache was used.

SY.

[Updated on: Thu, 19 November 2015 20:49]

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644925 is a reply to message #644924]

Thu, 19 November 2015 20:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Solomon Yakobson wrote on Thu, 19 November 2015 21:44

Function wasn't declared as result_cache or deterministic, so it has to be a bug if result cache was used.

Oops, I forgot about RESULT_CACHE_MODE=FORCE.

SY.

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644929 is a reply to message #644919]

Fri, 20 November 2015 01:52

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Do you see it now?
http://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm

Report message to a moderator

Re: SYS_GUID FUNCTION RETURNING SAME VALUE [message #644951 is a reply to message #644929]

Fri, 20 November 2015 12:34

jarockiadoss
Messages: 9
Registered: November 2015

Junior Member

Thank you. Have changed the "result_cache_mode" parameter value to "MANUAL" and flushed the cache.

Now it is working fine. Thanks for all your suggestions.

Report message to a moderator

Previous Topic:	Difference the rows
Next Topic:	Need Explanation on this query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:49:03 CDT 2024