How can you check the hidden parameters of your database Check the following view
x$ksppi

You can access remote database sequences using following statement. Here is the Example

SQL> select seq_name.nextval@dblink from dual;

Example
SQL> select seq_address_id.nextval@test from dual;

SQL>update tbl_seq_address set new_key_value=seq_address_id.nextval@test

Oracle 9i has an excellent feature called External Table. Basically when you get CSV files from some other company to see the data from Oracle and access before 9i we have to load the data into Oracle Table using SQL Loader, which involves lot of procedure, space and steps.
But in 9i we don’t need anything you can just join your CSV or DAT file as table in your database and you can do all manipulation.

For Example you got the file from company named ext_table.csv Content of this file is
1,”Mudhalvan”,5000
2,”Aarthi”,3000
3,”Balu”,5000

You want to use this file as Table in Oracle database. Please follow this steps

1. UTL_FILE_DIR parameter should be set to “*’. Meaning oracle is allowed to read files from any folder of that machine.
You can check the same by “show parameter utl_file_dir” from SQL command

2. Make sure external directory was created and read, write access for that directory was granted to your user name.
Login as SYSDBA privileged user and create External Directory
Sql> connect / as sysdba;
Sql> create or replace directory ext_tables as ‘c:\mudhalvan’;
Sql> grant read,write on directory ext_tables to mudhalvan;

3 Move the file (ext_table.csv) to c:\mudhalvan folder.

4 Login into oracle run the following command.

CREATE TABLE MUDHALVAN
(EMPNO NUMBER(10),
ENAME VARCHAR2(25),
SALARY NUMBER(17,2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL
)
LOCATION (‘ext_table.csv’)
)
REJECT LIMIT UNLIMITED;

6. Now you can see the new table created in your username called “MUDHALVAN”

7. sql> select * from mudhalvan;

1. V$LOCK
2. V$LOCKED_OBJECT
3. V$SESSION
4. V$ACCESS
5. V$TRANSACTION

DROP TABLESPACE INCLUDING CONTENTS CASCADE CONSTRAINTS
Will give an error
ORA-01561: failed to remove all objects in the tablespace specified

Solution
Step 1:
select ts# from ts$ where name = ‘‘;

Step 2:
update seg$ set type# = 3 where ts#=;
commit;

Step 3
shutdown abort;
startup

Step 4
drop tablespace including contents;

We have PL/SQL procedure provided by Oracle will migrate from Local to Dictionary and Dictionary to Local
      
SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL(‘TEMP02′);

Normally you are not allowed to run any DDL commands through DBLINK. If you want to run some DDL commands against remote database you need to us DBMS_SQL package. Here is the Example
      
DECLARE
c_handle INTEGER;
feedback INTEGER;
BEGIN
c_handle := DBMS_SQL.open_cursor@TEST;
DBMS_SQL.parse@TEST( c_handle, ‘create table test(eno number,ename varchar2(25))’, DBMS_SQL.V7);
feedback := DBMS_SQL.EXECUTE@TEST(c_handle);
DBMS_SQL.close_cursor@TEST(c_handle);
END;

This message meaning that frequent operation is happening where as Redo log not able to allocate since all redo log groups are filled recently. Even though log files are allocated sequentially there is some interval to overwrite the previous log. This is determined by initial parameter
       This happens when the previous check point is not complete. Consider the following
       — increase the value of LOG_CHECKPOINT_INTERVAL
       –increase the size of the redo logs
       –adding more log groups
       — set LOG_CHECKPOINT_TIMEOUT=0

Scenario:
 On the database we missed one control file (Nothing but first Control file specified in CONTROL_FILES Parameter of init.ora file)
 
1. Shutdown the database.
2. Removed control01.ctl file from c:\oracle\oradata\TEST\control01.ctl
3. Startup the database. Will give an error ORA-00205 : Error in identify control file, Check alert log for more info( We are not even able to mount the database). Alert log clearly says that control01.ctl file is missing
4. Replaced the Original Control file and start the database. ( Database started successfully)
5. Remove the control01.ctl file and changed CONTROL_FILES parameter in init.ora file.
a. Value before change
i. Control_files=’c:\oracle\oradata\TEST\control01.ctl’, ’c:\oracle\oradata\TEST\control02.ctl’, ’c:\oracle\oradata\TEST\control03.ctl’
b. Value  after changed
i. Control_files= ’c:\oracle\oradata\TEST\control02.ctl’, ’c:\oracle\oradata\TEST\control03.ctl’
Here our main technique is oracle always reads and verify based on the First file specified in parameter. Even though we had three control files in those location… Oracle look for only first file. If first file is not proper or not found then it gives an error. So what we did is… we removed that file from parameter.
6. Startup the database (Will work fine because we requested oracle to check only two control files.)
7. After some times we bring the DB down and moved the old control01.ctl file to the same location and changed the value of control_files values in init.ora file.
8. Start the database. Will give an error ORA-00214: controlfile ‘C:\ORACLE\ORADATA\TEST\CONTROL02.CTL’ version 2320 in consistent with file ‘C:\ORACLE\ORADATA\TEST\CONTROL01.CTL’ version 2317
Because there are some mismatch between these two files. To make it work.
9. Copy the content of control02 to control01 and start the Database will work.

SQL> create table test ( msg varchar2(4000) );

Table created.

SQL> create or replace trigger failed_to_extend_temp
after servererror on database
declare
l_sql_text ora_name_list_t;
l_n        number;
begin
if ( is_servererror(1652) )
then
insert into test values ( ‘ora_sysevent = ‘ || ora_sysevent );
insert into test values ( ‘ora_login_user = ‘ || ora_login_user );
insert into test values ( ‘ora_server_error = ‘ || ora_server_error(1) );
l_n := ora_sql_txt( l_sql_text );
for i in 1 .. l_n
loop
insert into test values ( ‘l_sql_text(‘ || i || ‘) = ‘ || l_sql_text(i) );
end loop;
end if;
end;

Trigger created.

SQL> create temporary tablespace test_temp tempfile ‘/tmp/test_temp.dbf’ size 512k reuse extent management local uniform size 64k;

Tablespace created.

SQL> create user temptest identified by temptest;

User created.

SQL> grant connect,resource to temptest;

Grant succeeded.

SQL>

SQL> alter user temptest temporary tablespace test_temp;

User altered.

SQL> conn temptest/temptest
Connected.
SQL>

SQL> select * from all_objects order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12;

SQL> select * from test;

SQL> drop table test;

SQL> ALTER DATABASE TEMPFILE ‘/tmp/test_temp.dbf’ DROP INCLUDING DATAFILES;

SQL> DROP TABLESPACE test_temp INCLUDING CONTENTS;

SQL> DROP TRIGGER failed_to_extend_temp;