db character set

To find the character set that is being used.

SELECT value
FROM sys.props$
WHERE name = 'NLS_CHARACTERSET'

db character set

To find the character set that is being used.
SELECT value$
FROM sys.props$
WHERE name = 'NLS_CHARACTERSET'

Changing owner of discoverer reports

To change the ownership of reports in discoverer try the following.

1. Make sure noone is accessing discoverer.
2. Find the internal discoverer user id
3. run the following ...

update eul5_documents
set doc_eul_id = [new disco user id],
doc_created_by = [db user id],
doc_updated_by = [db user id]
where doc_eu_id [old disco user id]

4. verify by logging into discoverer and checking that ownership of reports has changed.

default attributes

This post is related to tablespaces with regard to partitioned indexes, but can be used with other storage attributes.

When creating local indexes make sure that an index tablespace is specified before you start specifying the individual partitions. For Example:


create index ... on ... tablespace ...
partition ... local ...


To change the default tablespace for an index partition then use the following.


alter index ... default attributes tablespace ...


note: When a local index is created as part of a new partition, then the tablespaces specified above will also be created.

tablespaces and autoextending datafiles

Use the following command to add a a datafile and make that datafile automatically extend upto the maxsize specified.


ALTER TABLESPACE [ tablespace name ]
ADD DATAFILE [ datafile ] SIZE [ size of datafile ]
AUTOEXTEND ON NEXT [ how much to extend the file by ]
MAXSIZE [ size datafile upto this much ]

drop tables and materialized views

When dropping tables and mv's, you need to make sure that there are no foreign keys refrencing the primary keys or unique keys of the tables you are wanting to drop.

Otherwise you will get the following type of errors.


ORA-02449: unique/primary keys in table referenced by foreign keys


With tables, you can use the cascade constraints option, but there appears to be no such option for materialized views.

To drop such mvs, you need to drop the relevent FKs, disabling the FK does nothing.

Use the following query to drop these constraints.

SQL> select 'alter table ' || owner || '.' || table_name || ' drop constraint ' || constraint_name || ';' from dba_constraints where r_constraint_name = [constraint name]

create temporary tablespace ...

The following is the create tablespace for temporary tablespaces.

CREATE TEMPORARY TABLESPACE {tablespace name}
TEMPFILE '{file name}' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE {tablespace name}
/

be carefull with these files as they are sparse. This means that you create a 10g file, but it only uses 128k of actual space. Over time (as the temp tablepsace is used), the actual space used of this file will increase. The problem here is that other files may come along and grab this space so when the temp tablespace wants to use it, it just then runs out of space and reports some non obvious error message.