Re: Some beginner DBA questions (quota, pfile, insert, password)

Simon Goland <sg_at_mda.ca> writes: > Hi,
>
> I am a 3-weeks old DBA, and already have a few questions to this
> group... ;-). We are running Oracle 7.2 on SunOS 5.4.1.
>
> 1. What is the importance of $ORACLE_HOME/pfile directory and why do my
> init.ora files have to be there, instead of $ORACLE_HOME/dbs? Does it
> matter?
>

According to OFA standard,all the parameter files (init.ora, config.ora) should reside in pfile directory. You can create a link to $ORACLE_HOME/dbs directory.

> 2. I have created several cron jobs that do things for me, like doing
> nightly exports of the database. However, they require logging in as
> 'system' and I don't want to supply the password on the command-line for
> obvious reasons (like 'ps -af'). So I created a hidden file with no
> access to anyone, and my cron script has something like:
>
> exp PARFILE=export_parameters.txt < "my hidden file with password"
>
> How secure is it, and is there a better way?
>

Create an user (ops$oracle) which has got exp_full_database privilege. You can logon to oracle and can connect as

exp / (followed by other parameters)

This will make sure that password is not required and it will not be visible by system monitor command.

> 3. Some users do INSERTs with huge number of records, and it is all temp
> data that they don't really care about. However, it generates lots of
> redo logs. Is there a way to run an INSERT that does not generate redo
> logs, maybe similar to using TRUNCATE (no redo logs) instead of DELETE
> (lots of redo logs)?
>

There is some option called unrecoverable (I think it is in create table). This will switch off the redo log files.

The disadvantage being, you cannot recover these tables after crash. I am not sure of the syntax, but it starts with unrecoverable. Please export these tables after your operation.

> 4. Some users have created junk in tablespaces they were not supposed
> to. SO I set their default tablespace to TEMP. I would also like to
> restrict them in SYSTEM tablespace, maybe by setting a quota. How? Would
> it work? Any other suggestions?
>

Issue the following command for each of the tablespaces for which you need to revoke access.

alter user <username> quota 0 on system

> 5. Somebody here mentioned a DBA FAQ. Where can I get one?
>
> And that's it for now. I appreciate every comment or suggestion you
> might have for me. Additionally, could you please also email me the
> reply, since due to our firewall I don't have access to newsgroups from
> work. And often at home I am too busy reading Oracle docs... ;-)
>

I am not sure

I hope the above info is useful to you.

Regards

N.Prabhakar Received on Mon Dec 09 1996 - 00:00:00 CST